By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,058 Members | 1,336 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,058 IT Pros & Developers. It's quick & easy.

insert into table error

P: 6
oledbexception was unhandled....syntax error in insert into statement is the error I get I can edit/update information, delete and everything except for add

the cmdSave code is::::

Dim drNewRow As DataRow = m_dtTable1.NewRow()

With Me
drNewRow("id") = .txtid.Text
drNewRow("Date") = .txtdate.Text
drNewRow("Time") = .txttime.Text
drNewRow("CallSign") = .txtcallsign.Text
drNewRow("Freq") = .txtfreq.Text
drNewRow("Mode") = .txtmode.Text
drNewRow("RST") = .txtrst.Text
drNewRow("Notes") = .txtnotes.Text
drNewRow("QslTX") = .txtqsltx.Text
drNewRow("QslRX") = .txtqslrx.Text
End With

m_dtTable1.Rows.Add(drNewRow)
m_daTable1.Update(m_dtTable1) <<<<-----ERROR HERE

m_intRowPosition = m_dtTable1.Rows.Count - 1

m_blnNewRecord = False
m_blnUpdateRecord = False

ShowCurrentRecord()


----------------------------------------------------------------

declarations:::
Public m_cnTable1 As New OleDb.OleDbConnection
Public m_daTable1 As OleDb.OleDbDataAdapter
Public m_cbTable1 As OleDb.OleDbCommandBuilder
Public m_dtTable1 As New DataTable
Public m_intRowPosition As Integer = 0
Public m_blnNewRecord As Boolean = False
Public m_blnUpdateRecord As Boolean = False


----------------------------------------------------------------

I can post full code if need be but I figured going through that much might be enough
Dec 16 '07 #1
Share this Question
Share on Google+
7 Replies


Dököll
Expert 100+
P: 2,364
oledbexception was unhandled....syntax error in insert into statement is the error I get I can edit/update information, delete and everything except for add

the cmdSave code is::::

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim drNewRow As DataRow = m_dtTable1.NewRow()
  3.  
  4. With Me
  5. drNewRow("id") = .txtid.Text
  6. drNewRow("Date") = .txtdate.Text
  7. drNewRow("Time") = .txttime.Text
  8. drNewRow("CallSign") = .txtcallsign.Text
  9. drNewRow("Freq") = .txtfreq.Text
  10. drNewRow("Mode") = .txtmode.Text
  11. drNewRow("RST") = .txtrst.Text
  12. drNewRow("Notes") = .txtnotes.Text
  13. drNewRow("QslTX") = .txtqsltx.Text
  14. drNewRow("QslRX") = .txtqslrx.Text
  15. End With
  16.  
  17. m_dtTable1.Rows.Add(drNewRow)
  18. m_daTable1.Update(m_dtTable1) <<<<-----ERROR HERE
  19.  
  20. m_intRowPosition = m_dtTable1.Rows.Count - 1
  21.  
  22. m_blnNewRecord = False
  23. m_blnUpdateRecord = False
  24.  
  25. ShowCurrentRecord()
  26.  
  27.  
  28. ----------------------------------------------------------------
  29.  
  30. declarations:::
  31. Public m_cnTable1 As New OleDb.OleDbConnection
  32. Public m_daTable1 As OleDb.OleDbDataAdapter
  33. Public m_cbTable1 As OleDb.OleDbCommandBuilder
  34. Public m_dtTable1 As New DataTable
  35. Public m_intRowPosition As Integer = 0
  36. Public m_blnNewRecord As Boolean = False
  37. Public m_blnUpdateRecord As Boolean = False
  38.  
----------------------------------------------------------------

I can post full code if need be but I figured going through that much might be enough
Greetings!

I have not yet handled insert/update commands in his fashion, but one thing I see is should you also dimension daTable1 as you did here:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim drNewRow As DataRow = m_dtTable1.NewRow()
  3.  
  4.  
Also, are you updating to the daTable1 table?

Should you say:

Expand|Select|Wrap|Line Numbers
  1.  
  2. m_daTable1.Rows.Update(m_dtTable1) 
  3.  
  4.  
If nothing is working you might consider adding a different code to add. Something I am actually working on, works great. disregard if your works for you:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim my_database As Database
  3. Dim my_record As Recordset
  4. Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
  5. Set my_record = my_database.OpenRecordset("select * from Employee where Your_Price='" & Text1(0).Text & "'")
  6. my_record.Edit
  7.                 my_record!Your_Price = Text1(0).Text
  8.                 my_record!Name = Text1(1).Text
  9.                 my_record!Crime_Rate_1 = Text1(2).Text
  10.                 my_record!Crime_Rate_2 = Text1(3).Text
  11.                 my_record!Type = Text1(4).Text
  12.  
  13. my_record.Update
  14. my_record.Close
  15. my_database.Close
  16.  
  17. End If
  18.  
  19. End Sub
  20.  
Still try adding Rows.Update to the line mentioned, okay.

In a bit!

Dököll
Dec 16 '07 #2

Dököll
Expert 100+
P: 2,364
Nevermind, I think you're refering to Insert, you must Add Insert, not Update, so perhaps Rows.Insert, or simply Insert, instead of Update, my apologies. I thought you were having issues updating when I read that portion of the code.

See if that works:-)
Dec 16 '07 #3

P: 6
I replaced m_dtTable1.Rows.Add(drNewRow) with m_dtTable1.Rows.InsertAt(drNewRow, m_dtTable1.Rows.Count + 1)

still same error


m_daTable1.InsertCommand is the only insert available to me and that doesnt seem to be the right way to go either...thisis sooo frustrating, so close to finishing but yet soo far
Dec 16 '07 #4

P: 41
I think you are using reserved words as field names in the database (i.e. "Date" and "Time") try puting square brackets around those two words in your code, like so "[Date]" "[Time]" or you can change the field names (in the database) to somthing like "EventDate" and "EventTime". When using reserved words as field names in a database you have to use square brackets when using those field names in your query/code. There is a long list reserved words, here is a link to a list.

http://support.microsoft.com/kb/286335

Notes: you are also using "Notes" which is not a reserved word but "Note" is also "ID" is not on the list but I imagin it could give you problems. I would recomend (if possable) to change those field names as well. I hope this helps!


Torgg
Dec 16 '07 #5

P: 6
changed date to ddate same thing will work on the other fields and see how it goes
Dec 16 '07 #6

P: 6
WOOOHOOO

changed id date time and notes left all other fields the same and bingo worked best kind

leave it to me to pick names that are reserved

thank you soooooooooOOOOOOOOOOOOOOooooooooooooo much, this has been a 2 week battle

thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou thankyou
Dec 16 '07 #7

P: 41
I'm so glad I could help... take care!
Dec 16 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.