I am having one error and don't know how to solve. The error details are below

Question

0.00/5 (No votes)

See more:

C#

Conversion failed when converting date and/or time from character string.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.

Source Error: 


Line 29:         string s1 = "insert into Registration values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+RadioButtonList1.SelectedItem.Text+"','"+DropDownList3.SelectedItem.Text+"','"+TextBox5.Text+"','"+TextBox6.Text+"','"+TextBox7.Text+"','"+TextBox8.Text+"','"+DropDownList1.SelectedItem.Text+"','"+DropDownList2.SelectedItem.Text+"','"+TextBox9.Text+"')";
Line 30:         SqlCommand cmd = new SqlCommand(s1, con);
Line 31:         cmd.ExecuteNonQuery();
Line 32:         Response.Redirect("RegAck.aspx");
Line 33:     }

Source File: c:\Users\Dixit\Desktop\DOWNLOADED PROJECT\online-e\online-e\78\onlineexamination\Register.aspx.cs    Line: 31 

Stack Trace: 


[SqlException (0x80131904): Conversion failed when converting date and/or time from character string.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1961130
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4890575
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2412
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +317
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   Register.Button1_Click(Object sender, EventArgs e) in c:\Users\Dixit\Desktop\DOWNLOADED PROJECT\online-e\online-e\78\onlineexamination\Register.aspx.cs:31
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Version Information: Microsoft .NET Framework Version:2.0.50727.8009; ASP.NET Version:2.0.50727.8015

What I have tried:

hare is my Register.aspx file :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Register.aspx.cs" Inherits="Register" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head  runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1"  runat="server">
    <div>
     <asp:Panel ID="Panel1" runat="server" BackColor="Transparent" BorderColor="Black"
            BorderWidth="2px" Height="416px" Style="z-index: 100; left:96px; position: absolute;
            top: 32px" Width="792px" BackImageUrl="~/pictures/img30.jpg">
                                   
                                
              <span style="font-size: 24pt; color: #000033; font-family: Monotype Corsiva">
                 <span style="text-decoration: underline">Registration Form</span>       
                 
                 
             <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 100; left: 72px; position: absolute;
                 top: 96px">
             <asp:TextBox ID="TextBox2" runat="server" Style="z-index: 101; left: 464px; position: absolute;
                 top: 88px">
                
             
             <asp:DropDownList ID="DropDownList3" runat="server" Style="z-index: 125; left: 464px;
                 position: absolute; top: 128px" Width="152px">
                 <asp:ListItem>--select--
                 <asp:ListItem>.Net
                 
             
             <asp:TextBox ID="TextBox5" runat="server" Style="z-index: 103; left: 336px; position: absolute;
                 top: 192px" OnTextChanged="TextBox5_TextChanged">
             <asp:TextBox ID="TextBox6" runat="server" Style="z-index: 104; left: 80px; position: absolute;
                 top: 272px">
             <asp:TextBox ID="TextBox7" runat="server" Style="z-index: 105; left: 328px; position: absolute;
                 top: 272px" TextMode="Password">
                  <span style="text-decoration: underline"> </span>
             <asp:TextBox ID="TextBox8" runat="server" Style="z-index: 106; left: 600px; position: absolute;
                 top: 272px" TextMode="Password">
             <span style="text-decoration: underline"></span>
             <asp:TextBox ID="TextBox9" runat="server" Style="z-index: 107; left: 600px; position: absolute;
                 top: 320px">
             <span style="text-decoration: underline"></span></span>
            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="z-index: 108;
                 left: 264px; position: absolute; top: 368px" Text="Submit" Height="30px" Width="70px" />
                 <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Style="z-index: 109;
                     left: 448px; position: absolute; top: 368px" Text="Cancel" Height="30px" Width="70px" />
           
        <asp:Label ID="Label1" runat="server" BackColor="LemonChiffon" Font-Bold="True" ForeColor="Red"
            Style="z-index: 110; left: 0px; position: absolute; top: 56px" Text="Personnel Details"
            Width="784px">
        <asp:Label ID="Label2" runat="server" Font-Bold="True" Style="z-index: 111; left: 8px;
            position: absolute; top: 96px" Text="Name">
        <asp:Label ID="Label3" runat="server" Font-Bold="True" Style="z-index: 112; left: 336px;
            position: absolute; top: 96px" Text="DOB">
        <asp:Label ID="Label4" runat="server" Font-Bold="True" Style="z-index: 113; left: 8px;
            position: absolute; top: 144px" Text="Gender">
        <asp:Label ID="Label5" runat="server" Font-Bold="True" Style="z-index: 114; left: 336px;
            position: absolute; top: 136px" Text="Branch">
        <asp:Label ID="Label6" runat="server" Font-Bold="True" Style="z-index: 115; left: 256px;
            position: absolute; top: 192px" Text="College">
        <asp:Label ID="Label7" runat="server" BackColor="LemonChiffon" Font-Bold="True" ForeColor="Red"
            Style="z-index: 116; left: 0px; position: absolute; top: 232px" Text="Login Details"
            Width="784px">
        <asp:Label ID="Label8" runat="server" Font-Bold="True" Style="z-index: 117; left: 0px;
            position: absolute; top: 272px" Text="User Id">
        <asp:Label ID="Label9" runat="server" Font-Bold="True" Style="z-index: 118; left: 248px;
            position: absolute; top: 272px" Text="Password">
         
        <asp:Label ID="Label11" runat="server" Font-Bold="True" Style="z-index: 119; left: 536px;
            position: absolute; top: 328px" Text="Answer">
        <asp:DropDownList ID="DropDownList1" runat="server" Style="z-index: 120; left: 80px;
            position: absolute; top: 328px" Width="152px">
            <asp:ListItem>--select--
            <asp:ListItem>Admin
            <asp:ListItem>Instructor
            <asp:ListItem>Student
        
        <asp:DropDownList ID="DropDownList2" runat="server" Style="z-index: 121; left: 352px;
            position: absolute; top: 328px" Width="160px">
            <asp:ListItem>--select--
            <asp:ListItem>what is your pet name?
            <asp:ListItem>who is your favourite cricketer?
            <asp:ListItem>what is your ssn ?
        
        <asp:Label ID="Label12" runat="server" Font-Bold="True" Style="z-index: 122; left: 280px;
            position: absolute; top: 328px" Text="Question">
        <asp:Label ID="Label13" runat="server" Font-Bold="True" Style="z-index: 123; left: 0px;
            position: absolute; top: 328px" Text="User Type">
        <asp:Label ID="Label14" runat="server" Font-Bold="True" Height="8px" Style="z-index: 124;
            left: 512px; position: absolute; top: 264px" Text="Re-Enter Password" Width="72px">
             <asp:RadioButtonList ID="RadioButtonList1" runat="server" Font-Bold="True" RepeatDirection="Horizontal"
            Style="z-index: 102; left: 72px; position: absolute; top: 136px">
            <asp:ListItem>Female
            <asp:ListItem>Male
        
     
       
    </div>
    </form>
</body>
</html>

here is my Register.aspx.cs file :

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Register : System.Web.UI.Page
{

    SqlConnection con = new SqlConnection();

    public Register()
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        con.Open();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Session["sid"] = TextBox6.Text;
        string s1 = "insert into Registration values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+RadioButtonList1.SelectedItem.Text+"','"+DropDownList3.SelectedItem.Text+"','"+TextBox5.Text+"','"+TextBox6.Text+"','"+TextBox7.Text+"','"+TextBox8.Text+"','"+DropDownList1.SelectedItem.Text+"','"+DropDownList2.SelectedItem.Text+"','"+TextBox9.Text+"')";
        SqlCommand cmd = new SqlCommand(s1, con);
        cmd.ExecuteNonQuery();
        Response.Redirect("RegAck.aspx");
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
        TextBox7.Text = "";
        TextBox8.Text = "";
        TextBox9.Text = "";
        DropDownList1.SelectedItem.Text = DropDownList1.Items[0].Text;
        DropDownList2.SelectedItem.Text = DropDownList2.Items[0].Text;
        DropDownList3.SelectedItem.Text = DropDownList3.Items[0].Text;
        RadioButtonList1.Items[0].Selected = false;
        RadioButtonList1.Items[1].Selected = false;
    }

    protected void TextBox5_TextChanged(object sender, EventArgs e)
    {

    }
}

