Inserting date into an Oracle table

rajthampi · #1 (**permalink**) 12-05-05, 03:27

Greetings

I wonder how I can insert date values into an Oracle table through ASP. Please refer the script provided and suggest me what would be the best solution

Code:

<% Option Explicit %>
<% Session.LCID=2057%>
<!--#include File="Common.asp"-->
<%
	Dim adoCon				'Holds ADODB Connection
	Dim strSQL				'Holds query string for the Recordset
	Dim lrqno				'Holds the last service request number
	Dim udate				'Holds todays date
	Dim CompDescription
	udate = "'" & Date & "'"
	
	CompDescription = REPLACE(Request.Form("reqdes"),"'","~")
	
	Set adoCon = Server.CreateObject("ADODB.Connection")
	adoCon.Open = "DSN=OraKMD;UID=kmd;PWD=kmd;"
	
	strSQL = "select NVL(max(SRVC_UNIQUE_ID)+1,1) from USR_SRVC_RQTS"
	lrqno = adoCon.Execute(strSQL).Fields(0).value
	
	strSQL = "Insert into USR_SRVC_RQTS"_
			& "(PC_USER_ID,SRVC_REQ_DESCR,SRVC_REQ_DATE,SRVC_UNIQUE_ID)"_
			& "Values("_
			& "'"& Request.Form("usercode") & "'"&","_
			& "'"&CompDescription&"'" & ","_
			& to_date('2/2/2005','MM-DD-YYY') & ","_
			& lrqno & ")"
	adoCon.Execute(strSQL)
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
Successfully updated!
<% Response.Redirect("RequestService.asp?userid="&Request.Form("usercode")) %>
</body>
</html>
<% adoCon.Close %>

When I run this script explorer returns me the following error:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/UpdateServiceRequest.asp, line 25, column 10
& to_date('2/2/2005','MM-DD-YYY')

Any ideas? I am lost by the middle of a project just because I cannot insert date fields and rolling back to MS SQL means a nightmare to me.

Thanks in advance

rajthampi · #2 (**permalink**) 12-05-05, 04:03

I managed to do it somehow and it works. I re-structured the Query string as follows:

Code:

strSQL = "Insert into USR_SRVC_RQTS"_
			& "(PC_USER_ID,SRVC_REQ_DESCR,SRVC_REQ_DATE,SRVC_UNIQUE_ID)"_
			& "Values("_
			& "'"& Request.Form("usercode") & "'"&","_
			& "'"&CompDescription&"'" & ","_
			& "To_Date(" & udate & "," & "'" & "MM-DD-YYYY" & "'" & ")" & ","_
			& lrqno & ")"

I hope this might help somebody out there seeking help with inserting date values into an Oracle table through ASP.

Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech