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. http://stackoverflow.com/questions/910465/avoiding-sql-injection-without-parameters — InbetweenWeekends, Jun 13 '14 at 12:26
@onskee yeah you are right.. this will avoid sql injections. — Shell, Jun 13 '14 at 13:41

Shell · Accepted Answer · 2015-03-02T08:27:06.987

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 02 '15 at 08:27

answered Jun 13 '14 at 07:16

Shell

6,818
11
39
70

Well that column's type is "date" in DB, not "datetime" – Anjum Jun 13 '14 at 07:18
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? – Chris Dunaway Jun 13 '14 at 14:54
1

have u read all comments in this question @ChrisDunaway. I have already suggested and upvoted onskee's comment. – Shell Jun 13 '14 at 16:52

score 0 · Answer 2 · answered Jun 13 '14 at 07:24

0

Replace

" & expiryDate.Text & "

with

'" & expiryDate.Text & "'

answered Jun 13 '14 at 07:24

cachet.net

310
3
16

what happen when date is formatted like `01/06/2014`? – Shell Jun 13 '14 at 07:29
It is the rule to save data of type datetime in sql database. should be enclosed with two quotes e.g. 'MM-dd-yyyy' – cachet.net Jun 13 '14 at 08:42
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'`. – Shell Jun 13 '14 at 08:44
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. – Chris Dunaway Jun 13 '14 at 14:55

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

2 Answers2