Guideline for inserting data from datagrid into sql server table

[Pamilerin]

can any body help me by giving me a guideline on how to insert data from a populated datagrid into sql server table and how to make the values conform to the specified datatypes constructe in the sql server side...assuming the datagrid headings have already been created in the sql server side. Thanks.

[Begginnerdev]

You will have to loop through the datagrid like a table.

Here is another question from Microsoft's Forumns.

Click Here

[poojavb]

Try this....

Dim i As Integer
i = DataGridView1.CurrentRow.Index
'then use the insert query with the parameters like 

DataGridView1.Item(0, i).Value 'cell value keeps on changing for all cells

[Pamilerin]

the code below is what i have tried to implement it but it is showing me an error:" Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index" . any correction would be helpful

Dim objConnection As New SqlConnection(("server=SEER;database=Test;user id=sa;password=ASDSER"))

        Dim objCommand As SqlCommand = New SqlCommand()

        objCommand.Connection = objConnection

        objConnection.Open()

        'insert data to sql  database row by row

        Dim Symbol, pubDate As String
        Dim volume_of_transaction, transaction_amount As String

        For i As Integer = 0 To Me.DtaGridXML.Rows.Count

            Symbol = Me.DtaGridXML.Rows(i).Cells(0).ToString()

            volume_of_transaction = Me.DtaGridXML.Rows(i).Cells(1).ToString()

            transaction_amount = Me.DtaGridXML.Rows(i).Cells(2).ToString()

            pubDate = Me.DtaGridXML.Rows(i).Cells(3).ToString()

            objCommand.CommandText = "INSERT INTO TopTrades" & _
                                    "(Symbol, volume_of_transaction, transaction_amount, pubDate)" & _
                                    "VALUES('Symbol', 'volume_of_transaction', 'transaction_amount', 'pubDate')"
            Try
                objCommand.ExecuteNonQuery()
            Catch SqlExceptionErr As SqlException

                MessageBox.Show(SqlExceptionErr.Message)
            End Try
        Next

        objConnection.Close()

[Begginnerdev]

You will want to make sure to subtract 1 from count.

Liike this:

For i As integer = 0 to Me.DtaGridXML.Rows.Count - 1

[Pamilerin]

thanks, it worked...how did you know, explanation will be highly appreciated

[Begginnerdev]

Almost every obect that has an index is treated as a 0th based array.

Arrays will have a terminator character to the end.

Example

Cat

cat = 3 characters, but is actually 4 after the terminator.

Therefore,

 Dim test() As String = {"c", "a", "t"}

 MsgBox(test.Length) 'Returns 3'

 MsgBox(test(0)) 'Returns c'
 MsgBox(test(1)) 'Returns a'
 MsgBox(test(2)) 'Returns t'
 MsgBox(test(3)) 'Outside of bounds. Terminator character."