CODEWITHSUNDEEP
asp.netvb.netado.netdatatable
JayOnDotNet
JayOnDotNet

Reputation: 398

Insert datatable into a database table from a stored procedure

I have a datatable with some rows I need to insert that datatable in to database using a stored procedure. 

 For Each row As DataRow In dt.Rows
            cmd.Parameters.Add(New MySqlParameter("@DOWNLOAD_ID", dt.Rows(0)("DOWNLOAD_ID").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@FINALY_FIELDCODE", dt.Rows(0)("FINALY_FIELDCODE").ToString().Replace("|", "").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@UNITS", dt.Rows(0)("UNITS").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@CURRECY_CODE", dt.Rows(0)("CURRENCY_CODE").ToString()))
        Next
        cmd.ExecuteNonQuery()

When i do this I got an exception {"Parameter '@DOWNLOAD_ID' has already been defined."} How to get rid of this exception and can we insert the datatable with out for loop. Can any one help me

Upvotes: 0

Views: 2384

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

You should create separate MySqlCommands and execute them directly:

Using mysqlConnection = New MySqlConnection(connectionString)
    mysqlConnection.Open()
    For Each row As DataRow In dt.Rows
        Using cmd = New MySqlCommand("StoredProcedureName", mysqlConnection)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New MySqlParameter("@DOWNLOAD_ID", dt.Rows(0)("DOWNLOAD_ID").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@FINALY_FIELDCODE", dt.Rows(0)("FINALY_FIELDCODE").ToString().Replace("|", "").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@UNITS", dt.Rows(0)("UNITS").ToString()))
            cmd.Parameters.Add(New MySqlParameter("@CURRECY_CODE", dt.Rows(0)("CURRENCY_CODE").ToString()))
            cmd.ExecuteNonQuery()
        End Using
    Next
End Using

Upvotes: 1

Related Questions