Getting a date from a textfield to sql server

[osani]

Hi
I am trying to get a date from a textfield in vb.net to sql server 2000. The data type is datetime. The problem is no matter what I enter or how I enter it, the entry goes in to the db as 1/1/1900. I've included pieces of relevant code. Please help. Thanks

If IsDate(textdate.Text) = FalseThen
MsgBox("please enter a valid date")
Else
Dim str AsString = "insert demographic(accnum, clinic, qdate) values (" & _
acc & "," & _
preparestr(textclinic.Text) & "," & _
textdate.Text & ")"
mycommand = New SqlCommand(str, myconnection)
mycommand.ExecuteNonQuery()
End If

 

[TPM]

Date's need to be surrounded by #'s, just like strings need '.

[/color]
[color=#0000ff]#" & _
textdate.Text & "#)"[/color]
[color=#0000ff]

 

[osani]

the # didn't work

changed code to:
Dim str AsString = "insert demographic(quest_date, accnum, clinic) values (" & _
"#" & textdate.Text & "#" & "," & _
acc & "," & _
preparestr(textclinic.Text) & ")"

When I put in today's date, I get the error:
"The name '#4' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

 

[TPM]

Insert INTO demographic......

 

[osani]

nope, that doesn't matter (i did try it though)

the database is getting the entry with the right account number and clinic name, but recieving a date of 1/1/1900. The wierdest part is, I have date of birth entered on the next form and it takes the date just fine.

Thanks.

 

[TPM]

Is your date formatted MM/DD/YYYY?

 

[osani]

Yes, I've even tried to change it from datetime to smalldatetime - it just doesn't care what I put in there.

 

[osani]

Fixed It!!!!

Doh! It didn't need #, but it did need ' ' like a string.

Thanks for all your help

 

[TPM]

So storing it as a string in your DB?

 

[osani]

No, it is still stored as a datetime datatype and will only take date formatted strings but I have this code for the text boxes to trim and input the strings with ' ' and the date needed that too.

Public Function preparestr(ByVal strvalue As String) As String

If strvalue.Trim() = "" Then
Return "NULL"
Else
Return "'" & strvalue.Trim() & "'"
End If
End Function