Sp won't work, SQL does ????? RESOLVED

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

General SQL Server Forums

New to SQL Server Programming

Sp won't work, SQL does ????? RESOLVED

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 06:41:13

I am using a function to fill a listbox in asp.net If I run it using the sql statement in the code, it works. if I use the sp I get an error saying the sp expects @strBookingNo which wasn't supplied. I have stepped through the function and there is a value in txNewBookingNo.text. Here's my function - the part commented out is the part that works,but I'd rather do it through an sp. I've included my sp too

Function GetFacilitiesBookings()

Dim MySQL As String = "spRB_GetFacilitiesBookings"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text)))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet

myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")

Return ddlDataSet

'Dim strSQL As String

'strSQL = "Select * from tblRB_FacilitiesBookings where FB_BookingNo = " & CInt(Me.txtNewBookingNo.Text) & " and FB_Cancelled =0"
'Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConn)
'Dim ddlDataset As New DataSet
'ddlDataset.Clear()

'myDataAdapter.Fill(ddlDataset, "tblRB_FacilitiesBookings")
'Return ddlDataset

End Function

CREATE Procedure [spRB_GetFacilitiesBookings]

@intBookingNo integer

as

Select * from tblRB_FacilitiesBookings where
FB_BookingNo = @strBookingNo and
FB_Cancelled = 0
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 06:42:52

coz it's @intBookingNo

typo?

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 06:47:42

Try this

Alter Procedure [spRB_GetFacilitiesBookings]

@intBookingNo integer

as

Select * from tblRB_FacilitiesBookings where
FB_BookingNo = @intBookingNo and
FB_Cancelled = 0

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 06:50:04

Sorry folks - I do have @strBookingNo in my sp, but got my knuckles rapped last week for calling an integer @str..... I changed it be correct in my post.

Have you any other ideas ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 06:54:30

Did running this in Query Analyser give any error?

spRB_GetFacilitiesBookings yourNumber

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 06:59:16

No, it worked - it returned the records. I have used an sp in a similar function to bind a dropdownlist and a listbox, but I am not passing any parameters in those cases. I wondered if it is trying to fill the listbox before it fills txtNewBookingNo - posting back somewhere that I've missed

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 07:00:57

the parameter you created in your app must correspond to the parameter name in the sp

--------------------
keeping it simple...

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 07:07:51

It does - see my explanation earlier.

Kristen
Test

22859 Posts

Posted - 2005-10-04 : 07:33:08

Is there a different owner for any of the objects between QA and runtime?

I would suggest you put "dbo." in front of all object names [SProc, Table, View, Trigger, Function etc.) to prevent accidental use of a non-dbo object (its faster too)

Does

myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")

expect the columns to be in a particular order, or are they "mapped" somewhere?

I would suggest you never use SELECT * as in

Select * from tblRB_FacilitiesBookings where
FB_BookingNo = @intBookingNo and
FB_Cancelled = 0

but list the columns explicitly, then you can a) be sure that there are in an order you have determined (if that is important) and b) that any new columns added in the future, which are clearly not needed, will not be included - they will of course be slowing performance - particularly if the query would otherwise be "covered" by an index, or the new columns have large text data in them etc.

You probably ought to have

SET NOCOUNT ON

immediately after the "AS" to stop the SProc sending an addition recordset back to report "n Rows Processed"

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 07:39:04

dbo is the owner for all the objects. Thanks for your comments, but I believe the sp is never running because the parameter isn't being passed from the web page. I think I will have to leave it with the SQL in my code. I was just trying to be tidy and use an sp.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 07:41:04

it's actually not just being tidy but also a security concern

sp is the way to go especially if you're executing sql queries directly in your app

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-04 : 07:52:28

"I believe the sp is never running because the parameter isn't being passed from the web page"

Ah!

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)

is using MySQL - which is "spRB_GetFacilitiesBookings"

so I reckon its being called without the parameter, whereas "Cmd" looks to be all set up with SProc name and Parameter, but not used thereafter??

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 08:41:47

Are you able to alter my code ?

Function GetFacilitiesBookings()

Dim MySQL As String = "spRB_GetFacilitiesBookings"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text)))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet

myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")

Return ddlDataSet
End Function

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-04 : 08:55:01

Ive sorted it ! I've marked the line I had to add.

Thanks for everyone's help.

Dim MySQL As String = "spRB_GetFacilitiesBookings"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text)))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet
***** myDataAdapter.SelectCommand = Cmd *****
myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")

Return ddlDataSet

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 09:02:22

so that it is Client side problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources