locked
Passing parameters to a stored procedure in Visual Basic RRS feed

  • Question

  • Hi peeps,

    I need some help with passing parameters to a stored procedure from my visual basic code.

    Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.

    I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.

    The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.

    The code for the procedure is listed below:

    /*

    Name: usp_display_all_users

    Description: Displays activeuser, personid, comment from table: pswds

    Userid and sort from table: people

    Where the username is like the parameter supplied.

    Both tables joined on personid

    Author: Iain Blackwood

    Modification log: Change

    Description Date Changed by

    Created proc 02/01/07 Iain Blackwood

    */

    ALTER PROCEDURE usp_display_all_users

    (

    @searchStr nvarchar(128) =''

    )

    AS

    SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

    FROM dbo.pswds INNER JOIN

    dbo.people ON dbo.pswds.personid = dbo.people.personid

    WHERE (dbo.people.sort LIKE @searchStr + '%')

    ORDER BY dbo.people.sort

     

    The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.

    I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.

    Private Sub fillDataGrid()

    ' I NEED TO:

    ' 1: Fill the data set with all Accounts

    ' 2: Diplay the Data to the data grid

    ' delcare a new SQL connection

    sqlCon = New SqlConnection(conStr)

    ' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE

    Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"

    comStrPeople += " FROM pswds INNER JOIN"

    comStrPeople += " people ON pswds.personid = people.personid"

    comStrPeople += " ORDER BY sort"

    ' Display the command string: TEMPOARY

    testlbl2.Text = comStrPeople

    ' Declare a new SQL data adapter

    sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)

    Try

    ' Declare a new dataset

    sqlDataSet = New DataSet

    ' fill the sql data adapter with data from dataset: called PeoplePswds

    sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")

    ' Fill the forms datagrid view with data from the Dataset table PeoplePswds

    DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

    Catch ex As Exception

    ' Display suitable error message

    MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)

    End Try

    End Sub

     

    I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.

    Thanx Flakkie

    Friday, January 5, 2007 11:30 AM

