Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing form values to sql query in ADO

Status
Not open for further replies.
vprog

vprog

Programmer
Jul 9, 2003
3
0
0
US
Hello,
I am new to VBscripting. I need to create ADOCommand sql string to select all records that are between user entered start date and end date and user entered county in a html form.

The main idea is to display a report based on the specified dates and counties in a form. I need to accept the values from the user and then pass those values to sql query string and process them.
I would like to know how do I pass these values to sql string without creating a Access procedure or query. Can we use ADOCommand parameters for this? If so, how do we do that. Please find the code below. Please help.

<%@Language=VBScript%>
<%Option Explicit%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>
<HEAD>
<BODY>

<%
Dim objCommand, objRS
Dim strConnectionString
Dim DBPathName
Dim dtStartDt,dtEndDt,strCounty
Dim intNoofRecords

DBPathName = server.mappath("census.mdb")
strConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & DBPathName & ";"
Set objCommand = Server.CreateObject("ADODB.Command")

objCommand.ActiveConnection = strConnectionString
dtStartDt = Request.Form("eleStartDt")
dtEndDt = Request.Form("eleEndDt")
strCounty = Request.Form("elecounty")

objCommand.CommandText = "SELECT * FROM Participant WHERE Analysis_Date BETWEEN " & "Format(" & dtStartDt & "," & "'mm/dd/yy'" & ")" & " AND " & "Format(" & dtEndDt & "," & "'mm/dd/yy'" & ")" & _
" AND CountyID = " & "'" & strCounty & "'"
objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

intNoofRecords = objRS.recordcount

Set objCommand = Nothing

If intNoofRecords > 5 Then
' now loop through the records
While Not objRS.EOF
Response.Write objRS("Analysis_Date") & ": " & objRS("County") & "<BR>"
objRS.MoveNext
Wend
' now close and clean up
objRS.Close
Set objRS = Nothing
Else
msg = "There should be at least 5 participants to analyse the data"
Response.write ("<" & "script language=VBScript>")
Response.write ("Msgbox """ & msg & """<" & "/script>")
' now close and clean up
objRS.Close
Set objRS = Nothing
Response.Redirect "newApplication.asp"
End If
%>

</BODY>
</HTML>

 
Sort by date Sort by votes
You may try something like this:
Code: 
objCommand.CommandText = "SELECT * FROM Participant WHERE Analysis_Date BETWEEN #" _
 & Year(dtStartDt) & "-" & Month(dtStartDt) & "-" & Day(dtStartDt) & "# And #" _
 & Year(dtEndDt) & "-" & Month(dtEndDt) & "-" & Day(dtEndDt) & "#" _
 & " AND CountyID='" & strCounty & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
[0] The major technical hurle to overcome is whether the string so passed in the request object can be converted to date or datetime type. If it can you can simply use cdate() to do it and use directly in the sql statement. It is _not_ a format problem.
[tt]
'...etc
dtStartDt = [red]cdate([/red]Request.Form("eleStartDt")[red])[/red]
dtEndDt = [red]cdate([/red]Request.Form("eleEndDt")[red])[/red]
'etc...
objCommand.CommandText = "SELECT * FROM Participant WHERE Analysis_Date BETWEEN " & [red]dtStartDt & " AND " & dtEndDt[/red] & _
" AND CountyID = " & "'" & strCounty & "'"
'etc...
[/tt]
[0.1] If it cannot be convert to date/datetime, due to completely wrong user input in the form _or_ that the month/day are susceptible to be wrongly recognized, then, you have to validate it for the former and if it failed redirect to another page for user's amendment; or, for the latter, you do some surgery to make it unambiguous. Usually something like "yyyy-mm-dd" will always be unambiguously converted. Literal #yyyy-mm-dd# will always work fine too. Other form more familiar can also be acceptable as long as there is nolonger any ambiguity for the machine.

[1] >Can we use ADOCommand parameters for this? If so, how do we do that.
You sure can and it can be done very simply.
[tt]
'...etc
dtStartDt = [red]cdate([/red]Request.Form("eleStartDt")[red])[/red]
dtEndDt = [red]cdate([/red]Request.Form("eleEndDt")[red])[/red]
strCounty = Request.Form("elecounty")

objCommand.CommandText = "SELECT * FROM Participant WHERE Analysis_Date BETWEEN [red]x AND y[/red] & _
" AND CountyID = [red]z[/red]"
objCommand.CommandType = adCmdText
[blue]
'@p,@q and @r concrete names are not generically important and are chosen deliberately very different from x, y and z. Also the @ symbol is not generic neither. The only generically important to observe is the order of their create and append operation.
objCommand.parameters.append(objCommand.createParameter("@p", adDate, adParamInput,, dtStartDt))
objCommand.parameters.append(objCommand.createParameter("@q", adDate, adParamInput,, dtEndDt))
objCommand.parameters.append(objCommand.createParameter("@r", adVarChar, adParamInput,len(strCounty), strCounty))
[/blue]
Set objRS = objCommand.Execute

'etc etc...
[/tt]
 
Upvote 0 Downvote
amendment
Upon re-read, there is an obvious typo in editing for the corresponding line which should read like this.
[tt]
objCommand.CommandText = "SELECT * FROM Participant WHERE Analysis_Date BETWEEN x AND y[red]"[/red] & _
" AND CountyID = z"
[/tt]
 
Upvote 0 Downvote
Just another note

[2] I see now you have some msgbox() in the server-side block. That is not allowed. (Who to see the message?) Take them all out. If you need messages for debugging, use response.write to the client or use some log file to record them for debugging.
 
Upvote 0 Downvote
amendment-2

[2.1] Sorry [2] is an illusion. There is no msgbox on the server-side. It is inside response.write, so it is displayed on the client-side. Nevertheless, it is a kind of barely exposed script. Maybe a write to some predefined container in the page would be better? In any case, note [2] has no point.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lunter
  • Locked
  • Question
How to create vbArray+vbByte (8209) in *.vbs stript
Replies
0
Views
88
lunter
lunter
elly00
  • Locked
  • Question
sql result
Replies
1
Views
61
strongm
strongm
Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
135
Brianfree
Brianfree
Michastro
  • Locked
  • Question
Send an order to an IP power in VBS
Replies
0
Views
86
Michastro
Michastro
GessWurker1
  • Locked
  • Question
problem with AD query, empty values and form field population
Replies
0
Views
45
GessWurker1
GessWurker1

Part and Inventory Search

Sponsor

Back
Top