datetime stamp error 1

[aspnetuser]

The following code fails for me.

'this will stamp current date and time
'strPoolStartJoinDate = now()

sql = "INSERT INTO tblPoolConfigs (DateTime) VALUES('" & strStartJoinDate &"'"')"
DbConn.Execute(sql)

The sql 2000 field is smalldatetime.

any ideas?

 

[Genimuse]

Three possible problems. First is that the assignment is commented out... remove the ' from the front of the second line.

Second is the extra quotes at the end of your sql. It should be

sql = "INSERT INTO tblPoolConfigs (DateTime) VALUES('" & strStartJoinDate & "')"

Lastly, I don't believe that "DateTime" is a valid field name, is it? I thought it was a reserved keyword.

 

[aspnetuser]

You are right about the datetime, changed that to datetimestamp but now it does not append anything to the record...

sql = "INSERT INTO tblPoolConfigs (DateTimeStamp) VALUES('" & strStartJoinDate & "')"

??? Not sure why?Genimuse, any suggestions?

 

[Lothario]

Because use your variable "strPoolStartJoinDate" instead of what Genimuse suggested as an example "strStartJoinDate"

-L

 

[Genimuse]

Oops, yup, copied and pasted your sql and didn't notice that you'd changed variable names between the assignment and the string concatenation. Use the name you assigned to Now().

 

[TheCount]

For SQL database date you should use getdate() for Access database Now()

 

[Genimuse]

If you want the database server's time stored, that is.

 

[aspnetuser]

i get the following error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.

 

[DreXor]

you got the error using what code? please post updated sqlstr

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[aspnetuser]

sql = "INSERT INTO tblPoolConfigs (DateTimeStamp) VALUES('" & strPoolStartJoinDate & "')"

 

[DreXor]

can you response.write your sql statement before firing it? mainly concerned with the data in the variable.

also just to be on the safe side.. put your field and table names in []'s just in case of reserved word issues.


ps. thanks for the updated code.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[aspnetuser]

not sure how the code fails before the response.write
where do i place that.

 

[aspnetuser]

error converting character string to smalldatetime data type.

how do i convert the string to small date

like strPoolStartJoinDate = now()
convert(etc....

 

[DreXor]

you have something along the lines of :

sql = "INSERT INTO tblPoolConfigs (DateTimeStamp) VALUES('" & strPoolStartJoinDate & "')"
[highlight]response.write sql & "<br>"[/highlight]
Connection.Execute(SQL) ' this line gives the error

add the yellow line to the code for debugging, that way you can see what's trying to be handed to the DB just before it tells you it doesn't like it.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

my assumption on the matter is that you have an invalid string populating the strPoolStartJoinDate variable, and when it's being plugged into the sql statement it's causing the error.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[aspnetuser]

drexor thanks for the help. i got it to run switching to this formatt...do you no why???

sql3 = "insert into tblPoolConfigs" _
& " (PoolStartJoinDate)" _
& " values('" & strPoolStartJoinDate & "') set rs3 = DbConn.Execute(sql3)
set rs3 = nothing

Why is this different, it works now...

 

[DreXor]

because you have a different sql statement all around

sql3 = "insert into tblPoolConfigs(PoolStartJoinDate) values('" & strPoolStartJoinDate & "')"

VS what you used to have :

sql = "INSERT INTO tblPoolConfigs(DateTimeStamp) VALUES('" & strPoolStartJoinDate & "')"


you changed the fieldname, and if the field "DateTimeStamp" wasn't in your table, or whatnot, could have been part of the problem.

either which way about it, glad it's working now.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[aspnetuser]

Oh...Well thanks for all the help you got me to the right answer and for that goes a star!