Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5869
  • Last Modified:

Insert DateTime value in SQL-server

Hi experts,

probably an easy one...

A db on sql-server (2000),
has a table 'tbl_ResTest' with a datetime field (fldResDate)

Using a stored procedure:
'*********
CREATE PROCEDURE [dbo].[sp_DocNumAddDoc]
@dtResDate DateTime
AS

Insert into tbl_ResTest
(fldResDate)

Values

(@dtResDate)

GO

'*************

'**********************
In VB :
strSQL = "Execute sp_DocNumAddDoc " _
& "#" & CDate(txtResDate.Text) & "#"

objConn.Execute (strSQL)

'**********************


The date entered in the textbox 'txtResDate' looks like: 12/03/2003

On executing it errors out with "[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1 : Incorrect syntax near '/'."


In what format should the date be?
(the example shown is just what it is, an example ;-) )
G's
Ricky
0
Paurths
Asked:
Paurths
  • 2
  • 2
  • 2
1 Solution
 
TimCotteeCommented:
Hi Paurths,

I would always use this:

strSQL = "Execute sp_DocNumAddDoc " _
& "'" & Format(txtResDate.Text,"yyyy-mm-dd") & "'"

This ensures that the date is in a format that is unambiguous to SQL server and also is correctly surrounded in ' characters rather than # which is usually used with access (which I know you are familiar with!).

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0
 
PaurthsAuthor Commented:
Hi Tim,

thanx for the quick response.

Now it errors out with "Error converting data type varchar to datetime"

G's
Ricky
0
 
PaurthsAuthor Commented:
solved now, Tim.

I was still accepting dtResDate as datetime,
changed that to varchar and it works now.

Thanx for your assistance
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
darkolausCommented:
Hey TimCotte, you posted in

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20287510.html

code for taskstuff in win9x, i really need it for winnt. Hope you'll see this.

Greetz, Sven
0
 
TimCotteeCommented:
darkolaus:

have a look at the code posted by eddykt in http://www.experts-exchange.com/Q_20555955.html

It contains an example which works for NT (based on some code I have submitted here before.)
0
 
darkolausCommented:
thnx alot
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now