[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

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
0
noobe1
Asked:
noobe1
  • 2
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
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


0
 
noobe1Author Commented:
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?
0
 
Ammar GaffarSoftware EngineerCommented:
Ho noobe1,
Yes you have to loop in all rows in your datatable or your datagrid both of them are possible to be accessed, There is many ways to call stored procedures, but the easiest one like attached code
  Dim Cmd As New SqlCommand
        With Cmd
            .Connection = New SqlConnection("Your Connection String Here")
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Your Stored Procedure Name"
            .Parameters.AddWithValue("Parameter Name 1", PassedValue1)
            .Parameters.AddWithValue("Parameter Name 2", PassedValue2)
            .Parameters.AddWithValue("Parameter Name N", PassedValueN)
            Try
                .Connection.Open()
                .ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                .Connection.Close()
            End Try

        End With

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
@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"))
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now