Can i use asp within sql statements

knight · June 11th, 2004, 04:09 AM

how can i use asp statements within sql is it possible if yes pls
tell me write some examples pls
:D

Imar · June 11th, 2004, 04:43 AM

Hi there,

Well, it depends. If you construct your SQL statement in an ASP page, yes you can:

Code:

Dim SQL
SQL = "SELECT Col1, Col2 FROM MyTable WHERE ID = " & MyASPVariable

This will create a query that uses MyASPVariable in the WHERE clause of your SQL statement.
If you're using Stored Procedures, you can also pass ASP variables to those procedures as input parameters.

If you mean you can do something like this in a Stored Procedure

Code:

CREATE Procedure MyProc

AS

SELECT Col1, Col2, FROM MyTable WHERE ID = <%=MyASPVar%>

then the answer is no: you cannot do that.

Is this what you're after?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

knight · June 11th, 2004, 05:12 AM

it means we could not use asp tags within sql
is it illegile ? update tblRings set name='"&ringname&"',[desc]='"&desc&"', "<% if Request("s1")= "on" then size1="5.5" end if "
%> where item_id = " &Request("item_id")

pls tel me
or can u tell me other method to do that

Imar · June 11th, 2004, 05:18 AM

Did you read my post? I believe the answer is in there.
Yes, you can, if you construct the SQL in the ASP page. No, you can't if you're trying to use <%= %> syntax inside a stored procedure.

Where are you using this:

update tblRings set name='"&ringname&"',[desc]='"&desc&"', "<% if Request("s1")= "on" then size1="5.5" end if "
%> where item_id = " &Request("item_id")

In an ASP page? If so, you shouldn't use <% %> but simply concatenate your string, like you do with the desc variable. If you're trying to create a dynamic SQL statement, try this:

What is the size thingy supposed to do? If I were you, I'd stay far away from words that sound like reserved words, like desc and size.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

knight · June 11th, 2004, 06:02 AM

i use size1,size2... not size i think they are different from size reserve word 2nd i want to use this
strsql="update tblRings set name='"&ringname&"',[desc]='"&desc&"', "<% if Request("s1")= "on" then size1="5.5" end if "
%> where item_id = " &Request("item_id")

and after this
objRS.open strsql,conn
is it ok or not
dear imar i m trying 2 learn asp and i only read beginning asp 3.0 now so i m lammer in asp pls dont mind my foolish questions

Imar · June 11th, 2004, 06:13 AM

I assume that strsql="update .... is already within a server side code block, right? In that case, there is no need for <% and %>. Simply concatenate the various parts of your SQL statement:

Code:

strsql = "UPDATE tblRings SET name = '" & ringname & "',[desc]='" & desc
If Request("s1")= "on" Then 
  strsql = strsql & ", size1 = "5.5"
End If
strsql = strsql & "WHERE item_id = " & Request("item_id")

Does this help?

Don't worry about foolish question. I was always taught there is no such things as a foolish question; just foolish answers ;)
I was just afraid that you didn't look carefully enough at my answer.....

Cheers,

Imar

knight · June 11th, 2004, 06:25 AM

no it didnt work

om_prakash · June 11th, 2004, 07:23 AM

Try this :
<%
strsql = "UPDATE tblRings SET name = '" & ringname & "',[desc]='" & desc & "' "
If Request("s1")= "on" Then
strsql = strsql & ", size1 = 5.5"
End If
strsql = strsql & "WHERE item_id = " & Request("item_id")
%>

Does this help?

Om Prakash

Imar · June 11th, 2004, 08:57 AM

Try this instead:

strsql = strsql & ", size1 = 5.5"

Otherwise, please define "did not work". Posting detailed error info and descriptions makes things so much easier.....

Imar

knight · June 14th, 2004, 12:04 AM

ok this is working fine but how can i empty the size1 field other condition