insert into table error

[davincinewfie]

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

[Dököll]

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

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:

 
Dim drNewRow As DataRow = m_dtTable1.NewRow()
 
 

Also, are you updating to the daTable1 table?

Should you say:

 
m_daTable1.Rows.Update(m_dtTable1) 
 
 

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:

 
Dim my_database As Database
Dim my_record As Recordset
Set my_database = OpenDatabase("C:\DataGram\Data_Central.mdb")
Set my_record = my_database.OpenRecordset("select * from Employee where Your_Price='" & Text1(0).Text & "'")
my_record.Edit
                my_record!Your_Price = Text1(0).Text
                my_record!Name = Text1(1).Text
                my_record!Crime_Rate_1 = Text1(2).Text
                my_record!Crime_Rate_2 = Text1(3).Text
                my_record!Type = Text1(4).Text
 
my_record.Update
my_record.Close
my_database.Close
 
End If
 
End Sub
 

Still try adding Rows.Update to the line mentioned, okay.

In a bit!

Dököll

[Dököll]

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:-)

[davincinewfie]

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

[Torgg]

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

[davincinewfie]

changed date to ddate same thing will work on the other fields and see how it goes

[davincinewfie]

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

[Torgg]

I'm so glad I could help... take care!