|
 |
asp_databases thread: How to code database variable in SQL statement?
Message #1 by "Bob" <lewisbd@y...> on Tue, 20 Jun 2000 17:14:53
|
|
I have spent several days trying to resolve the problem with the following
code. The variable "varMember" is not being read as coded, but if I
substitute a valid string, such as "MIGR", which is a MemberID in my
database, the string is read.
Do I need to code variables differently in SQL?
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = varMember", "DSN=srm"
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = 'MIGR'", "DSN=srm"
Any help in this regard will be very much appreciated.
Bob Lewis
Dominican Republic
Message #2 by mkrishna - Internet Mail <mkrishna@c...> on Tue, 20 Jun 2000 12:38:45 -0400
|
|
Enclose your variable with "&variable&"
in your case "&varMember&"
this shud work!!!
> -----Original Message-----
> From: Bob
> Sent: Tuesday, June 20, 2000 1:15 PM
> To: ASP Databases
> Subject: [asp_databases] How to code database variable in SQL
> statement?
>
> I have spent several days trying to resolve the problem with the following
>
> code. The variable "varMember" is not being read as coded, but if I
> substitute a valid string, such as "MIGR", which is a MemberID in my
> database, the string is read.
>
> Do I need to code variables differently in SQL?
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = varMember", "DSN=srm"
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = 'MIGR'", "DSN=srm"
>
> Any help in this regard will be very much appreciated.
>
> Bob Lewis
> Dominican Republic
>
Message #3 by "Ken Schaefer" <ken.s@a...> on Wed, 21 Jun 2000 13:16:56 +1000
|
|
strSQL = "SELECT * "
strSQL = strSQL & "FROM [Contacts Table] "
strSQL = strSQL & "WHERE MemberID ='" & varMember & "'"
oRSv.Open strSQL, "DSN=srm"
OK, tips that you need to follow:
a) Never do SELECT *, always enumerate the fields that you want to use. If
you want to know why, email me off list and I will send you my top 5 reasons
b) Always allocate your SQL statement to a variable, and then use that
variable when creating the recordset (see example above). Why? So that you
can Response.write(strSQL) and see what it actually being sent to the
database. If you had done this, you'd have seen straight away what your
problem was
c) Avoid using ODBC/DSNs if you can, and switch to OLEDB. It's faster, and
you'll have less '80004005' Unspecified Errors
Cheers
Ken
> I have spent several days trying to resolve the problem with the following
> code. The variable "varMember" is not being read as coded, but if I
> substitute a valid string, such as "MIGR", which is a MemberID in my
> database, the string is read.
>
> Do I need to code variables differently in SQL?
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = varMember", "DSN=srm"
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = 'MIGR'", "DSN=srm"
>
> Any help in this regard will be very much appreciated.
>
> Bob Lewis
> Dominican Republic
Message #4 by "Dana Coffey" <dcoffey@x...> on Tue, 20 Jun 2000 12:50:28 -0400
|
|
Hey Bob,
yes they are different...just struggled with one myself and got it to work.
Here's the code.
strSQL = "SELECT * "
strSQL = strSQL & "FROM wtnCompany "
strSQL = strSQL & "WHERE ID =" & request("coID")
hope that helped some
if you've already assigned the variable, just put it where the
"request("coID") portion resides.
DC
-----Original Message-----
From: Bob
Sent: Tuesday, June 20, 2000 5:15 PM
To: ASP Databases
Subject: [asp_databases] How to code database variable in SQL statement?
I have spent several days trying to resolve the problem with the following
code. The variable "varMember" is not being read as coded, but if I
substitute a valid string, such as "MIGR", which is a MemberID in my
database, the string is read.
Do I need to code variables differently in SQL?
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = varMember", "DSN=srm"
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = 'MIGR'", "DSN=srm"
Any help in this regard will be very much appreciated.
Bob Lewis
Dominican Republic
---
Message #5 by Martin Maurais <martin@t...> on Tue, 20 Jun 2000 12:22:03 -0700
|
|
Hi Bob the variable needs to be outside of the SQL string. But it would be
better if you were to do it in two steps.
1: sqlString = "SELECT * FROM [Contacts Table] WHERE
MemberID = " & varMember
2: oRSv.Open sqlString, "DSN=srm"
the first step create the string with you variable and the second execute
the query
hope it helps!
Bob wrote:
> I have spent several days trying to resolve the problem with the following
> code. The variable "varMember" is not being read as coded, but if I
> substitute a valid string, such as "MIGR", which is a MemberID in my
> database, the string is read.
>
> Do I need to code variables differently in SQL?
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = varMember", "DSN=srm"
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = 'MIGR'", "DSN=srm"
>
> Any help in this regard will be very much appreciated.
>
> Bob Lewis
> Dominican Republic
>
Message #6 by "Sam Johnston II" <sam.johnston@j...> on Tue, 20 Jun 2000 17:24:29 -0500
|
|
I have exactly the same problem, and have spent days... the key might be to
abandon the ODBC and connect using OLEDB.. In any event, did you find an
answer?
Sam
-----Original Message-----
From: Bob
Sent: Tuesday, June 20, 2000 5:15 PM
To: ASP Databases
Subject: [asp_databases] How to code database variable in SQL statement?
I have spent several days trying to resolve the problem with the following
code. The variable "varMember" is not being read as coded, but if I
substitute a valid string, such as "MIGR", which is a MemberID in my
database, the string is read.
Message #7 by "Mark Morgan" <mark.morgan@v...> on Wed, 21 Jun 2000 06:54:09 +0100
|
|
Bob,
This is a syntax thing, you need to concatenate the value of the varMember
to the SQL statement, i.e.
SELECT * FROM [Contacts Table] WHERE MemberID = " & "'" & varMember & "'"
I'm assuming the MemberID column is a character column.
Regards,
Mark.
-----Original Message-----
From: Bob
Sent: 20 June 2000 17:15
To: ASP Databases
Subject: [asp_databases] How to code database variable in SQL statement?
I have spent several days trying to resolve the problem with the following
code. The variable "varMember" is not being read as coded, but if I
substitute a valid string, such as "MIGR", which is a MemberID in my
database, the string is read.
Do I need to code variables differently in SQL?
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = varMember", "DSN=srm"
oRSv.Open "SELECT * FROM [Contacts Table] WHERE
MemberID = 'MIGR'", "DSN=srm"
Any help in this regard will be very much appreciated.
Bob Lewis
Dominican Republic
Message #8 by "Bob Lewis" <lewisbd@y...> on Wed, 21 Jun 2000 14:58:55 -0400
|
|
Thanks for your reply to my request for help. I'll give it a try.
Bob
-----Original Message-----
From: mkrishna - Internet Mail
Sent: Tuesday, June 20, 2000 12:39 PM
To: ASP Databases
Subject: [asp_databases] RE: How to code database variable in SQL
statemen t?
Enclose your variable with "&variable&"
in your case "&varMember&"
this shud work!!!
> -----Original Message-----
> From: Bob
> Sent: Tuesday, June 20, 2000 1:15 PM
> To: ASP Databases
> Subject: [asp_databases] How to code database variable in SQL
> statement?
>
> I have spent several days trying to resolve the problem with the following
>
> code. The variable "varMember" is not being read as coded, but if I
> substitute a valid string, such as "MIGR", which is a MemberID in my
> database, the string is read.
>
> Do I need to code variables differently in SQL?
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = varMember", "DSN=srm"
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = 'MIGR'", "DSN=srm"
>
> Any help in this regard will be very much appreciated.
>
> Bob Lewis
> Dominican Republic
>
Message #9 by "Sam Johnston II" <sam.johnston@j...> on Wed, 21 Jun 2000 14:59:21 -0500
|
|
Try '"& variable &"'
This worked for me!
-----Original Message-----
From: Bob Lewis
Sent: Wednesday, June 21, 2000 1:59 PM
To: ASP Databases
Subject: [asp_databases] RE: How to code database variable in SQL
statemen t?
Thanks for your reply to my request for help. I'll give it a try.
Bob
-----Original Message-----
From: mkrishna - Internet Mail
Sent: Tuesday, June 20, 2000 12:39 PM
To: ASP Databases
Subject: [asp_databases] RE: How to code database variable in SQL
statemen t?
Enclose your variable with "&variable&"
in your case "&varMember&"
this shud work!!!
> -----Original Message-----
> From: Bob
> Sent: Tuesday, June 20, 2000 1:15 PM
> To: ASP Databases
> Subject: [asp_databases] How to code database variable in SQL
> statement?
>
> I have spent several days trying to resolve the problem with the following
>
> code. The variable "varMember" is not being read as coded, but if I
> substitute a valid string, such as "MIGR", which is a MemberID in my
> database, the string is read.
>
> Do I need to code variables differently in SQL?
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = varMember", "DSN=srm"
>
> oRSv.Open "SELECT * FROM [Contacts Table] WHERE
> MemberID = 'MIGR'", "DSN=srm"
>
> Any help in this regard will be very much appreciated.
>
> Bob Lewis
> Dominican Republic
>
Message #10 by "Bob Lewis" <lewisbd@y...> on Wed, 21 Jun 2000 14:58:44 -0400
|
|
Sam,
Yes, I did find an answer, at least a solution. I'm still not sure why I was
having a problem hard coding the sql statement, but by using concatenation,
it works with no problem.
Below is the code that works. I have put asterisks above and below the line
that was giving me problems.
Let me know if this doesn't fix the problem for you.
Bob
---------------------------
<%
Dim varMember
varMember = Request.Form("memberid")
Response.Write varMember
Response.Write "<hr>"
Dim oRSv, sqlText
set oRSv=server.createobject("ADODB.recordset")
' *********************************************
sqlText = "SELECT * FROM [Contacts Table] WHERE MemberID = " & "'" &
varMember & "'" & ";"
' *********************************************
Response.Write (sqlText)
Response.Write "<hr>"
oRSv.open sqlText, "DSN=srm"
Response.Write oRSv("Full Name")
%>
-----Original Message-----
From: Sam Johnston II
Sent: Tuesday, June 20, 2000 6:24 PM
To: ASP Databases
Subject: [asp_databases] RE: How to code database variable in SQL
statement?
I have exactly the same problem, and have spent days... the key might be to
abandon the ODBC and connect using OLEDB.. In any event, did you find an
answer?
Sam
-----Original Message-----
From: Bob
Sent: Tuesday, June 20, 2000 5:15 PM
To: ASP Databases
Subject: [asp_databases] How to code database variable in SQL statement?
I have spent several days trying to resolve the problem with the following
code. The variable "varMember" is not being read as coded, but if I
substitute a valid string, such as "MIGR", which is a MemberID in my
database, the string is read.
|
|
|
View Cart
