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'

error msg : value of type string cannot be converted to 'system.data.sqlclient.sqlcommand' — Daark Sdie
– Daark Sdie, Commented Apr 15, 2019 at 7:53
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 — Steve
– Steve, Commented Apr 15, 2019 at 7:53

Steve · Accepted Answer · 2019-04-15 08:15:19Z

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.

edited Apr 15, 2019 at 8:15

answered Apr 15, 2019 at 7:58

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Daark Sdie Over a year ago

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 Over a year ago

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 Over a year ago

thanks bro your advice help me a lot now my application work

Bart Hofland · Accepted Answer · 2019-04-15 07:55:04Z

0

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)"

answered Apr 15, 2019 at 7:55

Bart Hofland

3,9251 gold badge18 silver badges25 bronze badges

1 Comment

Daark Sdie Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related