Execute SQL statement with parameter from ASP page? [Archive]

Click to See Complete Forum and Search --> : Execute SQL statement with parameter from ASP page?

Alexander

03-23-2000, 04:10 AM

Hello,

Can anyone tell me how can I do that?

My ASP page look like this:

<%@ language = JScript %>
<HTML>
<BODY>
<%
//connect the database
strDBpath = "db1.mdb"
strDBlocation = Server.Mappath(strDBpath)
strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open(strSource)

kkkkk = 2
rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
= kkkkk")
%>
</BODY>
</HTML>

Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
However, if I changed the Execute statement with instant number like this:

rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")

It will work. Any help will be appreciated.

Alexander

mike

03-23-2000, 11:28 AM

To insert a variable into a SQL statement, build the SQL statement in a string
with the variable concatenated in and then execute the string.

Example:
Dim intID
Dim strSQL

intID = 2
strSQL = "SELECT * FROM MyData WHERE ItemID =" & intID
rsAllTalk = objConnection.Execute(strSQL)

Hope this helps

"Alexander" <alexander_chen@hotmail.com> wrote:
>
>Hello,
>
>Can anyone tell me how can I do that?
>
>My ASP page look like this:
>
><%@ language = JScript %>
><HTML>
><BODY>
><%
> //connect the database
> strDBpath = "db1.mdb"
> strDBlocation = Server.Mappath(strDBpath)
> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
> objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open(strSource)
>
> kkkkk = 2
> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>= kkkkk")
>%>
></BODY>
></HTML>
>
>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>However, if I changed the Execute statement with instant number like this:
>
>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")
>
>It will work. Any help will be appreciated.
>
>Alexander
>
>

jimmy cicjimmy

03-27-2000, 11:00 PM

if it were VB, you forgot to dim out what kkkkkk means
dim kkkkk as integer
Dont know alot about ASP but I am sure that is what your missing. Dont know
the lingo though.

"Alexander" <alexander_chen@hotmail.com> wrote:
>
>Hello,
>
>Can anyone tell me how can I do that?
>
>My ASP page look like this:
>
><%@ language = JScript %>
><HTML>
><BODY>
><%
> //connect the database
> strDBpath = "db1.mdb"
> strDBlocation = Server.Mappath(strDBpath)
> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
> objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open(strSource)
>
> kkkkk = 2
> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>= kkkkk")
>%>
></BODY>
></HTML>
>
>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>However, if I changed the Execute statement with instant number like this:
>
>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")
>
>It will work. Any help will be appreciated.
>
>Alexander
>
>

Ryan

03-28-2000, 03:57 AM

Alexander, I'll bet you that it doesn't work because you don't have single
quotes around kkkkkk. You don't need them when listing a number ( 2 for instance
), but you do if putting a string in you SQL.

Hope it helped.
ryan

"jimmy" cicjimmy wrote:
>
>if it were VB, you forgot to dim out what kkkkkk means
>dim kkkkk as integer
>Dont know alot about ASP but I am sure that is what your missing. Dont know
>the lingo though.
>
>
>
>
>"Alexander" <alexander_chen@hotmail.com> wrote:
>>
>>Hello,
>>
>>Can anyone tell me how can I do that?
>>
>>My ASP page look like this:
>>
>><%@ language = JScript %>
>><HTML>
>><BODY>
>><%
>> //connect the database
>> strDBpath = "db1.mdb"
>> strDBlocation = Server.Mappath(strDBpath)
>> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
>> objConnection = Server.CreateObject("ADODB.Connection")
>> objConnection.Open(strSource)
>>
>> kkkkk = 2
>> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>>= kkkkk")
>>%>
>></BODY>
>></HTML>
>>
>>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>>However, if I changed the Execute statement with instant number like this:
>>
>>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID =
2")
>>
>>It will work. Any help will be appreciated.
>>
>>Alexander
>>
>>
>

Martyn

03-28-2000, 09:12 AM

Alex,
you have to put WHERE ItemID = '" & KKKKK & "' To make it get a value from
a variable. See http://www.inquiry.com/techtips/asp_pro/answer.asp?pro=asp_pro&docID=4313
for a similar question answered by the ASP-Pro

Martyn

"Ryan" <parnell@iname.com> wrote:
>
>Alexander, I'll bet you that it doesn't work because you don't have single
>quotes around kkkkkk. You don't need them when listing a number ( 2 for
instance
>), but you do if putting a string in you SQL.
>
>Hope it helped.
>ryan
>
>"jimmy" cicjimmy wrote:
>>
>>if it were VB, you forgot to dim out what kkkkkk means
>>dim kkkkk as integer
>>Dont know alot about ASP but I am sure that is what your missing. Dont
know
>>the lingo though.
>>
>>
>>
>>
>>"Alexander" <alexander_chen@hotmail.com> wrote:
>>>
>>>Hello,
>>>
>>>Can anyone tell me how can I do that?
>>>
>>>My ASP page look like this:
>>>
>>><%@ language = JScript %>
>>><HTML>
>>><BODY>
>>><%
>>> //connect the database
>>> strDBpath = "db1.mdb"
>>> strDBlocation = Server.Mappath(strDBpath)
>>> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
>>> objConnection = Server.CreateObject("ADODB.Connection")
>>> objConnection.Open(strSource)
>>>
>>> kkkkk = 2
>>> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>>>= kkkkk")
>>>%>
>>></BODY>
>>></HTML>
>>>
>>>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>>>However, if I changed the Execute statement with instant number like this:
>>>
>>>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID =
>2")
>>>
>>>It will work. Any help will be appreciated.
>>>
>>>Alexander
>>>
>>>
>>
>