Answers

  • Hi Jens,

    Thanks for your response, it was exactly what I needed, I thought that I had got it right on friday but was having a small problem with the last line. Was really just me being a novice with Visual Basic.

    SqlDataAdapter da = new SqlDataAdapter(cmd,YourConnection);

    I was trying to declare the SqlDataAdapter by putting the sql Command parameter and not the String name of the Stored procedure.

    However after a little cursing I managed to sort it

     

    For any one that is having problems with the same thing in Visual Basic my Code is posted below.

    '-------------------------------------------------------------------------------

    ' Function to fill the form with the initial data

    ' When the application starts the first view shows all users in the system

    '-------------------------------------------------------------------------------

    Private Sub initialView()

    ' Declare and initalise the Sql connection

    Dim sqlCon As SqlConnection = New SqlConnection(conStr)

    ' Open the connection.

    ' It safe to do this as we cant get past login form without valid connection

    sqlCon.Open()

    Try

    ' Declare and initalise a new SQL Command

    Dim command As SqlCommand = New SqlCommand()

    ' Set Command text to stored procedure name

    command.CommandText = "usp_display_all_users"

    ' Set the command type to Stored procedure

    command.CommandType = CommandType.StoredProcedure

    ' Add parameter/s to the command. Depends on the Stored procedure

    command.Parameters.Add("@searchStr", SqlDbType.NVarChar, 128)

    ' Declare a new SQL data adapter: pass the stored procedure name

    ' and the SQL connection variable

    Dim sda = New SqlDataAdapter("usp_display_all_users", conStr)

    Try

    ' Declare a new dataset

    sqlDataSet = New DataSet()

    ' fill the sql data adapter with data from dataset: called PeoplePswds

    sda.Fill(sqlDataSet, "PeoplePswds")

    ' Fill the forms datagrid view with data from the Dataset table PeoplePswds

    DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

    Catch ex1 As Exception

    ' display error message

    MessageBox.Show("Unable to retrieve User Data at initialView error 001" + ex1.Message)

    End Try

    Catch ex2 As Exception

    ' Display error message

    MessageBox.Show("Unable to retrieve User Data at initialView error 002" + ex2.Message)

    Throw

    Finally

    ' close the connection

    sqlCon.Close()

    End Try

    End Sub

     

    NOTE: The code that answers the question on "passing parameters to stored procedure" is located after the first try clause in this code.

    Once again Thx to Jens for his Superb answer it really helped me.

    Thx Flakky

    Monday, January 8, 2007 10:10 AM
  • I found some more Stuff that relates to this subject for anyone that may need more help

    Basically the code in the post above works for calling the stored procedure but it does not correctly assign the @searchStr parameter value to the SqlCommand parameter and so the stored procedure when called uses the default Stored procedure value. In this case an empty string as can be seen in the code for the procedure below:

    ALTER PROCEDURE usp_display_all_users

    @searchStr NVARCHAR(128)=''

    AS

    SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

    FROM dbo.pswds

    INNER JOIN dbo.people

    ON dbo.pswds.personid = dbo.people.personid

    WHERE (dbo.people.sort LIKE @searchStr + '%')

    ORDER BY dbo.people.sort

    Using the code from the previous post will always result in the same view in the data grid as the procedure only ever uses the default value.

    To call the stored procdure correctly assiging the parameters required for the procedure from Visual Basic code you must:

    1. Open the connection to the data base
    2. Create a new SqlCommand
    3. Assign the Stored Procedure properties to the SqlCommand
    4. Create a new SqlDataAdapter passing it the SqlCommand created in part 3
    5. Create a new DataSet
    6. Fill the SqlDataAdapter with the new DataSet and assign it a RelationShip name (reference)
    7. Set your DataGrid's DataSource to the default view of the DataSet created above
    8. Finally Close the connection to the database

    My updated code for these 8 steps is located below

    Private Sub drawView(ByVal searchStr As String)

    ' Open the connection.

    ' It safe to do this as we cant get past login form without valid connection

    sqlCon.Open()

    Try

    sqlCmd = New SqlCommand

    ' Declare and initalise a new SQL Command

    With sqlCmd

    ' Set Command text to stored procedure name

    .CommandText = "usp_display_all_users"

    ' Set the command type to Stored procedure

    .CommandType = CommandType.StoredProcedure

    ' Add parameter/s to the command. Depends on the Stored procedure

    .Parameters.Add("@searchStr", SqlDbType.NVarChar, 128).Value = searchStr

    'add the conection to the command

    .Connection = sqlCon

    End With

    ' Declare a new SQL data adapter: pass the stored procedure name

    ' and the SQL connection variable

    Dim sda = New SqlDataAdapter(sqlCmd)

    Try

    ' Declare a new dataset

    sqlDataSet = New DataSet()

    ' fill the sql data adapter with data from dataset: called PeoplePswds

    sda.Fill(sqlDataSet, "PeoplePswds")

    ' Fill the forms datagrid view with data from the Dataset table PeoplePswds

    DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

    Catch ex2 As Exception

    ' display error message

    MessageBox.Show("Unable to retrieve User Data at drawView error 001 " + ex2.Message)

    End Try

    Catch ex1 As Exception

    ' Display error message

    MessageBox.Show("Unable to retrieve User Data at drawView error 002 " + ex1.Message)

    Throw

    Finally

    ' close the connection

    sqlCon.Close()

    End Try

    End Sub

    I hope this Clarifies any problems that you may be having with this subject

    Flakky 

    Wednesday, January 10, 2007 12:25 PM

