ASP SQL With Dynamic Queries

Just basic ASP, trying to learn that and possibly ASP .NET too.  

What I have so far is something like this but i know the code wont work right:
<%
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

ConnString="DRIVER={SQL Server};SERVER=SQLVM;UID=sa;" & _
"PWD=password;DATABASE=sourcetracking"

SQL = ?????????????????????

Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

Connection.Open ConnString

Recordset.Open SQL,Connection

Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

Outside of ASP mostly HTML. I have not figured out PHP with ASP yet. So for now I am going with HTML.

I'd recommend you cut out ASP classic entirely and go straight to ASP.Net, unless you absolutely have to!

You'll need to create the SQL as a stored procedure (http://msdn.microsoft.com/en-us/library/aa258259%28SQL.80%29.aspx) and then, if you are sticking with ASP classic do something like the following: http://blog.binarybooyah.com/blog/post/Classic-ASP-data-access-using-parameterized-SQL.aspx

I would have to use the SQL embedded because if I need to make stored proc's it will be a huge task getting change requests filled out and approved by 10 people in the company LOL. I will read the links as well and appreciate any further attention.

That second link I provided shows how to create a SQL Command object with inline SQL rather than an SP; use this object for assigning the parameters - it is a lot safer than adding values directly.

Are you passing in values from a form or querystring?

'you wouldn't need a recordset - just a basic example below

<%
Dim Connection
Dim ConnString
Dim SQL
Dim mField1
Dim mField2

'let's pretend that mField1 is a customer and the form name was named customer
'let's pretend that mField2 is a phone number and the form name was named phone

mField1 = request.form("customer")
mField2 = request.form("phone")

ConnString="DRIVER={SQL Server};SERVER=SQLVM;UID=sa;" & _
"PWD=password;DATABASE=sourcetracking"

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open ConnString

'now let's say in SQL Server, your table is called tblCustomer and your customer field is named cust
' and your phone field is named phonenumber
'
' you wanted to add a record or "insert" a record

SQL = "insert into tblCustomer (cust,phonenumber) values('" & mField1 & "','" & mField2 & "')"

Connection.Execute SQL

Connection.Close
Set Connection=nothing
%>

The example above is coming from a form that is submitted - this example is using querystring - for example - www.xxx.com?customer=bob&phone=123456

'using same code above
<%
Dim Connection
Dim ConnString
Dim SQL
Dim mField1
Dim mField2

mField1 = request.querystring("customer")
mField2 = request.querystring("phone")

ConnString="DRIVER={SQL Server};SERVER=SQLVM;UID=sa;" & _
"PWD=password;DATABASE=sourcetracking"

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open ConnString

'now let's say in SQL Server, your table is called tblCustomer and your customer field is named cust
' and your phone field is named phonenumber
'
' you wanted to add a record or "insert" a record

SQL = "insert into tblCustomer (cust,phonenumber) values('" & mField1 & "','" & mField2 & "')"

Connection.Execute SQL

Connection.Close
Set Connection=nothing
%>

'Hi,
'what about the case of:

mField1 = "';go;drop database sourcetracking; go;"

'?

'Regards

Allister_Reid - that is a true point but and I agree - stored procedures is the way to go - to avoid the SQL injection scenario that you mention above

- but he is looking for an answer to solve his problem and in simpliest terms, this will work.

For me that wont be an issue the user name it will actually run under will be read only plus not a publicly accessible site.

i mean a user who cant drop db sorry!!! lol

When you have a 300+ line query in SQL to use with basic ASP isnt there an easier way to do the statement when embedding the code into the script?

The easiest way would be to create a stored procedure and pass the variables to it and let SQL Server do all the work for you. You would have to create a Command object in ASP, and define the parameters to use it.

Here is another example that I have up as well.
http://www.experts-exchange.com/Q_24801116.html

Good Luck
Carrzkiss

Thank you!

You are so very welcome.
I will be adding these demo's to a new site that I am working on, along with a LOT of other projects.
So keep an eye on my profile for more information to become available.

Have a good one
Carrzkiss