DateTime field 2

[DaveCrate]

When fields from a asp form are inserted into a table, can the date and time of that insert be inserted as a field with it.

example: table
field name: ProductName, Produced, DateProduced, DateEntered
row: Part1 1000 01/06/2005 01/07/2005 09:42 AM

 

[SQLBill]

It depends on how you do the INSERT. You should be able to add the SQL Server function GETDATE().

INSERT INTO mytable
VALUES ('Part1', 1000, '01/06/2005', GETDATE())

-SQLBill

 

[Lothario]

GETDATE() should do it.

-L

 

[DaveCrate]

The column name is DateEntered. My fault
Should the last value be where GetDate()goes.

I looked at your code and mine really needs to be cleaned up.

mine:

strSQL = ""
strSQL = strSQL & "INSERT INTO Bliss1TPM "
strSQL = strSQL & "(Produced, Rejected, RunTime, DownTime, Shift, ProducedDate, Line, EnteredDate) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & intProduced & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & intRejected & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & intRunTime & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & intDownTime & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strShift & "'"
strSQL = strSQL & ", "
strSQL = strSQL & DATE_DELIMITER & datDateTimeField & DATE_DELIMITER
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strLine & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & GetDate() & "'"
strSQL = strSQL & ");"


Thanks for replies!
Suggestions?
Dave

 

[Lothario]

You are on the right track...did u get any errors with the above code...

-L

 

[DaveCrate]

yes

Variable is undefined: 'GetDate'

 

[Lothario]

Are you using SQL server or other database??

-L

 

[DaveCrate]

Sorry, SQL server

 

[Lothario]

GETDATE() shud work without any problems...

try this..

strSQL = "INSERT INTO Bliss1TPM(Produced, Rejected, RunTime, DownTime, Shift, ProducedDate, Line, EnteredDate) "
strSQL = strSQL & "VALUES('"&intProduced&"', "
strSQL = strSQL & '"&intRejected&"', '"&intRunTime&"', "
strSQL = strSQL & '"&intDownTime&"', '"&strShift&"', "
strSQL = strSQL & '"&datDateTimeField&"', '"&strLine&"', "
strSQL = strSQL & '"&GetDate()&"'); "

 

[Lothario]

oops...missed quotes

strSQL = "INSERT INTO Bliss1TPM(Produced, Rejected, RunTime, DownTime, Shift, ProducedDate, Line, EnteredDate) "
strSQL = strSQL & "VALUES('"&intProduced&"', "
strSQL = strSQL & " '"&intRejected&"', '"&intRunTime&"', "
strSQL = strSQL & " '"&intDownTime&"', '"&strShift&"', "
strSQL = strSQL &  " '"&datDateTimeField&"', '"&strLine&"', "
strSQL = strSQL & " '"&GetDate()&"'); "

-L

 

[DaveCrate]

Sorry L
I had to step away for minute

the error now is: Type mismatch

I'm a bit confused. What other info could I provide to help

Dave

 

[Lothario]

ok Do a response.Write(strsQL)

and copy the output and run in the analyzer...

-L

 

[r937]

getdate() does not want quotes around it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[DaveCrate]

I tried that and It's not writing the statement.
Same error.
here's the bulk of my code:

<%option explicit%>

<%
Dim DbConn

Const DATE_DELIMITER = "'"

Dim strSQL
Dim lngRecsAffected ' # of records affected

Dim intProduced
Dim strShift
Dim intRejected
Dim intRunTime
Dim intDownTime
Dim datDateTimeField
Dim strLine
Dim EnteredDate
Dim GetDate
Dim strErrorMsg ' Holds error message .


If Request.Form("action") <> "Save Form Data" Then
' Show the form
%>
<form action="<%=Request.ServerVariables("SCRIPT_NAME") %>" method="POST" name="FrontPage_Form1">
<input type="hidden" name="action" value="Save Form Data" />

'Form textbox info... I didn't think you need!

Else
' Do DB insert!

strLine = Request.Form("Line")
intProduced = Request.Form("Produced")
intRejected = Request.Form("Rejected")
intRunTime = Request.Form("RunTime")
intDownTime = Request.Form("DownTime")
strShift = Request.Form("Shift")
datDateTimeField = Request.Form("ProducedDate")


'Some datatype error handling of which I don't think you need

strSQL = "INSERT INTO Bliss1TPM(Produced, Rejected, RunTime, DownTime, Shift, ProducedDate, Line, EnteredDate) "
strSQL = strSQL & "VALUES('"&intProduced&"', "
strSQL = strSQL & " '"&intRejected&"', '"&intRunTime&"', "
strSQL = strSQL & " '"&intDownTime&"', '"&strShift&"', "
strSQL = strSQL & " '"&datDateTimeField&"', '"&strLine&"', "
strSQL = strSQL & " '"&GetDate()&"'); "


'response.write (strSQL)
' to test SQL statement

DbConn.Execute strSQL, lngRecsAffected

' Dispose of the CONN object
DbConn.Close
Set DbConn = Nothing

' Display a verification message and we're done!
%></FONT>
<div style="position: absolute; left: 188; top: 339; width: 900; height: 64">
<h2><font face="Arial">Thanks for submitting your Production Information for Bliss Line #1!</font></h2>

<p>
<font face="Arial">
<strong>Number of records affected:</strong> <FONT color=#0000ff><%= lngRecsAffected %></FONT>
</font>
</p>
</div>
<FONT color=#0000ff>
<%End If%>
<%End If%>
</FONT>

 

[r937]

here's the problem --

strSQL = strSQL & " '"&GetDate()&"'); "

getdate() doesn't want quotes around it

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[DaveCrate]

Thanks R937
I removed the quotes around

now looks like:
strSQL = strSQL & " '&GetDate()&'); "

error:
Syntax error converting datetime from character string

 

[Lothario]

Try this:

strSQL = strSQL & " &GetDate()& ); "

-L

 

[DaveCrate]

response.write worked this time

INSERT INTO Bliss1TPM(Produced, Rejected, RunTime, DownTime, Shift, ProducedDate, Line, EnteredDate) VALUES('2000', '2', '3', '0', '1st', '1/7/05', 'Bliss 1', '&GetDate()&');

Syntax error converting datetime from character string

 

[Lothario]

Remove the single quotes too...

-L

 

[r937]

what you want to end up with is this --

VALUES('2000', '2', '3', ... , GetDate() );


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)