Forums > Server-Side Scripting

Subject: Passing date variable from form

Posted By	Message
robx1971 Thu 5th Oct '09 21:33 user	I am trying to search a date range but I am having problems with the SQL statement: There is only one date field in the table (logDate) and the form has a startDate (text field) and endDate (text field). Using MS SQL 2005, VBScript and Dreamweaver CS4 I get a "Conversion failed when converting datetime from character string" message. ASP CODE: <% var rsPView__sDate = "%"; if (String(Request.Form("startDate")) != "undefined" && String(Request.Form("startDate")) != "") { rsPView__sDate = String(Request.Form("startDate")); } %> <% var rsPView__eDate = "%"; if (String(Request.Form("endDate")) != "undefined" && String(Request.Form("endDate")) != "") { rsPView__eDate = String(Request.Form("endDate")); } %> <% var rsPView_cmd = Server.CreateObject ("ADODB.Command"); rsPView_cmd.ActiveConnection = MM_CNoperator_STRING; rsPView_cmd.CommandText = "SELECT calllogTbl.logID,calllogTbl.logDate, calllogTbl.logCaller, calllogTbl.logInternal, calllogTbl.logResolved, calllogTbl.logTransfer, calllogTbl.logRepeat, operatorTbl.opFN FROM calllogTbl, operatorTbl WHERE logDate >= ? AND logDate <= ? AND operatorTbl.operatorID = calllogTbl.operatorID ORDER BY calllogTbl.logID DESC"; rsPView_cmd.Prepared = true; rsPView_cmd.Parameters.Append(rsPView_cmd. CreateParameter("param1", 135, 1, -1, rsPView__sDate)); rsPView_cmd.Parameters.Append(rsPView_cmd. CreateParameter("param2", 135, 1, -1, rsPView__eDate)); var rsPView = rsPView_cmd.Execute(); var rsPView_numRows = 0; %> Thanks in advance.
Rob Mon 2nd Nov '09 18:40 admin	Couple of questions first... 1. What database are you using? 2. What is the field type of the logDate field? ...you would obviously have to take the type of field into consideration when building your sql script, also, instead of the AND, use the BETWEEN method eg. SELECT calllogTbl.logID,calllogTbl.logDate, calllogTbl.logCaller, calllogTbl.logInternal, calllogTbl.logResolved, calllogTbl.logTransfer, calllogTbl.logRepeat, operatorTbl.opFN FROM calllogTbl, operatorTbl WHERE (logDate BETWEEN ? AND ?) AND operatorTbl.operatorID = calllogTbl.operatorID ORDER BY calllogTbl.logID DESC