Can't update if database fields is more than 8

akosidandan

Hello, I would like to ask why do I get a run time error that says

"invalid insert into command"

while updating a database records. But if the database records field is less than 8 it works well. The database I am using is ms access/.mdb and the connection I use is oledb connection. Any comments or suggestion are kindly appreciated. Thanks, dfan23

Dr Walt Fair PE

That error indicates you are trying to insert some invalid data into your database. Without knowing how you db is laid out and what you are trying to insert, there's no way to know what went wrong. Common problems include trying to save data that isn't consistent with the db field definition, trying to duplicate a primary key value, trying to save into a field that doesn't exist, etc.

CQ de W5ALT

Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software

Abhinav S

Your 8th field appears to have an issue when inserting into the database. Post some code here and someone will be able to help.

Me, I'm dishonest. And a dishonest man you can always trust to be dishonest.
Honestly. It's the honest ones you want to watch out for...

akosidandan

Hello, Sir this my database

FIELD Name | Data Type
ID number
lastName text
firstName text
middleName text
contactNum number
add text
eAdd text
status text
position text

This is my save code command

Try

        con.Open()
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds = New DataSet("DataSet")
        ds.Tables.Add(dt)

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter("Select \* from empTable", con)
        da.Fill(dt)
        con.Close()

        Dim newrow As DataRow = dt.NewRow
        With newrow
            .Item("ID") = Val(txtEmpIDAdd.Text)
            .Item("lastName") = txtLastAdd.Text
            .Item("firstName") = txtFirstAdd.Text
            .Item("middleName") = txtMiddleAdd.Text
            .Item("contactNumber") = Val(txtContactAdd.Text)
            .Item("add") = txtAddressAdd.Text
            .Item("eAdd") = txtEmailAdd.Text
            .Item("status") = cmbStatusAdd.Text
            .Item("position") = txtPositionAdd.Text
        End With
        dt.Rows.Add(newrow)


        con.Open()
        da = New OleDbDataAdapter("Select \* from empTable", con)
        Dim cb As New OleDbCommandBuilder
        cb = New OleDb.OleDbCommandBuilder(da)
        da.Update(dt)
        con.Close()


    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

This save code does not work if my database field is 9 above, but when I delete some fields the code work. Thanks for immediate response. Thanks, dfan23

akosidandan

Hello, Sir this my database

FIELD Name | Data Type
ID number
lastName text
firstName text
middleName text
contactNum number
add text
eAdd text
status text
position text

This is my save code command

Try

        con.Open()
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds = New DataSet("DataSet")
        ds.Tables.Add(dt)

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter("Select \* from empTable", con)
        da.Fill(dt)
        con.Close()

        Dim newrow As DataRow = dt.NewRow
        With newrow
            .Item("ID") = Val(txtEmpIDAdd.Text)
            .Item("lastName") = txtLastAdd.Text
            .Item("firstName") = txtFirstAdd.Text
            .Item("middleName") = txtMiddleAdd.Text
            .Item("contactNumber") = Val(txtContactAdd.Text)
            .Item("add") = txtAddressAdd.Text
            .Item("eAdd") = txtEmailAdd.Text
            .Item("status") = cmbStatusAdd.Text
            .Item("position") = txtPositionAdd.Text
        End With
        dt.Rows.Add(newrow)


        con.Open()
        da = New OleDbDataAdapter("Select \* from empTable", con)
        Dim cb As New OleDbCommandBuilder
        cb = New OleDb.OleDbCommandBuilder(da)
        da.Update(dt)
        con.Close()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

This save code does not work if my database field is 9 above, but when I delete some fields the code work. Thanks for immediate response. Thanks, dfan23

Luc Pattyn

Hi, rather than providing some information twice, you'd better tell us which database you are using, and what the exact error/exception information is you are getting for one or two specificied situations ("when I delete some fields the code work" is too vague). Your app is showing Exception.ToString() which is very good, but you failed to reproduce it here. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

akosidandan

Hello, Sir the database I am using is ms Access database Thanks, dfan23

akosidandan

Hello, I got the solution now sir. I have noticed that word "position" is a reserved word in database thanks, dfan23

Dr Walt Fair PE

Great! Glad to hear you found the problem. I've run into problems with reserved words, too. It can be very frustrating!

CQ de W5ALT

Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software

akosidandan

Hello, I got the solution now sir. I have noticed that word "position" is a reserved word in database. thanks, dfan23