Saving Date string to SQL Server date type column in asp.net (vb.net)

Question

I am a beginner in vb.net and asp.net. I am having a problem saving the date, which is a string value coming from the DevExpress datepicker control.

The column name is expiryDate and its type is date.

Here is my ASPX markup :

<tr>
    <td nowrap="nowrap" class="label" align="right" valign="top" style="width: 10%">
        Expiry Date
    </td>
    <td style="width: 40%" valign="top">
        <dxe:ASPxDateEdit ID="expiryDate" 
                          runat="server" 
                          EditFormat="Date" 
                          Date="13/06/2014" 
                          Width="200">
        </dxe:ASPxDateEdit>
    </td>
</tr>

And in C# code behind :

sqlSave = "INSERT INTO [Product] (Title, expiryDate) VALUES ("
          sqlSave += " '" & txtProductName.Text.Trim().Replace("'", "''") & "', "
          sqlSave += ""   & expiryDate.Text & " )"

I am using expiryDate.Text to save in the database but the date saved is: 1900-01-01 and sometime i get message Operand type clash: int is incompatible with date.

I don't know how to fix this issue, please help?

Please use SQL parameters. stackoverflow.com/questions/910465/… — InbetweenWeekends
– InbetweenWeekends, Commented Jun 13, 2014 at 12:26
@onskee yeah you are right.. this will avoid sql injections. — Shell
– Shell, Commented Jun 13, 2014 at 13:41

Shell · Accepted Answer · 2015-03-02 08:27:06Z

3

Try like this,

sqlSave = "INSERT INTO [Product] (Title, expiryDate) VALUES ("
                sqlSave += " '" & txtProductName.Text.Trim().Replace("'", "''") & "', "
                sqlSave += "'" & Convert.ToDateTime(expiryDate.Text).ToString("yyyy-MM-dd") & "' )"

But, I would like to suggest you to use parameterized query instead of simple concatenated string.

cmd.CommandText = "INSERT INTO [Product] (Title, expiryDate) VALUES (@Title,@expiryDate)";

cmd.Parameters.Add("@Title", SqlDbType.VarChar).Value = txtProductName.Text.Trim().Replace("'", "''");
cmd.Parameters.Add("@expiryDate", SqlDbType.DateTime).Value = Convert.ToDateTime(expiryDate.Text);

edited Mar 2, 2015 at 8:27

answered Jun 13, 2014 at 7:16

Shell

6,86911 gold badges43 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anjum Over a year ago

Well that column's type is "date" in DB, not "datetime"

Chris Dunaway Over a year ago

Why convert a string to a date and then back to a string only to insert it into a Date column in the DB? Why not just insert the date directly using a Date parameter?

Shell Over a year ago

have u read all comments in this question @ChrisDunaway. I have already suggested and upvoted onskee's comment.

cachet.net · Accepted Answer · 2014-06-13 07:24:53Z

0

Replace

" & expiryDate.Text & "

with

'" & expiryDate.Text & "'

answered Jun 13, 2014 at 7:24

cachet.net

3204 silver badges18 bronze badges

4 Comments

Shell Over a year ago

what happen when date is formatted like 01/06/2014?

cachet.net Over a year ago

It is the rule to save data of type datetime in sql database. should be enclosed with two quotes e.g. 'MM-dd-yyyy'

Shell Over a year ago

No I am not talking about rule.. if the date format is "dd/MM/yyyy" or "MM/dd/yyyy" then it creates problem. by default sql format is "yyyy-MM-dd". So, either we have to pass date in "yyyy-MM-dd" format or we have to change sql date format using SET DATEFORMAT 'DMY'.

Chris Dunaway Over a year ago

No, better to not use string concatenation. Instead, use parameters and specify the date directly as a datetime variable. That way you don't have to worry about string conversions.

Collectives™ on Stack Overflow

Saving Date string to SQL Server date type column in asp.net (vb.net)

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related