How to Pass Value from Stored Procedure?

[daniness]

Hello All,

I've been working on a project, but have decided to take another direction on it, after much and continued struggling.

I started out trying to use data binding, but was advised to use straight SQL statements in the code. So what I'm trying to do now: I have 2 forms, frm1 and frm2. frm1 has a combobox, which is showing all the data fine from a table, and an OK button. Upon clicking on this button, I need frm2 to load, correctly populating its textboxes and comboboxes with the corresponding data, based on the selection made on frm1. This is what I have so far:

frm1:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Windows.Forms

Public Class frmLocations
    Dim conn As New SqlConnection("Data Source=f03d3s-dev01; Initial Catalog=dos_track;User Id=vbuser; Password=tran3;")

    Private Sub frmLocation_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn.Open()
        Call FillCombo()
    End Sub

    Private Sub FillCombo()

        Dim sql As String = "Select distinct Site,depot_refnbr From Locations order by Site asc"
        Dim cmd As New SqlCommand(sql, conn)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        Dim dt As New DataTable

        da.Fill(ds, "Locations")
        Dim dr As SqlDataReader = cmd.ExecuteReader
        Do While dr.Read
            cboLocations.Items.Add(dr("Site"))
        Loop
        dr.Close()
        cmd.Cancel()
        cmd.Dispose()
    End Sub

    Private Sub cboLocations_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboLocations.SelectedIndexChanged
    End Sub

    Private Sub btnLocationOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLocationOk.Click
        clsMain.Location_str_value = cboLocations.Text
        'clsMain.Location_depot_refnbr = cboDepot.

        Dim f As New frmLocationInfo
        f.ShowDialog()
    End Sub

I also have a stored procedure to pass the desired value, depot_refnbr to frm2:

ALTER PROCEDURE dbo.sp_CorrespDepot

	@CorrespDepot varchar(50) output
AS
	/* SET NOCOUNT ON */ 
	SELECT DISTINCT depot.depot_name, depot.depot_refnbr
FROM         depot INNER JOIN
                      locations ON depot.depot_refnbr = locations.depot_refnbr
		RETURN

frm2:

Public Class frmLocationInfo
    Dim conn As New SqlConnection("Data Source=f03d3s-dev01; Initial Catalog=dos_track;User Id=vbuser; Password=tran3;")

    Private Sub frmLocationInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        conn.Open()
        Call PopulateTextboxes()
        Call FillDepotCombo()
    End Sub

    Private Sub PopulateTextboxes()
    End Sub

Private Sub FillDepotCombo()

        Dim sql As String = "Select distinct depot_name From Depot order by depot_name asc"
