Stored Queries with classic ASP

[KRMwebdesign]

I want to create some stored queries in MsAccess. I've created the really simple "select * from x where y" queries but I have a few queries that look for elements on the page. Lke this:

Dim MyColumn
MyColumn = request.QueryString("ColumnB")

if ( MyColumn<>"" ) then
MyColumn = replace(MyColumn, "'","''")
end if

"SELECT * FROM Tablex WHERE ColumnA = " & MyColumn & " "

Does anyone know how to write this in MsAccess SQL?

Thanks for any help.

[Ocean10000]

Here is an example I just did really quick in C# using a access db I put together for other research purposes.

I used parameters to insert the text into queries to protect against injection attacks. This way we do not have to worry about injection attacks escaping out of the query to do harm.

namespace ConsoleApplication21
{
class Program
{
static void Main(string[] args)
{
System.Data.DataSet List = Query("%poker%");

//Loops though the dataset to return all the hostnames
//that match the search results.
for (int i = 0;i <= List.Tables[0].Rows.Count - 1;i++)
{
Console.WriteLine(List.Tables[0].Rows[i]["HostName"].ToString());
}
Console.ReadLine();
}

/// <summary>
/// Used to Query the Access Database
/// </summary>
/// <param name="Search">Search term</param>
/// <returns>a dataset which contains all the search results</returns>
static System.Data.DataSet Query(string Search)
{
System.Data.OleDb.OleDbConnection Connection;
Connection = new System.Data.OleDb.OleDbConnection();
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"Poker Sites.mdb\"";

System.Data.OleDb.OleDbCommand Cmd = new System.Data.OleDb.OleDbCommand();
Cmd.Connection = Connection;
Cmd.CommandType = System.Data.CommandType.Text;
Cmd.CommandText = "Select * from Info where HostName like ?";

//----------------------------------------------------------
//The order of the Parameters matters for Access DB's since
//they are not given names only positions.
//----------------------------------------------------------
System.Data.OleDb.OleDbParameter Parameter;
Parameter = new System.Data.OleDb.OleDbParameter();
Parameter.DbType = System.Data.DbType.String;
Parameter.IsNullable = false;
Parameter.Value = Search;
Cmd.Parameters.Add(Parameter);

System.Data.OleDb.OleDbDataAdapter Adapter;
Adapter = new System.Data.OleDb.OleDbDataAdapter();
Adapter.SelectCommand = Cmd;
System.Data.DataSet ds = new System.Data.DataSet();
Adapter.Fill(ds);
return ds;
}
}
}

[KRMwebdesign]

So can I put classic ASP and SQL in the SQL view in MsAccess?

[Ocean10000]

It is possible to use views which have parameters in asp classic. I just do not have sample code currently to post.

[KRMwebdesign]

Thanks Ocean10000. I'll look into that.

So can i create a query where a I am selecting the all fields in a table where the tableID is equal to a session variable on the page?

Could you even show me a .NET version of this and I can work it out from there?

Thanks for all your help.

[KRMwebdesign]

I found this tutorial [stardeveloper.com...] and it is quite informative but it doesn't show you how to add or update your database from a form. Can anyone shed any light on this. I know it can't be far off.

Thanks again.

[Ocean10000]

'This is some code from a production website writen in classic asp that uses stored procedures. The same style should work for Access.

<!--#include file='adovbs.asp' -->
.
.
.
public sub spUpdateClientLogins(byval clientid,byval Loginid, byval Name, byval Login, byval Password)

Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = connection

cmd.CommandText = "spUpdateClientLogins"

cmd.CommandType = adCmdStoredProc

if (loginid<>"") then
cmd.Parameters.Append cmd.CreateParameter("@loginid", adInteger,adParamInput,,loginid)
end if

cmd.Parameters.Append cmd.CreateParameter("@clientid", adInteger,adParamInput,,10)

cmd.Parameters.Append cmd.CreateParameter("@Name", adVarChar,adParamInput,50,Name)

cmd.Parameters.Append cmd.CreateParameter("@Login", adVarChar,adParamInput,50,Login)

cmd.Parameters.Append cmd.CreateParameter("@Password", adVarChar,adParamInput,24,Password)

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

RsRecordset.CursorType = adOpenForwardOnly

RsRecordset.CursorLocation = adUseClient

RsRecordset.Open cmd

set spUpdateClientLogins = RsRecordset

set cmd = nothing

end sub

[KRMwebdesign]

Thanks Ocean10000. I have the asp page code down fine but it's the code in the MsAccess database I'm not sure of.