How to Insert value to sql server database from data table vb.net

Question

I'm making a application with two database and want to insert my second database table with value from first database table they have same column but on different database. so i make a datatable to populate my data from first database table and try to insert it on my second database

so i make a datatable to populate my data from first database table and try to insert it on my second database using data from datatable but always error cause string value

 Dim ds As DataSet = New DataSetMasBrgSpring
 Dim dt As DataTable
 dt = ds.Tables("DataTable1")
 If DataGridView1.Rows.Count > 0 Then
 dt.Rows.Clear()
 For i = 0 To DataGridView1.Rows.Count - 1
 dt.Rows.Add(DataGridView1.Rows(i).Cells(0).Value, etc.. 
 Dim sql1 As String
 Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
 Dim command As New Data.SqlClient.SqlCommand
 command = "insert into MasbrgSpring (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"
 command.Parameters.Add("@kodec", SqlDbType.VarChar)
 etc...
 For i As Integer = 0 To DataGridView1.Rows.Count - 1
 command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
 command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
 etc...
 str.Open()
 COMMANDSQL(sql1)
 str.Close()
 Next
 End If
End Sub

this is sub commandsql:

 Public Sub COMMANDSQL(ByVal S As String)
 sqlcom = New SqlCommand(S, str)
 sqlcom.CommandType = CommandType.Text
 sqlcom.ExecuteNonQuery()
End Sub

This is the error I get :

value of type string cannot be converted to 'system.data.sqlclient.sqlcommand'

• Marc Guillot: Can you share the exact error message that you get ?
• Daark Sdie: error msg : value of type string cannot be converted to 'system.data.sqlclient.sqlcommand'
• Steve: Please show the code of the COMMANDSQL method. It seems that you don't need at all tables, adapters and datasets, but just the SqlCommand

Answer 1

There are many problems in your code. Of course the immediate one that triggers the compilation error is the fact that you use command instead of command.CommandText to assign the query to the command, but there are others.

 If DataGridView1.Rows.Count > 0 Then
 Dim command As New Data.SqlClient.SqlCommand
 command.Text = "insert into MasbrgSpring 
 (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) 
 VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"
 command.Parameters.Add("@kodec", SqlDbType.VarChar)
 .... add all the other parameters ....
 ' Don't forget to set the connection to the command
 command.Connection = str
 str.Open()
 ' Now loop over the rows and for each one execute the insert command
 For i As Integer = 0 To DataGridView1.Rows.Count - 1
 command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
 command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
 ..... 
 command.ExecuteNonQuery()
 Next
 str.Close()
 End If
End Sub

Here I have removed all the stuff about SqlDataAdapter, DataTable and DataSet. They are not needed at all when you execute directly a SqlCommand.
Finally the SqlCommand prepared in the loop is the one to be executed, if you create a new SqlCommand inside that SQLCOMMAND method then you need to give it the same info you have already given to the command used in the loop.

So there is no need also for SQLCOMMAND method but you can directly call ExecuteNonQuery on the prepared SqlCommand.

A final advice. It is a very bad programming practive to have a global SqlConnection object. It keeps resources locked on the client and on the server and if, for any reason, you get an exception, the connection remains open with side effects not immediately obvious when you get problems in other data code blocks. ADO.NET has a Connection Pooling functionality far more advanced than the one you are trying to re-create keeping a global connection object. Instead use the Using statement around your Sql commands

Using sqlCon = New SqlConnection(......)
 If DataGridView1.Rows.Count > 0 Then
 Dim command As New Data.SqlClient.SqlCommand
 command.Connection = sqlCon
 ..... the previous code....
 End If
End Using

The using statement will help you to close and dispose the connection, while the connection pooling removes the overhead required to reconnect to the same database with the same connection string.

• Daark Sdie: so how to fix the problem ? i just want to learn about how to send data from first database to other database can you show me link to learn that ?
• Steve: You have your data inside that grid (DataGridView1) right? The code I have shown is the code required to extract the data from that grid and send it to the database you open using the connectionstring.
• Daark Sdie: thanks bro your advice help me a lot now my application work

Answer 2

It's probably this line:

command = "insert into MasbrgSpring (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"

I think you mean:

command.CommandText = "insert into MasbrgSpring (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"

• Daark Sdie: i got this error msg ExecuteNonQuery: CommandText property has not been initialized