Calling a SQL Stored Procedure? - help

[Idodontnet]

Hello,

I’m have one heard time trying to get data back from a SP on my SQL server...

Here is what I have from looking all over the net to get it working...

Dim conn = New SqlConnection("Data Source=SQLsvr5;Initial Catalog=Orders;Persist Security Info=True;User ID=userx;Password=123123")
conn.Open()

Dim sqlcomm As New SqlCommand
sqlcomm.Connection = conn

sqlcomm.CommandType = CommandType.StoredProcedure
sqlcomm.CommandText = "sp_Get_itemName"
sqlcomm.Parameters.Add("@ItemID", SqlDbType.Int)
sqlcomm.Parameters("@ItemID").Value = "5"
sqlcomm.Parameters.Add("@Name", SqlDbType.NVarChar, 100)
sqlcomm.Parameters("@Name").Direction = ParameterDirection.Output


sqlcomm.ExecuteNonQuery()
TextBox1.Text = sqlcomm.Parameters("@Name").Value.ToString

So this tries to Run a SP named "sp_Get_itemName"
- It sends the value of 5 for the Parameter of @ItemID
- I want to get back the field value for "Name"
- I get the ERROR:
“Procedure or function sp_Get_itemName has too many arguments specified.”

I know that the SP spits out 3 fields "ID", "Name" and "Description" when I run it from SQL server management studio


Any help would be appreciated, Thanks!
Shane

 

[jmcilhinney]

First up, why are you specifying the type of a parameter as Int and then supplying a String?

sqlcomm.Parameters.Add("@ItemID", SqlDbType.[B][U][COLOR="red"]Int[/COLOR][/U][/B])
sqlcomm.Parameters("@ItemID").Value = [B][U][COLOR="red"]"5"[/COLOR][/U][/B]

Secondly, I advise that rather than those two separate lines, just use this one:

sqlcomm.Parameters.AddWithValue("@ItemID", 5)

The parameter type is inferred from the value.

As for the question, can we see the contents of the sproc?

 

[wengwashere]

Is the Name parameter in your SP set as OUTPUT?

E.G.
CREATE PROCEDURE sp_Get_itemName
@ItemId INT, @Name VARCHAR(50) OUTPUT AS BEGIN ....
END

 

[Idodontnet]

Thanks for all the infor and help, I will give it a go in just bit...

--The SP looks like this--
Create Procedure [dbo].[sp_Get_itemName]
@ItemID Int
as
SELECT [ID]
,[Name]
,[Description]
FROM [Orders].[dbo].[Product]
Where ID = @ItemID
GO
--end sp--

As for "why are you specifying the type of a parameter as Int and then supplying a String"
- well I was hoping the computer would have mercury and ignore my ignorant use of ""...
Best of all I like your 2nd idea of letting the computer figure the type via the value.

Thanks, will give it a go and let you know.
Shane

 

[jmcilhinney]

Your sproc only has one parameter and you are passing two. That's why you are getting an error message saying that you have too many parameters.

The data retrieved by the sproc is returned in the result set, not via parameters. You are calling ExecuteNonQuery on your command when your sproc is specifically a query, i.e. a SELECT statement. You need to either call ExecuteReader and then use a DataReader to read the records or else use a DataAdapter and call Fill to populate a DataTable with the whole result set. If you actually only want the first column of the first row then you can call ExecuteScalar to get just that value.