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

Help needed! {Syntax error in INSERT INTO statement. error code: -2147217900}

P: 3
Hi,
I am new to this forum and have been troubled by this problem for the past few days. I would appreciate if someone would take a look and help me out.

I am using vb express 2005 edition...

Expand|Select|Wrap|Line Numbers
  1. Dim sql As String = "SELECT * FROM SB_ADWarning"
  2. Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SB_2000.mdb"
  3. Dim Con As New OleDbConnection(strConnString)
  4. Dim da As New OleDbDataAdapter(sql, Con)
  5. Dim ds As New DataSet()
  6. Dim dt As DataTable
  7. Dim dsNewRow As DataRow
  8. Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
  9. da.Fill(ds, "ADWarning")
  10. Con.Close()
  11.  dt = ds.Tables("ADWarning")
  12. Try
  13.             dsNewRow = dt.NewRow()
  14.             dsNewRow("IssueDate") = Me.TextBox1.Text
  15.             dsNewRow("IssueTime") = Me.TextBox2.Text
  16.             dsNewRow("FcstOnsetTime") = Me.TextBox3.Text
  17.             dsNewRow("FcstCldAmt") = Me.TextBox4.Text
  18.             dt.Rows.Add(dsNewRow)
  19.             Dim drModified As DataRow() = dt.Select(Nothing, Nothing, DataViewRowState.Added)
  20.             Con.Open()
  21.             ListBox1.DataSource = ds.Tables("ADWarning")
  22.             ListBox1.DisplayMember = "IssueDate"
  23.             da.UpdateCommand = cb.GetInsertCommand
  24.             da.Update(drModified)
  25.             MessageBox.Show("Database has been updated!", "Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
  26.         Catch eInsertException As Exception
  27.             MsgBox(eInsertException.Message & eInsertException.Source)
  28.             Throw eInsertException
  29.         Finally
  30.             Con.Close()
  31.         End Try

I get an error prompt when debugging
System.Oledb.OledbException: {"Syntax error in INSERT INTO statement."}
error code: -2147217900
error source: Microsoft JET Database Engine




Thank you for your kind attention!
Mar 19 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 149
Hi,
I am new to this forum and have been troubled by this problem for the past few days. I would appreciate if someone would take a look and help me out.

I am using vb express 2005 edition...

Dim sql As String = "SELECT * FROM SB_ADWarning"
Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SB_2000.mdb"
Dim Con As New OleDbConnection(strConnString)
Dim da As New OleDbDataAdapter(sql, Con)
Dim ds As New DataSet()
Dim dt As DataTable
Dim dsNewRow As DataRow
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
da.Fill(ds, "ADWarning")
Con.Close()
dt = ds.Tables("ADWarning")
Try
dsNewRow = dt.NewRow()
dsNewRow("IssueDate") = Me.TextBox1.Text
dsNewRow("IssueTime") = Me.TextBox2.Text
dsNewRow("FcstOnsetTime") = Me.TextBox3.Text
dsNewRow("FcstCldAmt") = Me.TextBox4.Text
dt.Rows.Add(dsNewRow)
Dim drModified As DataRow() = dt.Select(Nothing, Nothing, DataViewRowState.Added)
Con.Open()
ListBox1.DataSource = ds.Tables("ADWarning")
ListBox1.DisplayMember = "IssueDate"
da.UpdateCommand = cb.GetInsertCommand
da.Update(drModified)
MessageBox.Show("Database has been updated!", "Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch eInsertException As Exception
MsgBox(eInsertException.Message & eInsertException.Source)
Throw eInsertException
Finally
Con.Close()
End Try


I get an error prompt when debugging
System.Oledb.OledbException: {"Syntax error in INSERT INTO statement."}
error code: -2147217900
error source: Microsoft JET Database Engine




Thank you for your kind attention!
Hi ,

From the error statement it seems that the Insert statement is not formulated correctly at your end inside the program. Carefully look into the insert statement and see if you are missing out anything.

thanks
ansuman
www.mindfiresolutions.com
Mar 19 '07 #2

P: 3
Hi ,

From the error statement it seems that the Insert statement is not formulated correctly at your end inside the program. Carefully look into the insert statement and see if you are missing out anything.

thanks
ansuman
www.mindfiresolutions.com

Hi,
Thank you for your reply! I am not really sure how to correct the error.. I found that my error statement is: da.Update(drModified) when debugging.. Can you please help me to check how to correct this statement. Thank you for your help!
Mar 20 '07 #3

Expert 5K+
P: 8,434
Hi,
Thank you for your reply! I am not really sure how to correct the error.. I found that my error statement is: da.Update(drModified) when debugging.. Can you please help me to check how to correct this statement. Thank you for your help!
I'm not that familiar with this area, but would have to guess that the date/time format is to blame. Could you try commenting out the setting of any date and/or time fields, to see what effect it has? Date and times are a very common source of problems when dealing with Access databases.

(It may be necessary to use "#" delimiters around the values, or convert them to an actual Date data type before passing them, or perhaps simply change the format. Feel free to try any of these, but I believe the first step must be to pin down exactly where the error lies before trying to correct it. Hence the commenting.)

I can't really say anything about the connection, as I don't know anything about that area. But some other things that you might check include whether there are required fields for which you have not supplied a value. Or other validation rules in the database which your data doesn't match.

Is there any way we can get a look at the INSERT statement which is being generated? That might answer everything.
Mar 22 '07 #4

P: 1
Hi,

Just looking to your code; I thought you are storing date and time value in your database. If so, why are you using textbox. Use datetime control. It will solve your problem. One more thing is check the format for date and time. If you wanna save date and time; U must format them as needed by the database settings.

like :

table(0).Fields(0).value = Format(dtpdate.value,"dd-mm-yyyy")

hope this will help you.
Mar 23 '07 #5

Post your reply

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