Setting Date into DB

[Faheem786]

Hi there,

I have a Date value similar to 28/03/2003 which when I use the calculation

MyDate = "#"&Year ( MyDate ) & "-" & Month(MyDate ) & "-" & Day( MyDate ) &"#" gives the output as #2003-1-23#.

But when I insert this date value to the SQL Server it says an error as

"'#2003' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"

When I used the same calc without "#", it inserts the date as "1905-06-03" which I did not expect.

I'm not quite sure how to insert the date to the database. Kindly help me plsss..

Thanks
Faheem

 

[shaddow]

Ok you have to use quotes instead of # when using ADO objects from ASP and for SQL
will be like
MyDate = "[red]'[/red]"&Year ( MyDate ) & "-" & Month(MyDate ) & "-" & Day( MyDate ) &"[red]'[/red]" gives the output as #2003-1-23#.
hope this helps ________
George, M

 

[Tarwn]

Actually, if you look at reference 8 in this faq: faq333-3048 this question is already covered in the basic questions.

Specifically, #'s are for access, single quotes are for everyone else. You may also want to double check the format your entering the dates, the format displayed in your database may not always be the format it expects to see dates in.

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[shaddow]

You are corect but # is not for Access Databases used from the ADO objects it's just for access querry manager.
I dont think i ever saw working else but on the Access querry manager
ADO uses the SQL type sintax convetion not Access only one ________
George, M

 

[Tarwn]

I'm sorry, thats incorrect. When writing a query directed at an access database, dates are surrounded by #'s not single quotes. That query is sent from the driver to the database where the database then analyzes it almost exactly like it would analyze a query in the query analyzer. It then returns the data in the temporary table that is the result of the query and the ADO objects build up the recordset for you.

Just for the record, I have never seen a date in an SQL statement for an access database that worked with single quotes around it. If, by using ADO objects, you meant assigning the date to a specific field using a recordset object, then you should not need anything but the date stored as Date type:

rs.AddNew
rs("mydatefield") = cDate(Request.Form("myDate"))

As two more exmaples to back me up, here are sample SQL statements from a timecard system I wrote and used for over a year back when I was first starting. They all worked the last time I used this system:

sql_check = "SELECT * FROM Hours WHERE username = '"&username&"' AND job_id = '"&job_id&"' AND job_date = #"&job_date&"#"

sql_update = "DELETE FROM Hours WHERE job_date = #"&job_month&"/"&edit_array(i)&"/"&job_year&"# AND job_id = '"&job_id&"' AND username = '"&username&"'"

sql_update = "UPDATE Hours SET hours = "&Request.Form("hours_"&edit_array(i))&", juggled_hours = "&Request.Form("reported_"&edit_array(i))&" WHERE job_date = #"&job_month&"/"&edit_array(i)&"/"&job_year&"# AND job_id = '"&job_id&"' AND username = '"&username&"'"

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[shaddow]

No, that's not true you dont use

con.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="+dbPath to conect to a DB
i think this is better then using Jet.OLEDB Provider

I'm always using this type of conexion witch it's more portable then using Jet 4.0 and server dont have it. And for this type of conexion you have to use SQL like statements

TO be honest i used ADO(not DAO) objects inside Acces VBA and i had to use same SQL like sintax.
________
George, M

 

[Tarwn]

Ok, here is my connection string that worked with the above code:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	 "Data Source=c:\data\Timecard.mdb;" & _        
	 "Persist Security Info=False"

I am going to change to your connection string (which is by the way a slower connection) and retest.

strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\data\Timecard.mdb"

My retest worked just fine after I looked up what my password used to be. I added 5 hours for today and, poof, it showed up in the db and on the next page.

here is the outputted SQL statement for the read-ahead check to see if there are already hours for the project and date specified:

SELECT * FROM Hours WHERE username = 'eli' AND job_id = 'ch01' AND job_date = #3/21/2003#

Also, this is using 100% ADO, no DAO components at all.

Changing the #'s to single quotes results in the following error(Note I used a flush so wqe could see the SQL statement):

