asked on

VB.NET Update MSSQL using Stored Procedure

Hi,

I used the following method to get data from the database. Now, I've created a stored procedure that updates the database. My question is, how should I execute the update process?

Should I loop through the datatable, pass all the required parameters and call a ExecuteNonQuery command?

Thanks.

Public Shared Function GetData(ByVal username As String) As DataTable
Dim dt As New DataTable("Address")
dt.Columns.Add("Add1")
dt.Columns.Add("Add2")
dt.Columns.Add("ID")
dt.Columns.Add("C_ID")
dt.Columns.Add("DT_Created")

Dim con As New SqlConnection
con.ConnectionString = GetConnectionString()
con.Open()

Try
Dim cmdGetData As New SqlCommand("osp_GetData", con)
cmdGetData.CommandType = CommandType.StoredProcedure
cmdGetData.Parameters.Add("@Tablename", SqlDbType.NVarChar).Value = username
Dim reader As SqlDataReader = cmdGetData.ExecuteReader()

If reader.HasRows Then
Do While reader.Read()
Dim row As DataRow = dt.NewRow
row("ID") = reader("ID")
row("C_ID") = reader("C_ID")
row("DT_Created") = reader("DT_Created")
row("Add1") = reader("Add1")
row("Add2") = reader("Add2")
dt.Rows.Add(row)
Loop
End If

Catch ex As Exception
MsgBox(ex.ToString)
Finally
con.Close()
End Try

Return dt

End Function

Meir Rivkin

i don't understand what is the problem with your code.
it seems that u call the store procedure correctly and pass the arguments as u should.
In order to get the result sets from the stored procedure, the best way is to use a DataReader to get the results:

'A Temporary Variable to retrieve all columns in a row and fill them in Object arrayDim FieldValues(1) As Object

Dim SQLDataTable As New DataTable
' add the columns:
'SQLDataTable.Columns.Add(col1
'SQLDataTable.Columns.Add(col2

While (SqlDataReader.Read)
SqlDataReader.GetValues(FieldValues)
SQLDataTable.Rows.Add(FieldValues)
End While

noobe1

ASKER

Sorry if I wasn't clear. I know how to get the data from the database and put it into a datatable.

Now I want to update the database after I've made changes to the datatable. The changes are done via a datagridview.

I've already created the required stored procedure for updating the database but I'm not sure what is the best way to pass the data to the stored procedure. Should I loop through the datatable, pass all the required parameters and call a ExecuteNonQuery command?

ASKER CERTIFIED SOLUTION

Ammar Gaffar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Meir Rivkin

@noobe1

to your question of the best way to pass the data to the stored procedure, u have 2 options:
the simplest way to add parameters and respective values is using @Africans way (.AddWithValue).
the alternative is something like this:

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("@Tablename", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Calling Stored Procedures from VB.NET"))