CODEWITHSUNDEEP
c#sql-serverwinforms
Ramon Fonis
Ramon Fonis

Reputation: 167

Correct Format of sending DatetimePicker's Date value to SQL Server

SQL Server has the following Columns in the "Crash" Table

   (<NationalId, char(10),>
   ,<CrashTime, date,>
   ,<CrashLocation, nvarchar(50),>
   ,<City, nvarchar(50),>
   ,<CarColor, nvarchar(50),>
   ,<CarEngine, nvarchar(50),>
   ,<CarType, nvarchar(50),>
   ,<Damage, int,>)

There is a Stored Procedure Called "addCrash" with following codes:

ALTER Procedure [dbo].[addCrash]

           @NationalId char(10),
           @CrashTime date,
           @CrashLocation nvarchar(50),
           @City nvarchar(50),
           @CarColor nvarchar(50),
           @CarEngine nvarchar(50),
           @CarType nvarchar(50),
           @Damage int
AS
BEGIN          



INSERT INTO [dbo].[Crash]
           ([NationalId]
           ,[CrashTime],
           [CrashLocation]
           ,[City]
           ,[CarColor]
           ,[CarEngine]
           ,[CarType]
           ,[Damage])
     VALUES


(         @NationalId,
          @CrashTime ,
           @CrashLocation ,
           @City ,
           @CarColor ,
           @CarEngine ,
           @CarType ,
           @Damage)
END

Now in Visual Studio i use a DateTimePicker with the custom format of yyyy-MM-dd to send date to "CrashTime" column, the problem is whenever i try to add date from DateTimePicker to my database, There is error of not sending "CrashTime"(the date) Properly. 

private void button1_Click(object sender, EventArgs e)
    {

    SqlParams[] parameters = new SqlParams[]
    {
        new SqlParams("@NationalId", SqlDbType.Char, nationalId),
        new SqlParams("@CrashTime", SqlDbType.Date,  dateTimePicker1.Value.Date.ToString("yyyy-MM-dd")),
        new SqlParams("@CrashLocation", SqlDbType.NVarChar, TxtCrashLocation.Text),
        new SqlParams("@City", SqlDbType.NVarChar, cityCombo.SelectedValue.ToString()),
        new SqlParams("@CarColor", SqlDbType.NVarChar, ColorCombo.SelectedValue.ToString()),
        new SqlParams("@CarEngine", SqlDbType.NVarChar, EngineCombo.SelectedValue.ToString()),
        new SqlParams("@CarType", SqlDbType.NVarChar, cartypeCombo.SelectedValue.ToString()),
        new SqlParams("@Damage", SqlDbType.Int, TxtDamage.Text)

    };

    ModifyConnection mc = new ModifyConnection();
    mc.AddRecord("exec addCrash @NationalId,@CrashTime,@CrashLocation,@City,@CarColor,@CarEngine,@CarType,@Damage", parameters);
    MessageBox.Show("Crash added Successfuly");
}

I have no idea about the root of problem, even i get the value of DateTimePicker to a string the format sounds correct and is: 2017-01-05

Upvotes: 0

Views: 2436

Answers (1)

Kjell Inge Hestad
Kjell Inge Hestad

Reputation: 91

You have to specify a correct datatype for the parameter. Date and DateTime works, Date and String not.

Correct this one:

new SqlParams("@CrashTime", SqlDbType.Date,  dateTimePicker1.Value.Date.ToString("yyyy-MM-dd")),

Try this:

new SqlParams("@CrashTime", SqlDbType.Date, dateTimePicker1.Value.Date),

The datatype "Date" used in SQL Server accepts "DateTime" variables from your Visual Studio project.

Upvotes: 3

Related Questions