Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    187
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Passing VARs to quieries in access db

    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!

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    this works for stored queries in access and for SQL Server Stored Procedures

    Code:
    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
    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •