Solved

VB.NET Update MSSQL using Stored Procedure

Posted on 2010-08-14
4
656 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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