CODEWITHSUNDEEP
asp.netvb.net
Anjum
Anjum

Reputation: 681

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

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?

Upvotes: 1

Views: 5235

Answers (2)

Shell
Shell

Reputation: 6849

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);

Upvotes: 3

cachet.net
cachet.net

Reputation: 320

Replace 

" & expiryDate.Text & "

with

'" & expiryDate.Text & "'

Upvotes: 0

Related Questions