I have problem with stored procedure

Question

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 14-Aug-20 0:55am

Member 14914355

Updated 14-Aug-20 7:52am

Add a Solution

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.

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

Richard Deeming · Answer 1 · 2020-08-14T00:59:00

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.

Sandeep Mewara · Answer 2 · 2020-08-14T01:03:00

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[^]

Member 14914355 · Answer 3 · 2020-08-14T07:52:00

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

I have problem with stored procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0