indofraiser indofraiser - 7 months ago 12
SQL Question

Date input into SQL issue (nvar to date but no nvar used)

I am trying to get a date and then add it to the database.

To do this I am using (as a sample..)

If vDay = "TUESDAY" Or vDay = "2" Then
vDateEnd = DateTime.Now.AddDays(-2)
End If


I then want to put vDateEnd into the SQL. If I hardcode "09/16/2012" it works but I have played about (as you will see from the commented out lines) to no avail.

'report not found so we need to create one
'Create dates in the report if doe not exist
'### Update
Dim ConnectionString3 As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString
Dim dsNames3 As SqlDataSource
Dim sSQL3 As String

dsNames3 = New SqlDataSource
dsNames3.ConnectionString = sConnString

sSQL3 = "spWeeklyReportDatesCreate"

dsNames3.UpdateCommand = sSQL3
dsNames3.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure
dsNames3.UpdateParameters.Clear()
dsNames3.UpdateParameters.Add("DateStart", "09/16/2012")
'dsNames3.UpdateParameters.Add("DateEnd", "09/01/2013")
dsNames3.UpdateParameters.Add("DateEnd", (Mid(Date.Now.AddDays(-2), 1, 10)))
' dsNames3.UpdateParameters.Add("DateEnd", DateTime.Now.AddDays(-2).ToShortDateString("mm/dd/yyyy"))
dsNames3.UpdateParameters.Add("RunCompleted", "N")
dsNames3.Update()
dsNames3 = Nothing


Stored Procedure

USE [db]
GO
/****** Object: StoredProcedure [dbo].[spWeeklyReportDatesCreate] Script Date: 16/09/2013 15:23:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spWeeklyReportDatesCreate]

@DateStart smalldatetime,
@DateEnd smalldatetime,
@RunCompleted nvarchar(1)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

INSERT INTO [tblweeklyreports]

(DateStart, DateEnd,RunCompleted)
VALUES
(@DateStart, @DateEnd,@RunCompleted)

--select @AnswerID=@@IDENTITY

END
Datebase - both set to 'datetime' at the moment though all of these parameters have been played with i.e. all SmallDate, DateTime, Date etc...

Answer

Change this:

dsNames3.UpdateParameters.Add("DateEnd", (Mid(Date.Now.AddDays(-2), 1, 10)))

to this:

dsNames3.UpdateParameters.Add("DateEnd", SqlDbType.SmallDateTime).Value = vDateEnd

This allows you to assign a VB.Net Date type directly to the sql parameter. There is no formatting to worry about: ADO.Net handles any conversions needed. Make similar changes everywhere you use DateTime or SmallDateTime sql types.

What you were doing before made ADO.Net try to guess at your sql parameter type before sending the data to Sql Server, and in this case it guessed wrong. If you ever find yourself converting a VB.Net Date type into a string for use with SQL (such as with the Mid() function), you're doing something really wrong.

In fact, you should pretty much always use an explicit database type for sql parameters, because even when ADO.Net guesses almost right, such that it seems to work, subtle mistakes can still have big impacts. As you saw, ADO.Net will often use NChar for it's parameter type. That will cause a sql server to do an extra conversion later on, to match whatever type is really in the database. In some cases, Sql Server will try to do this conversion for every record in a potential result set. In other cases, it will cause Sql Server to not recognize that an index can be used. Both of those cases can result in dramatic performance differences. So please, just tell ADO.Net what parameter type you want it to use.