what values should I use for my datatable?

[rajulama]

Hello everyone, I have a datagridview and want to insert each row of this into a database(db1) table(sheet1). Both the datagridview and sheet1 has 7 fields. I used the following code but I just wonder what I should fill into the values for insert into statement, provided that except for field1 all of the data is in text format. Field1 is in number format:

Dim dgr1 As DataGridViewRow
For Each dgr1 In Me.DataGridView1.Rows
Dim con As OleDbConnection = New _ OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\db1.mdb")
con.Open()
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, _ Field2, Field3, Field4, Field5, Field6, Field7) values()", con)
cmd.ExecuteNonQuery()
con.Close()
Next

Thanks in advance.

[Johan Hakkesteegt]

If the fields in your database are all TEXT, CHAR, VARCHAR, OR NVARCHAR (i.e. strings) then you can feed pretty much whatever you want into those fields. The fields will accept text and numerical values. Only your first field will require a numerical value. The only thing you need to check is the maximum length of each field against the possible maximum length of each value that you might feed to it.

My advice is free, and you may get what you paid for.

[rajulama]

many thanks for the suggestions. Can you please give me an example code to fill into value?

[Johan Hakkesteegt]

Well the question is what kind of inserting you want to do. Simply put you could do something like this:

Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) values(1,2,3,4,5,6,7)", con)

or

Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) values(1," & SomeVariable & "," & SomeOtherVariable & "," & YetAnotherVariable & ",5,6,7)", con)

or Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) SELECT field1, field2, field3, field4, field5, field6, field7 FROM AnotherTable WHERE some condition is true ", con)

My advice is free, and you may get what you paid for.

[rajulama]

Since I am looping through each row of a datagridview, I would like to insert each row of the datagridview. What should I do in this case? Many Thanks

[Johan Hakkesteegt]

Use the second option from my previous answer, and set the value of each variable to the value you get from each field in each row in the datagridview Something like: Variable1 = row.Item("Field1") Variable2 = row.Item("Field2") etc.

My advice is free, and you may get what you paid for.

[rajulama]

I tried:

"INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ", " & dgr1.Cells(7) & " )"

but it says syntax error

[Johan Hakkesteegt]

Something like that, yes. Also adapt your code like this, so you will know if something goes wrong:

Try

'Your code here

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

and remember to check the length of the content of each cell against the maximum length of each field.

My advice is free, and you may get what you paid for.

[rajulama]

When I did like this:

Try
Dim dgr1 As DataGridViewRow

        For Each dgr1 In Me.DataGridView1.Rows

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ")", con)
       
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

It said: Error 1 Operator '&' is not defined for types 'String' and 'System.Windows.Forms.DataGridViewCell'. I don't know what values I should enter. This must be a simple stuff but I am just struck.

[Johan Hakkesteegt]

Yep, that's why the best first step in learning this stuff is the Try Catch statement. okay, so don't panic, this problem is something that you are going to bump into a lot when coding, and the solution is very simple: for example dgr1.Cells(0) is in fact the cell as an object, not the cell's content. So all you need to do is get to the object's (your datagridview cell) content. try dgr1.Cells(0).Value, and see what it says next.

My advice is free, and you may get what you paid for.

[rajulama]

Thank you very very much. Following code works:

Try
Dim dgr1 As DataGridViewRow

        For Each dgr1 In Me.DataGridView1.Rows

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values('" & dgr1.Cells(0).Value & "', '" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "')", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    Catch ex As Exception
        MsgBox(ex.ToString)  

End Try

But I just wonder why I am getting one extra blank row in my database table.

[Johan Hakkesteegt]

That is because the datagridview offers an empty line at the bottom for the user to enter new rows. You can prevent this row from being entered into your database by checking if all cells are empty or not. So instead of putting dgr1.Cells(0).Value directly into the insert statement, you could do something like this:

If Not dgr1.Cells(0).Value & dgr1.Cells(1).Value & dgr1.Cells(2).Value & etc. = "" Then
Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values('" & dgr1.Cells(0).Value & "', '" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "')", con)
End If

My advice is free, and you may get what you paid for.

[rajulama]

But I am getting two blank rows. One is the obvious one but I am getting one another row on the top of the default blank row.

[rajulama]

got it got it! Thank you so much. I am all done! :laugh:

[Jay Royall]

You can also test if the row is the empty one at the bottom by using:

If Not dgr1.IsNewRow Then
' insert
End If

[Johan Hakkesteegt]

Your's is actually the better alternative for this case (I hadn't even thought of that one). The only advantage to the method I suggested is that if the user deletes the contents of all the cells of a row, but does not delete the row itself, the row will also not be inserted.

My advice is free, and you may get what you paid for.