[RESOLVED]Insert Command not updating

[angelica]

Hi,

I am doing my first Web.Application and I have connected to a database and from the webform the user will input his details to be stored in the first available row and update it. I have this procedure

Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
        MyConn.Open()
       Dim sql As String = "Select * FROM ApplicantsDetails"
        Dim mydataadapter As New OleDbDataAdapter
        mydataadapter = New OleDbDataAdapter(Sql, MyConn)
        Dim mycmdbuilder As New OleDbCommandBuilder(mydataadapter)
        Dim myDatatable As New DataTable
        mydataadapter.Fill(myDatatable)
        Dim mynewrow As DataRow = myDatatable.NewRow
        mynewrow(0) = TxtID.Text
        mynewrow(1) = TxtName.Text
        mynewrow(2) = Txtsurname.Text
        mynewrow(6) = dropdschool.SelectedValue.ToString
        myDatatable.Rows.Add(mynewrow)
        mydataadapter.Update(myDatatable)
        MyConn.Close()
    End Sub

and it is giving me an error:

Syntax error in INSERT INTO statement. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error: 


Line 97:         mynewrow(6) = dropdschool.SelectedValue.ToString
Line 98:         myDatatable.Rows.Add(mynewrow)
Line 99:         mydataadapter.Update(myDatatable)
Line 100:        MyConn.Close()
Line 101:    End Sub

Can someone please tell me where I am missing... thanks

[angelica]

hi Guys,

I've been going into some reader and changed to my code below which uses parameter. I get no syntax error yet I am still unable to update the table. Can someone pls help, much appreciated

Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
        Dim sql As String = "Select * FROM ApplicantsDetails"
        Dim mydataadapter As New OleDbDataAdapter
        mydataadapter = New OleDbDataAdapter(Sql, MyConn)
        Dim mycmdbuilder As New OleDbCommandBuilder(mydataadapter)
        Dim myDatatable As New DataTable
 
        Dim reader As OleDbDataReader
        Dim parmID As New OleDbParameter("@ID Number", OleDbType.VarChar)
        Dim parmName As New OleDbParameter("@FirstName", OleDbType.VarChar)
        Dim parmSurname As New OleDbParameter("@SecondName", OleDbType.VarChar)
 
        cmd = New OleDbCommand(sql, MyConn)
        With cmd
            .Parameters.Add("@ID Number", TxtID.Text)
            .Parameters.Add("@FirstName", TxtName.Text)
            .Parameters.Add("@LastName", Txtsurname.Text)
 
            MyConn.Open()
            .ExecuteNonQuery()
            mydataadapter.Fill(myDatatable)
            Dim mynewrow As DataRow = myDatatable.NewRow
            myDatatable.Rows.Add(mynewrow)
 
            mydataadapter.Update(myDatatable)
        End With
        MyConn.Close()
    End Sub

[mendhak]

It appears to me that your datatable and dataadapter remain in local scope of your subs. The best thing for you to do is to execute an insert statement directly. There is no point in loading all those records and then adding a new row.

Check out the example here:
http://msdn.microsoft.com/library/de...querytopic.asp

[mendhak]

Sure.

Dim myCommand As New SqlCommand("INSERT INTO ApplicantDetails([ID Number],FirstName, LastName) VALUES('" & Textbox1.text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')" , myConn)
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()

[angelica]

Hi Mendhak, why does ID Number have square brackets??

([ID Number],FirstName, LastName)

Mendhak, can you edit my previous code with the insert cos Ive added the bit you sent me but although I get no error my table didnt get the new row. thanks

[Hack]

Hi Mendhak, why does ID Number have square brackets??

Because it has a space in it. If the field name was ID_Number, there would be no need for the brackets because there would be no space.

Brackets are also required if you use a reserved word as a field name; Example: Date Time

Both of those would have to be in brackets because they are reserved words.

You should never used reserved words as field names, and I avoid using spaces in field names just because I hate having to including the brackets in queries.

[mendhak]

Me too, I hate square brackets. They're more disrupting than beneficial.

I'm a little reluctant to be writing this for you because I thought the code was pretty self-explanatory and you should learn to do this yourself. Nevertheless, just this once.

Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
        Dim sql As String = "Select * FROM ApplicantsDetails"
 
         Dim myCommand As New SqlCommand("INSERT INTO ApplicantDetails([ID Number],FirstName, LastName) VALUES(" & TxtID.text & ",'" & TxtName.Text & "','" & Txtsurname.Text & "')" , myConn)
 
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()
 
    End Sub

[angelica]

Hi mendhak,

Thanks for your help, but I had coded that, see I try hard... but I'm still getting the error that the on:

myCommand.ExecuteNonQuery()

error says it :

Could not find output table 'ApplicantDetails'

Here's my latest code. any clues where Im wrong pls.

Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
        Dim sql As String = "Select * FROM ApplicantsDetails"
        Dim mydataadapter As New OleDbDataAdapter
        mydataadapter = New OleDbDataAdapter(Sql, MyConn)
        Dim myCommand As New OleDbCommand("INSERT INTO ApplicantDetails([IDNumber],FirstName, LastName) VALUES(" & TxtID.Text & ",'" & TxtName.Text & "','" & Txtsurname.Text & "')", MyConn)
 
        myCommand.Connection.Open()
        myCommand.ExecuteNonQuery()
        MyConn.Close()
 
    End Sub

[MetalKid]

Dim myCommand As New OleDbCommand("INSERT INTO ApplicantDetails...

You put down ApplicantDetails... Doesn't it need to be ApplicantsDetails? You missed the "s" on Applicant.

[angelica]

hi MetalKid,

Uh! It works fine now. Slip of the types and Ive been haking my brain. Thanks for your help. Cheers

[mendhak]

Now that it's working, add [Resolved] to the thread title... and you know the drill