Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi gentelmen this is my code
it gave me a fault in

ID = Convert.ToInt32(cmd.ExecuteScalar());
System.Data.SqlClient.SqlException: 'Error converting data type nvarchar to date.'

What I have tried:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    int ID = 0;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("1400-88"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FROM", txtStartDate.Text.Trim());
                cmd.Parameters.AddWithValue("@TO", txtEndDate.Text.Trim());
                cmd.Parameters.AddWithValue("@TYPE_ASSESSMENT", DropDownList1.Text.Trim());
                cmd.Parameters.AddWithValue("@BASE_WING", DropDownList20.Text.Trim());
                cmd.Parameters.AddWithValue("@SQUADRON", DropDownList3.Text.Trim());
                cmd.Parameters.AddWithValue("@FLIGHT_STAFF", DropDownList4.Text.Trim());
                cmd.Parameters.AddWithValue("@BRANCH", DropDownList5.Text.Trim());
                cmd.Parameters.AddWithValue("@WORK_CENTER", DropDownList6.Text.Trim());
                cmd.Parameters.AddWithValue("@REPEAT", DropDownList7.Text.Trim());
                cmd.Parameters.AddWithValue("@MAJOR", DropDownList8.Text.Trim());
                cmd.Parameters.AddWithValue("@SAFETY", DropDownList9.Text.Trim());
                cmd.Parameters.AddWithValue("@MINOR", DropDownList10.Text.Trim());
                cmd.Parameters.AddWithValue("@SPECIAL", DropDownList11.Text.Trim());
                cmd.Parameters.AddWithValue("@AQLA", DropDownList12.Text.Trim());
                cmd.Parameters.AddWithValue("@OTHER", DropDownList13.Text.Trim());
                cmd.Parameters.AddWithValue("@REPORT_CONTROL", TextBox6.Text.Trim());
                cmd.Parameters.AddWithValue("@OBSERVATION", TextBox2.Text.Trim());
                cmd.Parameters.AddWithValue("@REFERENCE", TextBox5.Text.Trim());
                cmd.Parameters.AddWithValue("@MOST_PROBABLE_CAUSE", DropDownList15.Text.Trim());
                cmd.Parameters.AddWithValue("@REFERENCE_AS_APPROPERIATE", TextBox3.Text.Trim());
                cmd.Parameters.AddWithValue("@RECOMMANDED_CORRECTIVE_ACTION", DropDownList17.Text.Trim());
                cmd.Parameters.AddWithValue("@QMA_CHECKLIST_TASK", drbTaskList.Text.Trim());
                cmd.Parameters.AddWithValue("@INSPECTOR_INFO", DropDownList18.Text.Trim());
                cmd.Parameters.AddWithValue("@INTERVAL", DropDownList19.Text.Trim());
                cmd.Parameters.AddWithValue("@CORRECTIVE_ACTION", TextBox7.Text.Trim());
                cmd.Parameters.AddWithValue("@OPR", DropDownList21.Text.Trim());
                cmd.Parameters.AddWithValue("@CORRECTED_BY", TextBox9.Text.Trim());
                cmd.Parameters.AddWithValue("@DATE_CORRECTED", TextBox8.Text.Trim());
                cmd.Connection = con;
                con.Open();
                ID = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
            }
Posted
Updated 14-Aug-20 7:52am
Comments
Sandeep Mewara 14-Aug-20 6:58am    
Error is coming from your stroed proc where you have some data convert in place. You have not shared that so would be difficult to pin point the line.
Second thought - given the error, it should be mostly solved in C# side itself (with code that you have shared) so will share how to solve for it.
Jin Vincent Necesario 14-Aug-20 7:15am    
I believe when you encounter the error: "Error converting data type varchar to date" you have passed a string that can't be parsed into a date. In other words, you need to check if you have passed a properly formatted date. You can check the following: @DATE_CORRECTED, @FROM, and @TO.

One of the values you're passing in is not a valid representation of a date.

We can't tell you which one, because it depends on the values in your controls, which we can't see, and the types of your stored procedure parameters, which we also can't see.

Use DateTime.TryParse[^] to attempt to parse the necessary values as dates before passing them to AddWithValue.
 
Share this answer
 
Reference: SqlException Class (System.Data.SqlClient) | Microsoft Docs[^]

Quote:
The exception that is thrown when SQL Server returns a warning or error. This class cannot be inherited.


Error was raised from SQL database, mostly because of some query execution in your stored procedure.