SELECT * FROM Hours WHERE username = 'eli' AND job_id = 'ch01' AND job_date = '3/21/2003' 
Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. 

/timecard/timecard2.asp, line 751

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[shaddow]

Yup i just told you dont use the DRIVER conexion but Jet ones.
Either way it's good but this will gives you some pains if you wanna change DB from access to SQL you wont only have to change conexion but queryes also.
It's just an opinion not necesarely to use it ________
George, M

 

[shaddow]

Hrmm here is some misunderstanding but i used the quotes on every querry i did and % instead of * for every search also.
It might be on what kind of database acces drivers you have on system. Anyway i bever liked Jet cuz everytime i had to put my local files on the server it wont work cuz Admin was to lazy to update Server with Latest Jet drivers.

Anyway it's still tricky for future use combining diferent types of SQL statements then use same for all type of conexions.
I might be mistaken about the date/time fields on the Jet but i ashure you that for me never worked * on searching database.
Anyway about the ADO thing i sayed before you have right it's 100% ADO but the Jet changes this when using it.
Access uses DAO to acces his DB's but as i told i prefer to use ADO (DRIVER conexion not Jet) and SQL like statements.

I think we made our point here ________
George, M

 

[Tarwn]

Actually, i think rather than making the point, you have muddled it a bit. Also, both of the moethods of declaring the connection string are used by ADO objects, and the connection object you had above is an ADO object.

And for the record, I use OLEDB drivers, not the ODBC drivers, and I have been through quite a few database migrations and server migrations.

That example used both drivers to connect. Here is another example. You get to choose which driver to use, which symbol, and you see the results.

Here is the code just to make doubly sure:

<html>
<body>
<%
If Request.Form(&quot;char&quot;) <> &quot;&quot; Then

	Dim strConnect
	Dim sqlStr, objRs

	If Request.Form(&quot;rdoConn&quot;) = &quot;odbc&quot; Then
		strConnect = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\data\Timecard.mdb&quot;
	Else
		strConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
			&quot;Data Source=c:\data\Timecard.mdb;&quot; & _        
			&quot;Persist Security Info=False&quot;
	End If

	Response.Write &quot;Connection String: &quot; & strConnect & &quot;<br>&quot;

	sqlStr = &quot;SELECT * FROM Hours WHERE job_date > &quot; & Request.Form(&quot;char&quot;) & Replace(Request.Form(&quot;txtDate&quot;),&quot;'&quot;,&quot;''&quot;) & Request.Form(&quot;char&quot;)

	Response.Write &quot;SQL String: &quot; & sqlStr & &quot;<br>&quot;

	Response.Flush

	Dim objConn
	Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)

	objConn.Open strConnect
	set objRS = objConn.Execute(sqlStr)

	If NOT objRS.EOF Then objRS.MoveFirst

	Do Until objRS.EOF
		Response.Write objRS(&quot;job_date&quot;) & &quot; - &quot; & objRS(&quot;hours&quot;) & &quot;<br>&quot;
		objRS.MoveNext
	Loop

	Set objRS = Nothing
	objConn.Close
	Set objConn = Nothing
Else
%>
<form method=&quot;post&quot; action=&quot;accesstest.asp&quot;>
Date: <input type=&quot;text&quot; name=&quot;txtDate&quot; value=&quot;1/1/2001&quot;><br>
Character: <select name=&quot;char&quot;>
			<option value=&quot;#&quot;>Use a #</option>
			<option value=&quot;'&quot;>Use a '</option>
			</select><br>
Conn type:<br>
<input type=&quot;radio&quot; name=&quot;rdoConn&quot; value=&quot;odbc&quot;>ODBC<br>
<input type=&quot;radio&quot; name=&quot;rdoConn&quot; value=&quot;other&quot;>OLEDB<br>
<input type=&quot;submit&quot; value=&quot;Run Test&quot;>
</form>
<%
End If
%>
</body>
</html>

And here is the link:

http://66.56.220.250/accesstest.asp

This uses both methods for the connection that you mentioned above, and it uses purely ADO objects as you also said above.

-Tarwn [sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048