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

[leaf0209]

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.Ol edbException: {"Syntax error in INSERT INTO statement."}
error code: -2147217900
error source: Microsoft JET Database Engine




Thank you for your kind attention!

[ansumansahu]

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=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\SB_20 00.mdb"
Dim Con As New OleDbConnection (strConnString)
Dim da As New OleDbDataAdapte r(sql, Con)
Dim ds As New DataSet()
Dim dt As DataTable
Dim dsNewRow As DataRow
Dim cb As OleDbCommandBui lder = New OleDbCommandBui lder(da)
da.Fill(ds, "ADWarning" )
Con.Close()
dt = ds.Tables("ADWa rning")
Try
dsNewRow = dt.NewRow()
dsNewRow("Issue Date") = Me.TextBox1.Tex t
dsNewRow("Issue Time") = Me.TextBox2.Tex t
dsNewRow("FcstO nsetTime") = Me.TextBox3.Tex t
dsNewRow("FcstC ldAmt") = Me.TextBox4.Tex t
dt.Rows.Add(dsN ewRow)
Dim drModified As DataRow() = dt.Select(Nothi ng, Nothing, DataViewRowStat e.Added)
Con.Open()
ListBox1.DataSo urce = ds.Tables("ADWa rning")
ListBox1.Displa yMember = "IssueDate"
da.UpdateComman d = cb.GetInsertCom mand
da.Update(drMod ified)
MessageBox.Show ("Database has been updated!", "Data", MessageBoxButto ns.OK, MessageBoxIcon. Information)
Catch eInsertExceptio n As Exception
MsgBox(eInsertE xception.Messag e & eInsertExceptio n.Source)
Throw eInsertExceptio n
Finally
Con.Close()
End Try


I get an error prompt when debugging
System.Oledb.Ol edbException: {"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.mindfiresol utions.com

[leaf0209]

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.mindfiresol utions.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(drMod ified) when debugging.. Can you please help me to check how to correct this statement. Thank you for your help!

[Killer42]

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(drMod ified) 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.

[rnirav]

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.