Solved

VB.NET Update MSSQL using Stored Procedure

Posted on 2010-08-14
4
650 Views
Last Modified: 2012-05-10
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
Comment
Question by:noobe1
  • 2
4 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 33439538
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
 

Author Comment

by:noobe1
ID: 33439598
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
 
LVL 12

Accepted Solution

by:
Ammar Gaffar earned 500 total points
ID: 33440076
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 33440098
@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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now