Passing parameter to stored procedure

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Can someone please take a look at this code and tell me what I am
doing wrong. Basically, I am passing an ID value from the asp.net
page to a stored procedure for use in the WHERE clause of the SELECT
statement. I want the user to select the ID from a list and then
return all the details for editing the entry.

I've included the code and the stored procedure.

The error I am getting is:
System.Data.SqlClient.SqlException: Line 11: Incorrect syntax near
'45'.

Line 11 is: dtrPatient = cmd.ExecuteReader()

The '45' is the patientID being passed.

Code:
Dim PatientID As Integer
Dim cmd As SqlCommand
Dim dtrPatient As SqlDataReader
PatientID = Request.Params("PatientID")
cmd = dbConn.CreateCommand()
With cmd
.CommandText = "spPatientMaintain"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@PatientID", SqlDbType.Int, 4)
.Parameters(0).Value = PatientID
End With

dtrPatient = cmd.ExecuteReader()

Stored Procedure (SqlServer):
CREATE PROCEDURE scott.spPatientMaintain (@PatientID as int)
AS

EXEC
('SELECT dbo.tblDoctor.DoctorName,
dbo.tblPatientAddress.PatientAddressTypeID,
dbo.tblPatientAddress.Address, dbo.tblPatientAddress.City,
dbo.tblPatientAddress.StateID,
dbo.tblPatientAddress.ZipCode,
dbo.tblPatientAddressType.AddressTypeDescription,
dbo.tblStateList.StateAbbreviation,
dbo.tblPatient.PatientID,
dbo.tblPatient.PatientNumber, dbo.tblPatient.LastName,
dbo.tblPatient.FirstName, dbo.tblPatient.HomePhoneNumber,
dbo.tblPatient.WorkPhoneNumber,
dbo.tblPatient.MobilePhoneNumber, dbo.tblPatient.Comments,
dbo.tblPatient.DoctorID,
dbo.tblPatient.EmailAddress
FROM dbo.tblPatientAddressType INNER JOIN
dbo.tblPatientAddress ON
dbo.tblPatientAddressType.PatientAddressTypeID =
dbo.tblPatientAddress.PatientAddressTypeID INNER JOIN
dbo.tblStateList ON
dbo.tblPatientAddress.StateID = dbo.tblStateList.StateID INNER JOIN
dbo.tblDoctor INNER JOIN
dbo.tblPatient ON dbo.tblDoctor.DoctorID =
dbo.tblPatient.DoctorID ON dbo.tblPatientAddress.PatientID =
dbo.tblPatient.PatientID
WHERE (dbo.tblPatientAddress.PatientAddressTypeID = 1) and
(dbo.tblPatient.PatientID=' + @PatientID)
GO


Any help is GREATLY appreciated.
 
Scott,

I don't see anything wrong here except maybe the Select statement. I did something similar and it works fine:

VB code:
Dim cmd As SqlCommand
Dim dtrPatient As SqlDataReader
cmd = cn.CreateCommand()
With cmd
.CommandText = "NG_sp"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ID", SqlDbType.Int, 4)
.Parameters(0).Value = 10249
End With
dtrPatient = cmd.ExecuteReader()
dtrPatient.Read()
MessageBox.Show(dtrPatient("OrderDate"))

SP:
ALTER PROCEDURE NG_Sp (@ID as int)
AS

EXEC ('SELECT * from Orders where OrderId =' + @id)
RETURN

Try to run the query in VS.NET from Server explorer and see if that succeeds. Upon running it in VS.NET, it will prompt you to enter the PatientID. Does that succeed?

If yes, then the problem is in the Application. if it fails then the problem is in the Select statement.

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
 
Hussein-

THANKS!!! I'm an asp.net newbie and did not realize I
could run stored procedures from within VS.NET like that
to check syntax, etc. It was a problem with the select
statement in the stored procedure. I rebuilt it and it
works fine now. Again, many thanks.

Scott

-----Original Message-----
Scott,

I don't see anything wrong here except maybe the Select
Click to expand...
statement. I did something similar and it works fine:
VB code:
Dim cmd As SqlCommand
Dim dtrPatient As SqlDataReader
cmd = cn.CreateCommand()
With cmd
.CommandText = "NG_sp"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ID", SqlDbType.Int, 4)
.Parameters(0).Value = 10249
End With
dtrPatient = cmd.ExecuteReader()
dtrPatient.Read()
MessageBox.Show(dtrPatient("OrderDate"))

SP:
ALTER PROCEDURE NG_Sp (@ID as int)
AS

EXEC ('SELECT * from Orders where OrderId =' + @id)
RETURN

Try to run the query in VS.NET from Server explorer and
Click to expand...
see if that succeeds. Upon running it in VS.NET, it will
prompt you to enter the PatientID. Does that succeed?
If yes, then the problem is in the Application. if it
Click to expand...
fails then the problem is in the Select statement.
I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft
Click to expand...
Strategic Technology Protection Program and to order your
FREE Security Tool Kit, please visit
 
You are welcome, Scott!

I'm glad that your problem is resolved. Good luck!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
 
Scott,

One other point: why use the EXEC statement in T/SQL?
Stored procedures are prepared when created and this
speeds up their execution. However, statements submitted
through EXEC are not prepared when the stored procedure is
created, thus losing some of the performance advantages of
stored procedures (although SQL Server 2000 has some
clever query caching which helps a bit).

How about changing your stored procedure to remove the
EXEC statement, e.g.:

CREATE PROCEDURE scott.spPatientMaintain (@PatientID as
int)
AS
SELECT dbo.tblDoctor.DoctorName, ...
FROM dbo.tblPatientAddressType, ...
WHERE (dbo.tblPatientAddress.PatientAddressTypeID = 1)
AND dbo.tblPatient.PatientID = @PatientID
GO
 
Back
Top