• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

Example Of Database Connection using ADO Parameters

Hi All

Please could someone provide me with an example ASP database connection string to a SQL Server 2000 database and 1 ADO parameter which loads the data into a recordset.

Many thanks,

Rit
0
rito1
Asked:
rito1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
rito1Author Commented:
Hi angellll,

The parameters need to be passed into a stored procedure and also 1 minor detail I missed was - I am using VBScript.. Sorry!

Rit
0
 
darkmooinkCommented:
i dont do it that way i use this way
Set DBatd = Server.CreateObject("ADODB.Connection")
DBcon.Open "Provider=SQLOLEDB; Server=192.168.1.1; Database=database name; UID=userid; PWD=password;"
 
Set Results = DBCon.Execute("select * from table1")

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
darkmooinkCommented:
ops line 1 should be Set DBcon
0
 
rito1Author Commented:
Hi,

The reason I was doing it with parameters was that I have been stunk by an SQL injection attack previous so wanted to use ADO parameters to declare them.

Here is the code I have so far... I just can't figure out how to load the data into a recordset:

Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open strDSNless
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_SELECTCustomers"
   cmd.CommandType = adCmdStoredProc
   
   cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
      adParamInput)
   ' Set value of Param1 of the default collection to 22
   cmd("Param1") = 22
   cmd.Execute
0
 
Kelvin McDanielSr. Developer/ConsultantCommented:
Try This...

[BEGIN] ------------------------------------------------------------------------------------

' *** This basis code is from your last comment on 06.23.2008 @ 09:18am EDT ***
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSNless
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_SELECTCustomers"
cmd.CommandType = adCmdStoredProc
   
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
      adParamInput)
' Set value of Param1 of the default collection to 22
cmd("Param1") = 22

' *** Delete the cmd.Execute and replace it with the following ***
Set oRS = Server.CreateObject("ADODB.RecordSet")
oRS.CursorLocation = 3
oRS.Open cmd

[END  ] ------------------------------------------------------------------------------------
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now