...

View Full Version : Passing VARs to quieries in access db



phill_ridout
07-01-2007, 10:01 PM
a while ago i found this tutorial that shows you how to pass values to a query that was pre-created in an access database. I can not find this tutorial, does any one know of it? If not could someone post some sample code please. I want to use a dsn-less conection.

If anyone could help that would be great!

miranda
07-02-2007, 05:59 PM
this works for stored queries in access and for SQL Server Stored Procedures



Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open myDSN
sSQL = "EXECUTE Query_or_Procedure_Name '" & String_Variable & "'," & integer_Variable
oConn.Execute(sSQL)

You can also use the command object to pass the values.

Like so


Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open myDSN
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
Set .ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "dbo.sp_AddJob"
.Parameters.Append .CreateParameter("@JobTitle",adVarChar,adParamInput,Len(sJobName),sJobName)
.Parameters.Append .CreateParameter("@TestType ",adVarChar,adParamInput,Len(sAssessment),sAssessment)
.Parameters.Append .CreateParameter("@Description",adLongVarChar,adParamInput,Len(sDescription),sDescription)
.Parameters.Append .CreateParameter("@WeightedQuestions",adInteger,adParamInput,4, CInt(sWeighted))
.Parameters.Append .CreateParameter("@Locations",adVarChar,adParamInput,Len(strLocations),strLocations)
.Parameters.Append .CreateParameter("@PrescreenQuestionType",adInteger,adParamInput,4,CInt(PrescreenType))
.Parameters.Append .CreateParameter("@JobclassID",adInteger,adParamInput,4, iJobClass)
.Parameters.Append .CreateParameter("@UserID",adChar,adParamInput,9,UserID)
Set oRs = .Execute
iJobID = oRs("JobID")
oRs.Close
End With



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum