Updating database problem

[k80sg]

Hi, I am using the following code to insert record into my database but I keep getting the "Error while inserting record on table...Invalid column name 'xxx'." but when I tried to issue the command in SQL server:

INSERT Movie_Table VALUES(100, 'Avatar');

I am okay.

Thanks for the help!

Private Sub store_Data()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim iCounter As Integer

        Try
            con.ConnectionString = "Data Source=HPEnvy-HP; Initial Catalog=Cinema; User Id=sa; Password=8034615h;"             
            con.Open()
            cmd.Connection = con

            For Each cCtrl As Control In Panel1.Controls
                If TypeOf cCtrl Is TextBox Then
                    Dim txtBox As New TextBox
                    txtBox = cCtrl
                    iCounter += 1
                        cmd.CommandText = ("INSERT INTO Movie_Table(Movie_ID, Movie_Title) VALUES(" & ("00" & iCounter.ToString) & " ," & (txtBox.Text) & ")") 
' The txtBox.Text is causing the problem which I don't understand why.
                End If

            Next

            cmd.ExecuteNonQuery()
            MsgBox("Record saved successfully")
            iCounter = 0

        Catch ex As Exception
            MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
        Finally
            con.Close()
        End Try

    End Sub

[si_the_geek]

Rather than just looking at the code that builds the string, also look at the string it creates (which will be in cmd.CommandText after that line runs).

In this case there are clear differences between what it creates and your example.


In addition to that, string building is not a wise move... For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).