SQL command not inserting all values in datagrid into db,

Question

I am trying to pass gridview data into database. The problem I am having is that not all data in my gridview is entering the database. Doing a messagebox shows only name column is going in. Here is my code

Protected Sub Button2_Click1(sender As Object, e As EventArgs) Handles Button2.Click
    Dim sc As StringCollection = New StringCollection
    Dim field1 As String = String.Empty
    Dim i As Integer = 0
    Do While (i < GridView1.Rows.Count)
        field1 = GridView1.Rows(i).Cells(0).Text
        i = (i + 1)
    Loop
    ' get the  field to be Inserted
    sc.Add(field1)
    ' add the field to be Inserted in the StringCollection
    InsertRecords(sc)
    ' call method for insert and pass the StringCollection values

End Sub





Dim conn As MySqlConnection = New MySqlConnection("Server=****************;Database=******;Uid=*******;Pwd=****;allow user variables=true")
    Dim sb As StringBuilder = New StringBuilder(String.Empty)
    For Each item As String In sc
        Const sqlStatement As String = "INSERT INTO student(name, age, adress) VALUES ("
        sb.AppendFormat("{0}'{1}' ", sqlStatement, item)

    Next
    sb.Append(")")

    MsgBox(sb.ToString)

    Try
        conn.Open()
        Dim cmd As MySqlCommand = New MySqlCommand(sb.ToString, conn)
        cmd.CommandType = CommandType.Text
        cmd.ExecuteNonQuery()

    Catch ex As System.Data.SqlClient.SqlException
        Dim msg As String = "Insert Error:"
        msg = (msg + ex.Message)
        Throw New Exception(msg)
    Finally
        conn.Close()
    End Try
End Sub

Don't concatenate strings to create a sql command but use parameters. — Tim Schmelter
– Tim Schmelter, Commented Oct 18, 2012 at 11:17
What is sc and where's your code which executes the command? — Tim Schmelter
– Tim Schmelter, Commented Oct 18, 2012 at 11:19

Tim Schmelter · Accepted Answer · 2012-10-18 11:40:14Z

1

Don't concatenate strings to create a sql command but use parameters to primarily avoid sql-injection.

Const sqlStatement As String = "INSERT INTO student(name, age, adress) VALUES (?Pname,?Page,?Padress)"
Try
    Using con = New MySqlConnection(connectionString)
        con.Open()
        For Each item In sc
            Using cmd = New MySqlCommand(sqlStatement, con)
                cmd.Parameters.AddWithValue("?Pname", item.Name)
                cmd.Parameters.AddWithValue("?Page", item.Page)
                cmd.Parameters.AddWithValue("?Padress", item.Padress)
                cmd.ExecuteNonQuery()
            End Using
        Next
    End Using
Catch ex As System.Data.SqlClient.SqlException
    ' log message '
    Throw ' don't use throw new Exception or throw ex '
End Try

Note that i've used sc like a collection of a custom class with all needed properties. But even if that was not correct, it should give you an idea how it works.

edited Oct 18, 2012 at 11:40

answered Oct 18, 2012 at 11:34

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Tim Schmelter Over a year ago

@Steve: And i've forgotten to add my sql statement ;)

user1712552 Over a year ago

Thanks guys Im going to give that a go. Its really appreciated!!

user1712552 Over a year ago

The problem i now get is the Pname in the paramater section is not a member of a string

Tim Schmelter Over a year ago

@user1712552: Then you should tell me what type it is. You can convert it, for example to int: int.Parse(stringValue) etc.

user1712552 Over a year ago

I dont understand . This is what im doing cmd.Parameters.AddWithValue("?Pname", item.name) Now when I bang that in Im getting error there is a blue line under item.name. When hovering mouse over it is says 'name' is not a member of 'String'.

|

Collectives™ on Stack Overflow

SQL command not inserting all values in datagrid into db,

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related