Solved

VB.NET Update MSSQL using Stored Procedure

Posted on 2010-08-14
4
653 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.Net - KeyPress Event 4 36
Creating a route in asp.net webforms 2 26
VB.Net Report Printing Issue 3 34
Error handling in asp.net site 5 27
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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