Will this work

[FallenPaladin]

Hi I am experimenting with VBScript and SQL and am unsure if the following code will actually work. I do not have access to IIS at present so I am unable to test it. I would be greatfull if anyone could just take a brief look at the code to see if the syntax is correct. Thanks

<%@Language = "VBScript" %>
<%Option Explicit%>
<!--#include virtual="/advobs.inc"-->
<%
Dim newDate
Dim newTime
Dim newMessage
Dim objConn
Dim sqlInsert
Dim objRecordSet

Set objConn = Server.CreatObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};"&_"DBQ=C:/inetpub/wwwroot/feedBack.mdb"
objConn.Open

newDate = Date()
newTime = Time()
newMessage = request.form("strMessage")

if newMessage <> "" then
	sqlInsert="INSERT INTO tblMessages(date,time,message) VALUES(newDate,newTime,newMessage)"
end if

Set objRecordSet=Server.CreateObject("ADODB.Recordset")
objRecordSet.Open "tblMessages", objConn,,,adCmdTable

Do while Not objRecordSet.EOF
	Response.Write"<B>" & objRs ("time") & "</B><BR>"
	Response.Write objRs("date") & "<BR>"
	Response.Write objRs ("message") & "<BR>"
	Response.Write "<p><hr></p>"

	objRecordSet.MoveNext
Loop

objRecordSet.Close
Set objRecordSet = Nothing

objConn.Close
Set objConn = Nothing

[FallenPaladin]

sorry the above code needs to be amended for the loop to read

Do while Not objRecordSet.EOF
	Response.Write"<B>" & objRecordSet ("time") & "</B><BR>"
	Response.Write objRecordSet("date") & "<BR>"
	Response.Write objRecordSet ("message") & "<BR>"
	Response.Write "<p><hr></p>"

	objRecordSet.MoveNext
Loop

[SheSaidImaPregy]

Here is the edited code and the reasons why:

<%@Language = "VBScript" %>
<%Option Explicit%>
<!--#include virtual="/advobs.inc"-->
<%
Dim newDate
Dim newTime
Dim newMessage
Dim objConn
Dim sqlInsert
Dim objRecordSet
' Added below for SQL statement below
Dim strSQL

Set objConn = Server.CreatObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};"&_"DBQ=C:/inetpub/wwwroot/feedBack.mdb"
objConn.Open

'newDate = Date()
'newTime = Time()
'Not needed as below. If this doesn't work (the current_timestamp), you can always use the following line and substitute it in below:
'newDateTime = now()
newMessage = Trim(Request.Form("strMessage"))
' You should always trim variables coming in to lose beginning or trailing white spaces

if newMessage <> "" then
	sqlInsert="INSERT INTO tblMessages(datecreated,message) VALUES(CURRENT_TIMESTAMP,newMessage)"
end if

' Great thing about SQL, you can automatically put the date and time inside the database without having to code it in. You can set this to be default on your database, or do it from the SQL query. Change the date and time columns on your database to one. And also realize that date is probably a protected word in SQL. Use something more suitable like, datecreated.

Set objRecordSet=Server.CreateObject("ADODB.Recordset")

' You should never open the entire table like that, even if that works.. Use a select statement.

strSQL = "SELECT * FROM tblMessages"
objRecordSet.Open strSQL, objConn, adCmdTable

' Another way to do the below line is 
'Do Until objRecordSet.EOF
' But what you have is fine.
Do while Not objRecordSet.EOF
	Response.Write"<B>" & FormatDateTime(objRs .fields("datecreated"),3) & "</B><BR>"
	Response.Write FormatDateTime(objRs("datecreated"),2) & "<BR>"
	Response.Write objRs.fields("message") & "<BR>"
	Response.Write "<p><hr></p>"

	objRecordSet.MoveNext
Loop

' What the formatdatetime does is take the value and format what you want out of it. there are many ways to do this. the 2 after the value just creates time as 00:00:00 PM/AM, the 3 means 00/00/0000 format.
' Also remember that whenever you are dealing with more than one row, you need to put the recordsetname.fields attribute above.

objRecordSet.Close
Set objRecordSet = Nothing

objConn.Close
Set objConn = Nothing

[FallenPaladin]

Thank you for the reply it has been very helpful.

Jon

[vasanthiraajan]

ya

working

[SheSaidImaPregy]

glad to hear it!

[SheSaidImaPregy]

So you know, if you are using a DATETIME field in your SQL database, you use the CURRENT_TIMESTAMP to get the current date and time. If you are using just date, use CURDATE(). When retrieving your information out of your database, especially for dates, you can do it this way:

DATE_FORMAT(datecreated, '%m/%d/%Y')

this will pull something out of your datetime or date column as: 2/7/2007

A full list on what you can do is here:
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Oh, and please mark the question as solved above the bottom banner on the page.