Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Hi,

that a easy thing, like the error says, you can′t combine data retrieval (thats selecting or outputting the result *and* assigning values to variables. If you want to put out the *CASENO*, put in in a variable and do a SELECT @.CASENO afterwards fetching the values into the variables.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This may be doing what Jens suggested, but I'm not sure. I changed:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

to this:

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

and it worked.

Thanks for the reply Jens.

|||

Nice to hear that this helped, but if you want to select (output the case number, I am not sure after you last reply) you *could* do that:

SELECT @.Caseno = dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

Select @.Caseno

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Change
SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME.....

to

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME......

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Parameter passing to a stored procedure

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@.domainUserID NVARCHAR(50) ,

@.EmployeeID NVARCHAR(50) ,

@.loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@.EmployeeID, @.loginType, @.domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page...

--begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
--

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?no.

Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand

insertMatchedUser = new SqlCommand;
insertMatchedUser.CommandText = "USP_MactchUser"
insertMatchedUser.CommandType = CommandType.StoredProcedure
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters("@.EmployeeID").Value = lblEmployeeID.Text.Trim()
insertMatchedUser.Parameters("@.domainUserID").Value = lblDomainuserID.Text.Trim()

insertMatchedUser.Connection = SqlConnection1;
SqlConnection1.Open()
'conn openned
insertMatcheduser.ExecuteNonQuery()

insertMatchedUser.Dispose()
SqlConnection1.Dispose()
End Sub|||Also:

CREATE PROCEDURE USP_MactchUser
@.domainUserID NVARCHAR(50) ,
@.EmployeeID NVARCHAR(50) ,
@.loginType bit = 0
AS

the bit data type literal should not have single quotes.sql

Parameter entered has the wrong format.. you must be ....me!

Hi

I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox

this is the parameter definition:
<asp:ControlParameterControlID="tbComment"Name="Comment"PropertyName="Text"Type="String"/>

Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.

I use aSqlDataSource with automatic generated script.

look forwart to a solution

walter

Do you use a stored procedure or ...? make sure the parameter (comment) is not passed to a parameter of type int.