03-28-2000, 09:37 AM

I do see a syntax error in the select statement, try this

"select * from mydata where itemID = '" & kkkkk &"'"

those are single quote ' then double "
close by double single double

"Alexander" <alexander_chen@hotmail.com> wrote:
>
>Hello,
>
>Can anyone tell me how can I do that?
>
>My ASP page look like this:
>
><%@ language = JScript %>
><HTML>
><BODY>
><%
> //connect the database
> strDBpath = "db1.mdb"
> strDBlocation = Server.Mappath(strDBpath)
> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
> objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open(strSource)
>
> kkkkk = 2
> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>= kkkkk")
>%>
></BODY>
></HTML>
>
>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>However, if I changed the Execute statement with instant number like this:
>
>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")
>
>It will work. Any help will be appreciated.
>
>Alexander
>
>

Leo

03-28-2000, 10:56 AM

The key to whether you use single quotes around a variable is dependent on
the Field type in the database. Numeric type fields do not need them and
string type fields do need them

In the below example if the field IN THE DATABASE is a string type then you
must surround your variable in the single quotes

strSQL = "SELECT * FROM myData WHERE NAME = '" & strName & "'"

If the field in the database is numeric then (integer, single, etc) then
the single quotes are unnecessary.

strSQL = "SELECT * FROM myData WHERE ID = " & intID

Hope this helps and clears some of the confusion I have seen in previous
responses

Leo

"Ed" <Ed@antares-iti.com> wrote:
>
>I do see a syntax error in the select statement, try this
>
>"select * from mydata where itemID = '" & kkkkk &"'"
>
>those are single quote ' then double "
>close by double single double
>
>
>"Alexander" <alexander_chen@hotmail.com> wrote:
>>
>>Hello,
>>
>>Can anyone tell me how can I do that?
>>
>>My ASP page look like this:
>>
>><%@ language = JScript %>
>><HTML>
>><BODY>
>><%
>> //connect the database
>> strDBpath = "db1.mdb"
>> strDBlocation = Server.Mappath(strDBpath)
>> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
>> objConnection = Server.CreateObject("ADODB.Connection")
>> objConnection.Open(strSource)
>>
>> kkkkk = 2
>> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>>= kkkkk")
>>%>
>></BODY>
>></HTML>
>>
>>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>>However, if I changed the Execute statement with instant number like this:
>>
>>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID =
2")
>>
>>It will work. Any help will be appreciated.
>>
>>Alexander
>>
>>
>

Andi

03-30-2000, 10:33 PM

Hi Alexander

k = 2
rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID" + kkkkk)

Your previous statement doesn't work because "kkkk" is part of the SQL statement
not parameter.

Andrew Henson

03-31-2000, 01:19 PM

"Alexander" <alexander_chen@hotmail.com> wrote:
>
>Hello,
>
>Can anyone tell me how can I do that?
>
>My ASP page look like this:
>
><%@ language = JScript %>
><HTML>
><BODY>
><%
> //connect the database
> strDBpath = "db1.mdb"
> strDBlocation = Server.Mappath(strDBpath)
> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
> objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open(strSource)
>
> kkkkk = 2
> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>= kkkkk")
>%>
></BODY>
></HTML>
>
>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>However, if I changed the Execute statement with instant number like this:
>
>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")
>
>It will work. Any help will be appreciated.
>
>Alexander
>
>
Your first sql statement you are looking for all ItemID fields containing
kkkkk, as kkkkk is a string literal it MUST be surrounded by single quotes
ie 'kkkkk'

this should then work, based on my SQL Knowledge as I am learning ASP.

rgds

Andrew

Zhou Hao

04-11-2000, 06:57 AM

Hi,

Very simple,you just use:

rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID=" &
kkkkk)

Zhou Hao

"Alexander" <alexander_chen@hotmail.com> wrote:
>
>Hello,
>
>Can anyone tell me how can I do that?
>
>My ASP page look like this:
>
><%@ language = JScript %>
><HTML>
><BODY>
><%
> //connect the database
> strDBpath = "db1.mdb"
> strDBlocation = Server.Mappath(strDBpath)
> strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDBlocation
> objConnection = Server.CreateObject("ADODB.Connection")
> objConnection.Open(strSource)
>
> kkkkk = 2
> rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID
>= kkkkk")
>%>
></BODY>
></HTML>
>
>Unfortunately, it does not work on my Windows 2000 machine with IIS 5.0.
>However, if I changed the Execute statement with instant number like this:
>
>rsAllTalk = objConnection.Execute("SELECT * FROM MyData WHERE ItemID = 2")
>
>It will work. Any help will be appreciated.
>
>Alexander
>
>