Posted 15-Mar-16 20:21pm

Member 12170781

Updated 16-Mar-16 0:26am

Jörgen Andersson

Add a Solution

Comments

VR Karthikeyan 16-Mar-16 5:40am

You are providing string value to the database table column, which is defined as DateTime column. Provide DateTime value to the DateTime column.

Member 12170781 16-Mar-16 5:52am

have a look at my Registration table :

CREATE TABLE [dbo].[Registration]
(
[Id] INT IDENTITY (100, 1) NOT NULL,
[name] VARCHAR (50) NOT NULL,
[DOB] DATETIME NOT NULL,
[gender] VARCHAR (50) NOT NULL,
[branch] VARCHAR (50) NOT NULL,
[college] VARCHAR (50) NOT NULL,
[uid] VARCHAR (50) NOT NULL,
[pwd] VARCHAR (50) NOT NULL,
[rpwd] VARCHAR (50) NOT NULL,
[utype] VARCHAR (50) NOT NULL,
[que] VARCHAR (500) NOT NULL,
[ans] VARCHAR (5000) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

then also same error is coming

VR Karthikeyan 16-Mar-16 6:01am

Your 3rd column is DOB of type DateTime, you are passing TextBox2.Text to this column, a DateTime column cannot accept string values. What is the value you are getting in TextBox2?

Member 12170781 16-Mar-16 6:06am

I am passing date(for e.g :01/01/2016) in TextBox2 but it is not getting converted into date

VR Karthikeyan 16-Mar-16 6:17am

It is not get converted automatically, we have to convert it. see the solution below.

Richard Deeming 16-Mar-16 8:44am

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Accepted Answer · 2016-03-16T00:21:00

Solution 1

Don't make sql statements using string concatenation, your code is open to sql injection errors. Used parameterised queries instead

Configuring Parameters and Parameter Data Types[^]

Convert the text in your textbox into a DateTime variable using DateTime.TryParse and use that variable in your query parameter.

This question is asked very frequently, google the error message and you'll find lots of code examples on how to do this properly.

Posted 16-Mar-16 0:21am

F-ES Sitecore

Comments

Member 12170781 16-Mar-16 6:43am

thank you

CHill60 16-Mar-16 7:01am

Really do change to using parameterised queries - quite apart from helping to protect from sql injection it also makes the whole thing more readable and can help to prevent errors. Here is a useful link to read The Right Way To Query A Database: Parameterizing Your SQL Queries. - C# Tutorials | Dream.In.Code[^]

VR Karthikeyan · Accepted Answer · 2016-03-16T00:26:00

Solution 2

Hi, just change your code like below,

C#

DateTime DateConverted;
bool IsValid = DateTime.TryParse(TextBox2.Text.Trim(), out DateConverted);
if(IsValid == false)
{
  return; //value in TextBox2 is not valid
}
string s1 = "insert into Registration values('" 
            + TextBox1.Text + "','" 

            + DateConverted + "','" //string to DateTime conversion

            + RadioButtonList1.SelectedItem.Text + "','" 
            + DropDownList3.SelectedItem.Text + "','" 
            + TextBox5.Text + "','" 
            + TextBox6.Text + "','" 
            + TextBox7.Text + "','" 
            + TextBox8.Text + "','" 
            + DropDownList1.SelectedItem.Text + "','" 
            + DropDownList2.SelectedItem.Text + "','" 
            + TextBox9.Text + "')";

And, take a look at Casting and Type Conversions (C# Programming Guide)[^]
Follow the guidelines on Solution 1.

Posted 16-Mar-16 0:26am

VR Karthikeyan

Updated 16-Mar-16 0:56am

v3

Comments

F-ES Sitecore 16-Mar-16 6:42am

The problem with Parse is that it will throw an exception if the text in TextBox2 can't be converted to DateTime and he is back to his original problem. Also if it contains "1/2/2016" is that 1st Feb or 2nd Jan?

VR Karthikeyan 16-Mar-16 6:53am

You are right, F-ES Sitecore. Thanks a lot, I came across this problem, I forgot the problem of using Parse method. Thank you for guiding me in a right way.

Member 12170781 16-Mar-16 6:55am

then instead of parse what should I use?

VR Karthikeyan 16-Mar-16 6:57am

I have updated the solution. See the solution.

Member 12170781 16-Mar-16 7:13am

after applying your new solution I am facing some different problem. The problem is that after clicking on submit button the Password and re-enter password's textbox value get disappear and remaining textbox value remain as it is also it is not getting direct to next page

VR Karthikeyan 16-Mar-16 7:18am

Try to debug your code, if IsValid variable is equal to false, the lines after this won't execute. Try with different date and check when this problem occurs.

Member 12170781 16-Mar-16 7:28am

yes, I got it.Thank you. It is accepting 'yyyy-mm-dd' format. If I want to change the format(for e.g dd-mm-yyyy) then what changes should I do ?

VR Karthikeyan 16-Mar-16 7:36am

Use like this,
DateConverted.ToString("dd-MM-yyyy")

Member 12170781 16-Mar-16 7:43am

Thank you again

Richard Deeming 16-Mar-16 8:44am

You've copied the SQL Injection[^] vulnerability from the question.