DateTime problem

acko · August 19th, 2003, 10:32 AM

I cannot insert datetime value in sqlserver table from vb
I use datapicker control
Here is code

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Provider = "SqlOledb"
cn.Properties("User ID") = "sa"
cn.Properties("Password") = "password"
cn.Properties("Initial Catalog") = "NorthWind"
cn.Properties("Data Source") = "AckoB"

cn.Open
I tryed the following and nothing is working
cn.Execute "Insert datum values Datum (#" & dt.Value & "#)"
cn.Execute "Insert datum values Datum (" & dt.Value & ")"
cn.Execute "Insert datum values Datum (" & dt.Value & ")"
thanks
Alex

Imar · August 19th, 2003, 03:19 PM

Hi Alex,

I don't think your problem is related to the date, but to your INSERT statement.
Here's the general syntax for an INSERT statement:

Code:

INSERT [INTO] TableName (Column1, Column2) VALUES('Value1', Value2)

So, it looks to me you got your parentheses and the order of elements wrong. Change the SQL statement to this

Code:

cn.Execute "INSERT datum  (Datum) VALUES (#" & dt.Value & "#)"

Is it correct that both the tablename as the columnname are datum?

Note that the # is only used for Access databases. For SQL Server / MSDE you'll need the '

Code:

cn.Execute "INSERT datum  (Datum) VALUES ('" & dt.Value & "')"

HtH

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

acko · August 20th, 2003, 02:09 AM

Thanks but this is not working
cn.Execute "INSERT datum (Datum) VALUES ('" & dt.Value & "')"
Alex

Imar · August 20th, 2003, 02:29 AM

Hi Alex,

It would be really useful if you'd provide some more information about the situation.

For example: Define "Not working". Do you get an error? If so, what error. Post the error message here.
Also, how is your table created? What's the table name and which columns do you have?.

Another thing, don't use a SQL statement directly on the Execute method. Instead, assign it to a SQL variable which you can write to the screen using Trace, or to a temporary label, like this:

Code:

Dim SQL As String
SQL = "INSERT datum  (Datum) VALUES ('" & dt.Value & "')"
MyDebugLabel.Text = SQL

This way it's easier to see what the SQL statement contains.

Without this information, it's pretty hard to guess what the problem is, and to provide a solution for it.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Jeff Mason · August 20th, 2003, 12:59 PM

If I had to guess, I would say you are running into locale issues. As I understand it, the value property of the datetimepicker control will return a string whose format is according the current locale.

Try formatting the value property in ISO format to avoid any issues with confusing months and days:

Code:

cn.Execute "INSERT datum (Datum) VALUES ('" & format(dt.Value, "yyyy-mm-dd") & "')"

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

pickedaname · August 12th, 2004, 08:41 AM

I am having same issue trying to set datetimepicker.value as the stored procedures parameter.value and suspected formatting to be the problem. When I try to change the format it wants 3 parameters, not 2.
it want EnumType as System.Type first, then the value, then the format string. I am having a hard time finding the types although I assume the type here would be date.
Help?
Thanks

Aal · October 31st, 2004, 05:48 AM

hi Acko . . .
trie this :
------- CONVERT Dt Value to date and Format as Medium Date
"INSERT INTO [" & TABLE_NAME & "] VALUES('" & format(cdate(dt.value),"mm/dd/yyyy")) & "')"
or
"INSERT INTO [" & TABLE_NAME & "] VALUES" & _
"('" & format(cdate(dt.value),"medium date")) & "')"
if there's an error, check this :
- YOUR SQL SERVER Date/Time Properties ( System )
- usually datatype Date/Time for a field in SQL server take "Medium Date" format, try the
second INSERT INTO Statement above.
I have tried the code, and it's work for me. Also i used the same control ( DatePicker ) in my form . . .
have a NICE CODING PALE, bye . . .

Aal · October 31st, 2004, 05:53 AM

sorry i forgot this, i used SQL Server too . . . bye