Now, two ways to solve it:
1. Pass the right data as expected from your C# itself to SP
2. Check the data being passed is valid value that you can convert in SP


Member 14914355 wrote:
'Error converting data type nvarchar to date.'

Most possibly, either your date passed as string is empty or has some invalid characters that fail to get converted into a date format. Would suggest to convert it into Datetime in C# using Convert.ToDateTime[^] or DateTime.Parse[^] whatever suits you and then pass it.

Try and see.

UPDATE:
BTW, you can also solve this in Stored Procedure too in case the error is just because of the format of the date and your convert is using some different format value.
Reference:
Date and Time Conversions Using SQL Server[^]
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
v3
SORRY FOR INTERRUPT THIS THE STORED PROCEDURES

CREATE PROCEDURE [dbo].[1400-88]
	@FROM DATE ,
	@TO DATE ,
	@TYPE_ASSESSMENT NVARCHAR(50),
	@BASE_WING NVARCHAR(50),
	@SQUADRON NVARCHAR(50),
	@FLIGHT_STAFF NVARCHAR(50),
	@BRANCH NVARCHAR(50),
	@WORK_CENTER NVARCHAR(50),
	@REPEAT NVARCHAR(50),
	@MAJOR NVARCHAR(50),
	@SAFETY NVARCHAR(50),
	@MINOR NVARCHAR(50),
	@SPECIAL NVARCHAR(50),
	@AQLA NVARCHAR(50),
	@OTHER NVARCHAR(50),
	@REPORT_CONTROL NVARCHAR(50),
    @OBSERVATION NVARCHAR(50),
    @REFERENCE NVARCHAR(50),
    @MOST_PROBABLE_CAUSE NVARCHAR(50),
    @REFERENCE_AS_APPROPERIATE NVARCHAR(50),
    @RECOMMANDED_CORRECTIVE_ACTION NVARCHAR(50),
    @QMA_CHECKLIST_TASK# NVARCHAR(50),
    @INSPECTOR_INFO NVARCHAR(50),
    @INTERVAL NVARCHAR(50),
	@CORRECTIVE_ACTION NVARCHAR(500),
	@OPR NVARCHAR(10),
	@CORRECTED_BY NVARCHAR(20),
	@DATE_CORRECTED DATETIME

AS
BEGIN
		INSERT INTO [1400-88-QAR]
	([DATE FROM]
	,[DATE TO]
	,[TYPE ASSESSMENT]
    ,[BASE-WING]
	,[SQUADRON]
	,[FLIGHT/STAFF]
	,[BRANCH]
	,[WORKCENTER]
	,[REPEAT]
	,[MAJOR]
	,[SAFETY]
	,[MINOR]
	,[SPECIAL]
	,[AQLA]
	,[OTHER]
	,[REPORT CONTROL #]
    ,[OBSERVATION]
    ,[REFERENCE]
    ,[MOST PROBABLE CAUSE]
    ,[REFERENCE (as APPROPERIATE)]
    ,[RECOMMENDED CORRECIVE ACTION]
    ,[QMA CHECKLIST TASK #]
    ,[INSPECTOR INFO]
    ,[INTERVAL]
	,[CORRECTIVE_ACTION]
	,[OPR]
	,[CORRECTED_BY]
	,[DATE_CORRECTED])
		VALUES
	(@FROM
	,@TO
	,@TYPE_ASSESSMENT
	,@BASE_WING
	,@SQUADRON
	,@FLIGHT_STAFF
	,@BRANCH
	,@WORK_CENTER
	,@REPEAT
	,@MAJOR
	,@SAFETY
	,@MINOR
	,@SPECIAL
	,@AQLA
	,@OTHER
	,@REPORT_CONTROL
    ,@OBSERVATION
    ,@REFERENCE
    ,@MOST_PROBABLE_CAUSE
    ,@REFERENCE_AS_APPROPERIATE
    ,@RECOMMANDED_CORRECTIVE_ACTION
    ,@QMA_CHECKLIST_TASK#
    ,@INSPECTOR_INFO
    ,@INTERVAL
	,@CORRECTIVE_ACTION
	,@OPR
	,@CORRECTED_BY
	,@DATE_CORRECTED)
		
		SELECT SCOPE_IDENTITY() -- 1400-88	   
    
END
 
Share this answer
 
Comments
Patrice T 14-Aug-20 13:59pm    
Use Improve question to update your question.
So that everyone can pay attention to this information.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900