What's wrong with my stored proc? 1

[abs2003]

I have stored proc

CREATE PROCEDURE [dbo].[bookstore_select_Order_BySchoolName] 
	@SchoolName varchar(255)
AS

SELECT Orders.OrderID, Orders.dcOrderNumber, Orders.BilltoName, Orders.BilltoAddr1, Orders.BilltoCity, Orders.BilltoState, Orders.ccAmount, Orders.Total, 
                      psSchool.SchoolName, psSchool.SchoolID,psSchool.CONO, psSchool.CSNO
FROM         Orders INNER JOIN
                      psSchool ON Orders.CONO = psSchool.CONO AND Orders.CSNO = psSchool.CSNO
WHERE    (psSchool.SchoolName like '%' + @SchoolName + '%')
ORDER By Orders.BilltoName
GO

I can use SQL Query Analyzer to test it. It works fine. When call it from my ASP.NET, it return nothing.

Here's my code...

Dim objConnection As New SqlConnection(Application("strSQLConnection"))
Dim sqlCommand1 As New SqlCommand("bookstore_select_Order_BySchoolName", objConnection)
Dim sqlAdapter As New SqlClient.SqlDataAdapter(sqlCommand1)
Dim sqlDataSet As New DataSet
sqlCommand1.CommandType = CommandType.StoredProcedure
sqlCommand1.Parameters.Add(New sqlClient.SqlParameter("@BillingAddr", SqlDbType.Char, 255))
sqlCommand1.Parameters("@BillingAddr").Value = "My School"
 objConnection.Open()
 sqlAdapter.Fill(sqlDataSet, "theTable")

 If objConnection.State <> ConnectionState.Closed Then objConnection.Close()
        objConnection.Dispose()
        sqlCommand1.Dispose()

        Return sqlDataSet

        sqlAdapter.Dispose()
        sqlDataSet.Dispose()

 

[gmmastros]

This looks like a vb.net issue. I recommend you post in that forum forum796 to get a quicker response.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Humph... sproc parameter is @SchoolName. In VB.NET thingamajig, you are using @BillingAddr. So if SQLCommand uses named parameters, shouldn't you get an error or something?

Also: @SchoolName is of type varchar, while parameter is of type char (SQLDBType.Char). Probably irrelevant due to auto-trimming but anyway...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[abs2003]

vongrunt,
sorry. I meant @SchoolName instead of @billingAddr. The only wierd thing is it runs fine in SQL Query Analyze. But when I call from my ASP.NET, it returns nothing.

Good new is you are absolutely right about data type. I changed it to SQLDBType.VarChar. Problem solved!
I'd give you 3 stars if I can.

Thank you vongrunt

 

[Ceal]

In your post, you stated, "I can use SQL Query Analyzer to test it. It works fine. When call it from my ASP.NET, it returns nothing."

When I use my Stored Proc in Query Analyzer, all I get is "command was completed succesfully". If I use a regular query, I get output but with Stored Procs (including others I've copied), the "command was completed succesfully" is all I get.

How do you view output in your Stored Proc in Query Analyzer? Thanks.

 

[abs2003]

Ceal,

EXEC "MyStored_Proc" "myParameter"

Press F5 (Not Ctrl + F5)

Make sure you choose the right database to pull from.

Good luck

 

[Ceal]

abs2003

Thanks very much for your reply. I replaced the ALTER with EXEC and that didn't work. There is a template in SQL Server with cursor output but I couldn't get it to work and it seemed a lot more complicated than I needed. Do I need to use the template or was there something else you did.

Ceal

 

[Ceal]

ABS200

I was using the wrong template in SQL Server. I used the cursor output rather than just output. Anyway, it works now. I can see my Stored Proc output in SQL Server Query Analyzer Results Pane.

This is the code I used for the Northwinds database that comes with SQL Server (in case someone wants to try it). Thank you very much.

-- =============================================
-- Create procedure with OUTPUT Parameters
-- =============================================

CREATE PROCEDURE [See_Output_in_Stored_Proc]
(@CustomerID_1 nchar(5) OUTPUT)
AS

GO
DECLARE @CustomerIDA nchar
EXECUTE See_Output_in_Stored_Proc @CustomerIDA OUTPUT
SELECT DISTINCT CustomerID FROM dbo.Customers
GO