|
 |
asp_databases thread: Problems with EXEC----HELP!!!!
Message #1 by "Salman Ahmed" <salman.ahmed@r...> on Mon, 17 Sep 2001 18:01:20
|
|
Help,
Cannot figure out what the problem is.
Here is what I have in ASP+++++++++++++++++++++++++++++++++
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout=40
Conn.Open = "DSN=LapDomA;uid=Bully;pwd=pswer"
Conn.CursorLocation = 2
SET rs = Server.CreateObject("ADODB.Recordset")
SqlCmd = "exec sp_find_CANDIDATE '" & CUSERID & "' "
rs.Open SqlCmd,Conn
Here is my stored procedure++++++++++++++++++++++++++++++++
CREATE PROCEDURE [sp_find_CANDIDATE]
(
@CUSERID [varchar](50)
)
AS
SET NOCOUNT ON
DECLARE @MyString1 [nvarchar](100)
DECLARE @MyString [nvarchar](1000)
SELECT @MyString1=N' WHERE [IntelliBrain].[dbo].[INDIVIDUALTEST].
[CUSERID] = ' + @CUSERID
EXEC(N'SELECT * FROM [IntelliBrain].[dbo].[INDIVIDUALTEST]' + @MyString1 )
Here is the error+++++++++++++++++++++++++++++++++
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Syntax error
converting the varchar value 'fdlfd' to a column of data type int.
The error shows in line containg
'rs.Open SqlCmd,Conn'
Message #2 by David Cameron <dcameron@i...> on Tue, 18 Sep 2001 09:32:16 +1000
|
|
I think that 'fdlfd' has been passed through for your value of @CUSERID.
Judging from the name of the field CUSERID it is integer auotnumber field.
You are setting 'fdlfd' equal to an int field in the WHERE clause. You
should set @CuserID as data type int and only pass through int values. Also
is there a reason for using the exec statement? This edited stored proc
should do the same work and will run faster:
CREATE PROCEDURE sp_find_CANDIDATE
@CUSERID Int
AS
SET NOCOUNT ON
SELECT *
FROM INDIVIDUALTEST
WHERE CUSERID = @CUSERID
You might also want to name the fields you want explicitly, rather then
using *. It is a performance hit. Also you do not have to specify the
database and the user in front of every column and table, unless you are
accessing another database. [] are only needed if the column and table names
have spaces in them.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Salman Ahmed [mailto:salman.ahmed@r...]
Sent: Tuesday, 18 September 2001 4:01 AM
To: ASP Databases
Subject: [asp_databases] Problems with EXEC----HELP!!!!
Help,
Cannot figure out what the problem is.
Here is what I have in ASP+++++++++++++++++++++++++++++++++
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout=40
Conn.Open = "DSN=LapDomA;uid=Bully;pwd=pswer"
Conn.CursorLocation = 2
SET rs = Server.CreateObject("ADODB.Recordset")
SqlCmd = "exec sp_find_CANDIDATE '" & CUSERID & "' "
rs.Open SqlCmd,Conn
Here is my stored procedure++++++++++++++++++++++++++++++++
CREATE PROCEDURE [sp_find_CANDIDATE]
(
@CUSERID [varchar](50)
)
AS
SET NOCOUNT ON
DECLARE @MyString1 [nvarchar](100)
DECLARE @MyString [nvarchar](1000)
SELECT @MyString1=N' WHERE [IntelliBrain].[dbo].[INDIVIDUALTEST].
[CUSERID] = ' + @CUSERID
EXEC(N'SELECT * FROM [IntelliBrain].[dbo].[INDIVIDUALTEST]' + @MyString1 )
Here is the error+++++++++++++++++++++++++++++++++
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Syntax error
converting the varchar value 'fdlfd' to a column of data type int.
The error shows in line containg
'rs.Open SqlCmd,Conn'
Message #3 by Salman.Ahmed@r... on Wed, 19 Sep 2001 09:58:31 -0400
|
|
Thanks David , I will try that!
Ouch, that is nasty stored proc to debug.
I have had a quick look at it, sorry no time for a deeper analysis. From
what I see you are trying generate a SQL statement based on whether a
number of values have been passed through or not. ie if they are 0 then
they add nothing to the WHERE clause.
There are a number of different ways of approaching this problem. One
approach is the one that you have taken. The other approach, which I think
is a better one, is to write it into the where clause. Something like this:
CREATE PROCEDURE qryMyProc
@var1 VarChar(20), @Var2 VarChar(20)
AS
SET NOCOUNT ON
SELECT * -- Never use *, even if you do want all fields, it is
slower
FROM MyTable
WHERE (field1 = @var1 OR @var1 IS NULL) AND
(field2 = @var2 OR @var2 IS NULL)
In this case I have passed through NULL when things are not to be added to
the WHERE clause. Aside this is a better option to use than 0, as 0 for
varChar Fields is '0' and NULL is the same for all field types. We had a
situation like this where people had a search option where they could enter
between 1 and 6 Search criteria that were ANDed together. We stared off
using a SQL string generated in the page, but ended building a stored proc
along the lines of the one above. This should be faster than a generated
SQL string.
If what I have suggested will not work for some reason then there are a few
tings that I can suggest that you can do.
1. To help me debug this I would need the SQL string that the ASP page
generates. It would also help to have a brief description of the tables and
their fields.
2. Don't execute the SQL string generated in the stored proc, read it into
an output parameter, or just get the value from a recordset. Once you have
seen the string the error should be apparent. This is the same principle as
using Response.Write to show a SQL string.
One last comment, I will help you with this problem, but I would prefer it
if you addressed questions to the list, rather than to me directly.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Salman.Ahmed@r... [mailto:Salman.Ahmed@r...]
Sent: Tuesday, 18 September 2001 10:47 PM
To: dcameron@i...
Subject: [asp_databases] RE: Problems with EXEC----HELP!!!!
David,
Thanks for your response. Actually, the column @CUSERID is a varchar(50).
That is why I am confused why I am getting an error. Also I just showed a
part of a big stored proc. I am sending you the whole thing. If you have
time maybe
you can give me some suggestions. I will really appreciate that. Thanks
again for your kind response.!
--------------------------------------------------- ASP
Code---------------------------
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout=40
Conn.Open = "DSN=LapDomA;uid=Brain;pwd=Brain"
Conn.CursorLocation = 2
SET rs = Server.CreateObject("ADODB.Recordset")
SqlCmd = "exec sp_find_CANDIDATE '" & CORPUSER & "' , '" & CANDIDATE &
"' , '" & SUBJECT1 & "' , '" & SUBJECT2 & "', '" & SUBJECT3 & "', '" &
SUBJECT4 & "', '" & SCORE1 & "' , '" & SCORE2 & "', '" & SCORE3 & "', '" &
SCORE4 & "', '" & STATE & "' , '" & COUNTRY & "' , '" & CDAY & "' , '" &
CMONTH & "' , '" & CYEAR & "' "
rs.Open SqlCmd,Conn
---------------------------------------------------Stored
Proc---------------------------
CREATE PROCEDURE [sp_find_CANDIDATE]
(@CUSERID [varchar](50),
@CANDIDATE [int],
@SUBJECTID1 [int],
@SUBJECTID2 [int],
@SUBJECTID3 [int],
@SUBJECTID4 [int],
@SCORE1 [int],
@SCORE2 [int],
@SCORE3 [int],
@SCORE4 [int],
@STATE [varchar](50),
@COUNTRY [varchar](50),
@DAY [int],
@MONTH [int],
@YEAR [int]
)
AS
SET NOCOUNT ON
DECLARE @MyString1 [nvarchar](100)
DECLARE @MyString2 [nvarchar](100)
DECLARE @MyString3 [nvarchar](100)
DECLARE @MyString4 [nvarchar](100)
DECLARE @MyString5 [nvarchar](100)
DECLARE @MyString6 [nvarchar](100)
DECLARE @MyString7 [nvarchar](100)
DECLARE @MyString8 [nvarchar](100)
DECLARE @MyString9 [nvarchar](100)
DECLARE @MyString10 [nvarchar](100)
DECLARE @MyString11 [nvarchar](100)
DECLARE @MyString12 [nvarchar](100)
DECLARE @MyString13 [nvarchar](100)
DECLARE @MyString14 [nvarchar](100)
DECLARE @MyString [nvarchar](1000)
IF @CANDIDATE = 1 BEGIN
SELECT @MyString1=N' WHERE [IntelliBrain].[dbo].[INDIVIDUALTEST].[CUSERID]
= ' + @CUSERID + Right(3,@CUSERID)
END
ELSE
BEGIN
SELECT @MyString1= ' WHERE 1 =1 '
END
IF @SUBJECTID1 <> 0 BEGIN
SELECT @MyString2 = ' AND
[IntelliBrain].[dbo].[INDIVIDUALTEST].[SUBJECTID1] ='
END
ELSE
BEGIN
SELECT @MyString2 = ' AND 1 =1'
END
IF @SUBJECTID2 <> 0 BEGIN
SELECT @MyString3 = ' AND
[IntelliBrain].[dbo].[INDIVIDUALTEST].[SUBJECTID2] ='
END
ELSE
BEGIN
SELECT @MyString3 = ' AND 1 = 1'
END
IF @SUBJECTID3 <> 0 BEGIN
SELECT @MyString4 = ' AND
[IntelliBrain].[dbo].[INDIVIDUALTEST].[SUBJECTID3] ='
END
ELSE
BEGIN
SELECT @MyString4 = ' AND 1 = 1'
END
IF @SUBJECTID4<> 0 BEGIN
SELECT @MyString5 = ' AND
[IntelliBrain].[dbo].[INDIVIDUALTEST].[SUBJECTID4] ='
END
ELSE
BEGIN
SELECT @MyString5 = ' AND 1 = 1'
END
IF @SCORE1<> 0 BEGIN
SELECT @MyString6 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[SCORE1] >
='
END
ELSE
BEGIN
SELECT @MyString6 = ' AND 1 = 1'
END
IF @SCORE2<> 0 BEGIN
SELECT @MyString7= ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[SCORE2] >
= '
END
ELSE
BEGIN
SELECT @MyString7 = ' AND 1 = 1'
END
IF @SCORE3<> 0 BEGIN
SELECT @MyString8= ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[SCORE3] >
='
END
ELSE
BEGIN
SELECT @MyString8 = ' AND 1 = 1'
END
IF @SCORE4<> 0 BEGIN
SELECT @MyString9 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[SCORE4] >
= '
END
ELSE
BEGIN
SELECT @MyString9 = ' AND 1 = 1'
END
IF @STATE= NULL BEGIN
SELECT @MyString10 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[STATE]
='
END
ELSE
BEGIN
SELECT @MyString10 = ' AND 1 = 1'
END
IF @COUNTRY= NULL BEGIN
SELECT @MyString11 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[COUNTRY]
='
END
ELSE
BEGIN
SELECT @MyString11 = ' AND 1 = 1'
END
IF @DAY<> 0 BEGIN
SELECT @MyString12 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[DAY] >
= '
END
ELSE
BEGIN
SELECT @MyString12 = ' AND 1 = 1'
END
IF @MONTH<> 0 BEGIN
SELECT @MyString13 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[MONTH] >
= '
END
ELSE
BEGIN
SELECT @MyString13 = ' AND 1 = 1'
END
IF @YEAR<> 0 BEGIN
SELECT @MyString14 = ' AND [IntelliBrain].[dbo].[INDIVIDUALTEST].[YEAR] >
='
END
ELSE
BEGIN
SELECT @MyString14 = ' AND 1 = 1'
END
EXEC(N'SELECT * FROM [IntelliBrain].[dbo].[INDIVIDUALTEST]' +@MyString1 +
@MyString2 + @SUBJECTID1)
( Here I did not finish the Stored Proc)
------------------------------------------------------------------------Error--------------------------------------
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Syntax error
converting
the varchar value 'fd@f...' to a column of data type int.
----- Forwarded by Salman Ahmed/Radisys_Corporation/US on 09/18/01 08:38 AM
-----
David Cameron
<dcameron@i... To: "ASP Databases"
-now.com> <asp_databases@p...>
cc:
09/17/01 07:32 Subject: [asp_databases] RE:
Problems with
PM EXEC----HELP!!!!
Please respond
to "ASP
Databases"
I think that 'fdlfd' has been passed through for your value of @CUSERID.
Judging from the name of the field CUSERID it is integer auotnumber field.
You are setting 'fdlfd' equal to an int field in the WHERE clause. You
should set @CuserID as data type int and only pass through int values. Also
is there a reason for using the exec statement? This edited stored proc
should do the same work and will run faster:
CREATE PROCEDURE sp_find_CANDIDATE
@CUSERID Int
AS
SET NOCOUNT ON
SELECT *
FROM INDIVIDUALTEST
WHERE CUSERID = @CUSERID
You might also want to name the fields you want explicitly, rather then
using *. It is a performance hit. Also you do not have to specify the
database and the user in front of every column and table, unless you are
accessing another database. [] are only needed if the column and table
names
have spaces in them.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Salman Ahmed [mailto:salman.ahmed@r...]
Sent: Tuesday, 18 September 2001 4:01 AM
To: ASP Databases
Subject: [asp_databases] Problems with EXEC----HELP!!!!
Help,
Cannot figure out what the problem is.
Here is what I have in ASP+++++++++++++++++++++++++++++++++
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout=40
Conn.Open = "DSN=LapDomA;uid=Bully;pwd=pswer"
Conn.CursorLocation = 2
SET rs = Server.CreateObject("ADODB.Recordset")
SqlCmd = "exec sp_find_CANDIDATE '" & CUSERID & "' "
rs.Open SqlCmd,Conn
Here is my stored procedure++++++++++++++++++++++++++++++++
CREATE PROCEDURE [sp_find_CANDIDATE]
(
@CUSERID [varchar](50)
)
AS
SET NOCOUNT ON
DECLARE @MyString1 [nvarchar](100)
DECLARE @MyString [nvarchar](1000)
SELECT @MyString1=N' WHERE [IntelliBrain].[dbo].[INDIVIDUALTEST].
[CUSERID] = ' + @CUSERID
EXEC(N'SELECT * FROM [IntelliBrain].[dbo].[INDIVIDUALTEST]' + @MyString1 )
Here is the error+++++++++++++++++++++++++++++++++
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Syntax error
converting the varchar value 'fdlfd' to a column of data type int.
The error shows in line containg
'rs.Open SqlCmd,Conn'
David Cameron
<dcameron@i... To: "ASP Databases"
-now.com> <asp_databases@p...>
cc:
09/17/01 07:32 Subject: [asp_databases] RE: Problems with
PM EXEC----HELP!!!!
Please respond
to "ASP
Databases"
I think that 'fdlfd' has been passed through for your value of @CUSERID.
Judging from the name of the field CUSERID it is integer auotnumber field.
You are setting 'fdlfd' equal to an int field in the WHERE clause. You
should set @CuserID as data type int and only pass through int values. Also
is there a reason for using the exec statement? This edited stored proc
should do the same work and will run faster:
CREATE PROCEDURE sp_find_CANDIDATE
@CUSERID Int
AS
SET NOCOUNT ON
SELECT *
FROM INDIVIDUALTEST
WHERE CUSERID = @CUSERID
You might also want to name the fields you want explicitly, rather then
using *. It is a performance hit. Also you do not have to specify the
database and the user in front of every column and table, unless you are
accessing another database. [] are only needed if the column and table
names
have spaces in them.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Salman Ahmed [mailto:salman.ahmed@r...]
Sent: Tuesday, 18 September 2001 4:01 AM
To: ASP Databases
Subject: [asp_databases] Problems with EXEC----HELP!!!!
Help,
Cannot figure out what the problem is.
Here is what I have in ASP+++++++++++++++++++++++++++++++++
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout=40
Conn.Open = "DSN=LapDomA;uid=Bully;pwd=pswer"
Conn.CursorLocation = 2
SET rs = Server.CreateObject("ADODB.Recordset")
SqlCmd = "exec sp_find_CANDIDATE '" & CUSERID & "' "
rs.Open SqlCmd,Conn
Here is my stored procedure++++++++++++++++++++++++++++++++
CREATE PROCEDURE [sp_find_CANDIDATE]
(
@CUSERID [varchar](50)
)
AS
SET NOCOUNT ON
DECLARE @MyString1 [nvarchar](100)
DECLARE @MyString [nvarchar](1000)
SELECT @MyString1=N' WHERE [IntelliBrain].[dbo].[INDIVIDUALTEST].
[CUSERID] = ' + @CUSERID
EXEC(N'SELECT * FROM [IntelliBrain].[dbo].[INDIVIDUALTEST]' + @MyString1 )
Here is the error+++++++++++++++++++++++++++++++++
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Syntax error
converting the varchar value 'fdlfd' to a column of data type int.
The error shows in line containg
'rs.Open SqlCmd,Conn'
|
|
|
View Cart
