Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 19th, 2003, 10:32 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default DateTime problem

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
Reply With Quote
  #2 (permalink)  
Old August 19th, 2003, 03:19 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old August 20th, 2003, 02:09 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks but this is not working
cn.Execute "INSERT datum (Datum) VALUES ('" & dt.Value & "')"
Alex
Reply With Quote
  #4 (permalink)  
Old August 20th, 2003, 02:29 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old August 20th, 2003, 12:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old August 12th, 2004, 08:41 AM
Registered User
 
Join Date: Aug 2004
Location: Rochester, NY, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #7 (permalink)  
Old October 31st, 2004, 05:48 AM
Aal Aal is offline
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 . . .

Reply With Quote
  #8 (permalink)  
Old October 31st, 2004, 05:53 AM
Aal Aal is offline
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DateTime Problem kwilliams ASP.NET 2.0 Professional 1 March 12th, 2008 01:36 PM
DateTime Problem!!! Pls help cmualo SQL Server 2000 4 August 17th, 2007 03:08 AM
Problem with DateTime and Textbox MAKO C# 5 July 28th, 2006 10:09 AM
Datetime problem hastikeyvan Dreamweaver (all versions) 16 January 25th, 2006 11:58 AM



All times are GMT -4. The time now is 10:59 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.