?
Solved

Insert DateTime value in SQL-server

Posted on 2003-03-19
6
Medium Priority
?
5,863 Views
Last Modified: 2012-06-27
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
Comment
Question by:Paurths
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 8165800
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
 
LVL 12

Author Comment

by:Paurths
ID: 8165845
Hi Tim,

thanx for the quick response.

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

G's
Ricky
0
 
LVL 12

Author Comment

by:Paurths
ID: 8166121
solved now, Tim.

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

Thanx for your assistance
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Expert Comment

by:darkolaus
ID: 8166895
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 8166948
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
 

Expert Comment

by:darkolaus
ID: 8167053
thnx alot
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question