All replies

  • Hi Flakky, here is the C# equivalent, should be easy for you to switch this over to VB:

    SqlCommand cmd = new SqlCommand();

    cmd.CommandText = "usp_display_all_users";

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@searchStr ",SqlDbType.NVarChar,128);

    SqlDataAdapter da = new SqlDataAdapter(cmd,YourConnection);



    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Friday, January 5, 2007 3:25 PM
  • Hi Jens,

    Thanks for your response, it was exactly what I needed, I thought that I had got it right on friday but was having a small problem with the last line. Was really just me being a novice with Visual Basic.

    SqlDataAdapter da = new SqlDataAdapter(cmd,YourConnection);

    I was trying to declare the SqlDataAdapter by putting the sql Command parameter and not the String name of the Stored procedure.

    However after a little cursing I managed to sort it

     

    For any one that is having problems with the same thing in Visual Basic my Code is posted below.

    '-------------------------------------------------------------------------------

    ' Function to fill the form with the initial data

    ' When the application starts the first view shows all users in the system

    '-------------------------------------------------------------------------------

    Private Sub initialView()

    ' Declare and initalise the Sql connection

    Dim sqlCon As SqlConnection = New SqlConnection(conStr)

    ' Open the connection.

    ' It safe to do this as we cant get past login form without valid connection

    sqlCon.Open()

    Try

    ' Declare and initalise a new SQL Command

    Dim command As SqlCommand = New SqlCommand()

    ' Set Command text to stored procedure name

    command.CommandText = "usp_display_all_users"

    ' Set the command type to Stored procedure

    command.CommandType = CommandType.StoredProcedure

    ' Add parameter/s to the command. Depends on the Stored procedure

    command.Parameters.Add("@searchStr", SqlDbType.NVarChar, 128)

    ' Declare a new SQL data adapter: pass the stored procedure name

    ' and the SQL connection variable

    Dim sda = New SqlDataAdapter("usp_display_all_users", conStr)

    Try

    ' Declare a new dataset

    sqlDataSet = New DataSet()

    ' fill the sql data adapter with data from dataset: called PeoplePswds

    sda.Fill(sqlDataSet, "PeoplePswds")

    ' Fill the forms datagrid view with data from the Dataset table PeoplePswds

    DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

    Catch ex1 As Exception

    ' display error message

    MessageBox.Show("Unable to retrieve User Data at initialView error 001" + ex1.Message)

    End Try

    Catch ex2 As Exception

    ' Display error message

    MessageBox.Show("Unable to retrieve User Data at initialView error 002" + ex2.Message)

    Throw

    Finally

    ' close the connection

    sqlCon.Close()

    End Try

    End Sub

     

    NOTE: The code that answers the question on "passing parameters to stored procedure" is located after the first try clause in this code.

    Once again Thx to Jens for his Superb answer it really helped me.

    Thx Flakky

    Monday, January 8, 2007 10:10 AM
  • I found some more Stuff that relates to this subject for anyone that may need more help

    Basically the code in the post above works for calling the stored procedure but it does not correctly assign the @searchStr parameter value to the SqlCommand parameter and so the stored procedure when called uses the default Stored procedure value. In this case an empty string as can be seen in the code for the procedure below:

    ALTER PROCEDURE usp_display_all_users

    @searchStr NVARCHAR(128)=''

    AS

    SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

    FROM dbo.pswds

    INNER JOIN dbo.people

    ON dbo.pswds.personid = dbo.people.personid

    WHERE (dbo.people.sort LIKE @searchStr + '%')

    ORDER BY dbo.people.sort

    Using the code from the previous post will always result in the same view in the data grid as the procedure only ever uses the default value.

    To call the stored procdure correctly assiging the parameters required for the procedure from Visual Basic code you must:

    1. Open the connection to the data base
    2. Create a new SqlCommand
    3. Assign the Stored Procedure properties to the SqlCommand
    4. Create a new SqlDataAdapter passing it the SqlCommand created in part 3
    5. Create a new DataSet
    6. Fill the SqlDataAdapter with the new DataSet and assign it a RelationShip name (reference)
    7. Set your DataGrid's DataSource to the default view of the DataSet created above
    8. Finally Close the connection to the database

    My updated code for these 8 steps is located below

    Private Sub drawView(ByVal searchStr As String)

    ' Open the connection.

    ' It safe to do this as we cant get past login form without valid connection

    sqlCon.Open()

    Try

    sqlCmd = New SqlCommand

    ' Declare and initalise a new SQL Command

    With sqlCmd

    ' Set Command text to stored procedure name

    .CommandText = "usp_display_all_users"

    ' Set the command type to Stored procedure

    .CommandType = CommandType.StoredProcedure

    ' Add parameter/s to the command. Depends on the Stored procedure

    .Parameters.Add("@searchStr", SqlDbType.NVarChar, 128).Value = searchStr

    'add the conection to the command

    .Connection = sqlCon

    End With

    ' Declare a new SQL data adapter: pass the stored procedure name

    ' and the SQL connection variable

    Dim sda = New SqlDataAdapter(sqlCmd)

    Try

    ' Declare a new dataset

    sqlDataSet = New DataSet()

    ' fill the sql data adapter with data from dataset: called PeoplePswds

    sda.Fill(sqlDataSet, "PeoplePswds")

    ' Fill the forms datagrid view with data from the Dataset table PeoplePswds

    DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

    Catch ex2 As Exception

    ' display error message

    MessageBox.Show("Unable to retrieve User Data at drawView error 001 " + ex2.Message)

    End Try

    Catch ex1 As Exception

    ' Display error message

    MessageBox.Show("Unable to retrieve User Data at drawView error 002 " + ex1.Message)

    Throw

    Finally

    ' close the connection

    sqlCon.Close()

    End Try

    End Sub

    I hope this Clarifies any problems that you may be having with this subject

    Flakky 

    Wednesday, January 10, 2007 12:25 PM
  • How to do that in Visual Basic using ADODB.Connection?
    Wednesday, May 23, 2012 2:07 PM