Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.NET Update MSSQL using Stored Procedure

Posted on 2010-08-14
4
Medium Priority
?
660 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:Meir Rivkin
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 2000 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:Meir Rivkin
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

926 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