Dim dtDepot As New DataTable("Depot")

        Dim cmdCorrespDepot As New SqlCommand("sp_CorrespDepot", conn)
        cmdCorrespDepot.CommandType = Data.CommandType.StoredProcedure
        cmdCorrespDepot.Parameters.Add("@CorrespDepot", SqlDbType.VarChar, 50)

   With cmdCorrespDepot
            .Parameters.Add("@CorrespDepot", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput
        End With

        dtDepot.Load(cmdCorrespDepot.ExecuteReader())

 cboDepot.DataSource = dtDepot
        cboDepot.DisplayMember = "depot_name"
        cboDepot.ValueMember = "depot_refnbr"

conn.Close()
    End Sub
End Class

I also have a clsMain, in where I keep global variables. When debugging, after clicking on the OK button on frm1, I keep receiving the error, "String[1]: the Size property has an invalid size of 0". Can someone please advise on this?

 

[jmcilhinney]

I started out trying to use data binding, but was advised to use straight SQL statements in the code.

The first point to note is that that's like saying that you started out driving to work but were advised to eat salad for lunch. The two have no connection whatsoever. Maybe you mean that you started out creating a Data Source and letting the IDE generate the objects and data-bindings for you. Data-binding is only a link between a list of data and a control. How that list of data came to be populated is nothing whatsoever to do with data-binding.

As for the question, it might have something to do with this:

        cmdCorrespDepot.Parameters.Add("@CorrespDepot", SqlDbType.VarChar, 50)

   With cmdCorrespDepot
            .Parameters.Add("@CorrespDepot", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput
        End With

Given that you are calling Add twice, it should be clear that you are adding two parameters. You're using the same name twice though, so I think that that should probably throw an exception. If not, the second parameter would replace the first, and the second parameter doesn't have a size specified, i.e. it would default to 0. Why the second call to Add when you can tack the Direction property assignment onto the first one just as you have for the second?

Also, your sproc doesn't make sense. What's the parameter for? You never actually assign a value to it?

 

[daniness]

Hi jmcilhinney and all,

As you can tell, I'm quite the novice at this and am trying to muddle my way through it. I appreciate any help I can get.

As you suggested, I've changed the statement to add parameters to:

With cmdCorrespDepot
            .Parameters.Add("@CorrespDepot", SqlDbType.VarChar, 50).Direction = ParameterDirection.InputOutput
            '.Parameters("@CorrespDepot").Value = depot_refnbr
        End With

...and that seems to be okay now.

As for the sproc, I've changed it to:

ALTER PROCEDURE dbo.sp_CorrespDepot

	@CorrespDepot varchar(50) output,
	@Depot_Nbr int
AS
	/* SET NOCOUNT ON */ 
	SELECT DISTINCT depot.depot_name
FROM         depot INNER JOIN
                      [COLOR="Red"]locations ON depot.depot_refnbr = locations.depot_refnbr
                      where depot_name = @CorrespDepot[/COLOR]
	
	RETURN

The parameter, @CorrespDepot, is to designate what the corresponding depot is and populate the Depot combobox with it as frm2 loads, based on the location that was selected on frm1.

Any insight would be appreciated. Thanks.

 

[daniness]

"Procedure or Function...expects parameter...which was not supplied" error

Hello All,

I'm now receiving the following error, ""Procedure or Function 'sp_CorrespDepot' expects parameter '@CorrespDepot', which was not supplied" after selecting a location on frm1 and clicking the "OK" button.

Here is my stored procedure:

ALTER PROCEDURE sp_CorrespDepot
(
	@CorrespDepot varchar(50) output,
	@Depot_Nbr int)
	
	as
	SELECT DISTINCT depot.depot_name 
FROM         depot INNER JOIN
                      locations ON depot.depot_refnbr = locations.depot_refnbr
                      where depot_name = @CorrespDepot

Any assistance would be appreciated.

 

[jmcilhinney]

Again your sproc doesn't make sense. You've specified two parameters, one as an output parameter and one as an input parameter. The input parameter you're not using at all and the output parameter you're trying to use the value of instead of assigning a value to.

 

[daniness]

Hi JM,

How would I pass a value from frm1 to the input parameter? I'm trying to pass the depot_refnbr, which would come along with the selection made on the combobox there for Location into the @Depot_Nbr parameter. I'm then trying to pass this value to @CorrespDepot to have frm2's Depot combobox to load with the corresponding depot populated.

Can you please make a recommendation on a book that I could reference for this? Thanks in advance!

 

[jmcilhinney]

Forget your form for the moment. Your form is never going to do anything useful if the SQL code doesn't work. Think about it. If you have an input parameter then you should be using its value inside the SQL code. If you don't then what's the point of passing the data in in the first place? If you have an output parameter then you should be assigning a value to it in the SQL. If you don't, how are you going to be able to use that value in the calling code?

So, ask yourself what you're actually trying to achieve. Exactly what data to do you need to achieve it? That will be the input parameter(s) and you'll use that data in the SQL code. Exactly what data will it produce? If it's individual values then you would use output parameters for that. If it's a list of values from the result of a query then you won't be using output parameters at all. You'll just be reading the result set.