parameters with Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

parameters with Stored Procedure

Author

Topic

angelica
Starting Member

6 Posts

Posted - 2008-06-24 : 03:28:29

I have this parameter problem which has been bothering me for quite some time. Being the first time using SPROC I can use some direction please.

I have the SP below - supplierFID and supplierID I ve dimmed because I am not sure if I should include these 2 parameters since they are generated by the database on server.

Im trying to populate a combobox with an alias of 2 fields from 2 different tables. Can someone pls tell me if the stored Proc is correct?

Furthermore I have vb.Net code to access the SPROC where I declare the parameters as follows:

I appreciate some direction please. Any help with code??

[CODE]Private Sub FillLASP(ByVal cbo As ComboBox)

Dim query As String = "LA_Supp"
Dim Ordersupplier As String
Try

Using cmd As New SqlCommand(query, MyConn)

cmd.CommandType = CommandType.StoredProcedure
'cmd.Parameters.AddWithValue("@supplierFID", "supplierFID")
'cmd.Parameters.AddWithValue("@supplierID", "supplierID")
cmd.Parameters.AddWithValue("@LANo", "LANo")
cmd.Parameters.AddWithValue("@suppName", "suppName")

cmd.Parameters("LANo").Direction = ParameterDirection.Output
cmd.Parameters("@suppName").Direction = ParameterDirection.Output
MyConn.Open()
cmd.ExecuteNonQuery()
suppName = cmd.Parameters("@suppName").Value
LANo = cmd.Parameters("@LANo").Value

OrderSupplier = "LANo" & "-" & "suppName"

cboBookSu.DisplayMember = "OrderSupplier"
cboBookSu.ValueMember = "LANo"
End Using

MyConn.Close()[/CODE]

[CODE]ALTER PROCEDURE [dbo].[LA_Supp]
-- Add the parameters for the stored procedure here,----dont know
-- @supplierFID int,
-- @supplierID int @LANo nvarchar(15) OUTPUT,
@LAID int OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT tb_LA.LANo, (tb_LA.LANo + '-' + tb_supplier.suppName) AS OrderSupplier
FROM tb_LA INNER JOIN tb_supplier ON tb_LA.supplierFID =tb_supplier.supplierID
Order by LANo DESC;

END[/CODE]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-24 : 04:02:31

How many records will your select statement return?
I guess if you dont use the supplierID as a parameter for the stored proc your procedure will return more than one record.

angelica
Starting Member

6 Posts

Posted - 2008-06-24 : 04:57:45

Actually I need the SPROC to return

an alias consisting of 2 fields(LANo + SuppName) in the displayMember

LANo in the ValueMember

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-24 : 05:31:08

quote:
Originally posted by angelica

Actually I need the SPROC to return

an alias consisting of 2 fields(LANo + SuppName) in the displayMember

LANo in the ValueMember

But i need to know the NUMBER OF RECORDS the query will return.

angelica
Starting Member

6 Posts

Posted - 2008-06-24 : 05:34:15

at the moment I have only five records but it will eventually grow to about 30 records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources