Convert nvarchar to date error through

Question

1.00/5 (1 vote)

See more:

private void btnSave_Click(object sender, RoutedEventArgs e)
     {
         try
         {
             if (isValid())
             {
                // id1 = picUserimage.Source.ToString();
                 SqlConnection conn = new SqlConnection(ConnectionString);
                 conn.Open();
                 SqlCommand cmd = new SqlCommand("add_profile_sp", conn);
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.AddWithValue("@company_id", SqlDbType.Int).Value = string.IsNullOrWhiteSpace(txtCompanyId.Text) ? DBNull.Value : (object)txtCompanyId.Text;
                 cmd.Parameters.AddWithValue("@company_name", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCompanyName.Text) ? DBNull.Value : (object)txtCompanyName.Text;
                 cmd.Parameters.AddWithValue("@caption", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCaption.Text) ? DBNull.Value : (object)txtCaption.Text;
                 cmd.Parameters.AddWithValue("@address1", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtAddress1.Text) ? DBNull.Value : (object)txtAddress1.Text;
                 cmd.Parameters.AddWithValue("@address2", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtAddress2.Text) ? DBNull.Value : (object)txtAddress2.Text;
                 cmd.Parameters.AddWithValue("@mobileno", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtMobile.Text) ? DBNull.Value : (object)txtMobile.Text;
                 cmd.Parameters.AddWithValue("@email", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtEmail.Text) ? DBNull.Value : (object)txtEmail.Text;
                 cmd.Parameters.AddWithValue("@gst", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtGstin.Text) ? DBNull.Value : (object)txtGstin.Text;
                 cmd.Parameters.AddWithValue("@cloudApi", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCloudApi.Text) ? DBNull.Value : (object)txtCloudApi.Text;
                 cmd.Parameters.AddWithValue("@username", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtUsername.Text) ? DBNull.Value : (object)txtUsername.Text;
                 cmd.Parameters.AddWithValue("@password", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtPassword.Password) ? DBNull.Value : (object)txtPassword.Password;
                 cmd.Parameters.AddWithValue("@selectusertype", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtcombox.Text) ? DBNull.Value : (object)txtcombox.Text;
                 //cmd.Parameters.AddWithValue("@uploadlogo", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(id1) ? DBNull.Value : (object)id1;
                 cmd.Parameters.AddWithValue("@uploadlogo", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(picUserimage.Source.ToString()) ? DBNull.Value : (object)picUserimage.Source.ToString();
                 cmd.Parameters.AddWithValue("@activationkey", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtActivationKey.Text) ? DBNull.Value : (object)txtActivationKey.Text;
                 cmd.Parameters.AddWithValue("@createdby", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCreatedby.Text) ? DBNull.Value : (object)txtCreatedby.Text;
                 cmd.Parameters.AddWithValue("@date", SqlDbType.Date).Value = string.IsNullOrWhiteSpace(txtDate.Text) ? DBNull.Value : (object)txtDate.Text;
                 cmd.ExecuteNonQuery();
                 conn.Close();
                 MessageBox.Show("Successfully saved", "Fill Field", MessageBoxButton.OK, MessageBoxImage.Information);
                 refresh();
                 btnUpdate.IsEnabled = false;
                 New_Add_Profile nap = new New_Add_Profile();
                 nap.Show();
                 this.Close();
             }
         }
         catch (SqlException ex)
         {
             MessageBox.Show(ex.Message);
         }
     }

What I have tried:

<pre>   private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (isValid())
                {
                   // id1 = picUserimage.Source.ToString();
                    SqlConnection conn = new SqlConnection(ConnectionString);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("add_profile_sp", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@company_id", SqlDbType.Int).Value = string.IsNullOrWhiteSpace(txtCompanyId.Text) ? DBNull.Value : (object)txtCompanyId.Text;
                    cmd.Parameters.AddWithValue("@company_name", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCompanyName.Text) ? DBNull.Value : (object)txtCompanyName.Text;
                    cmd.Parameters.AddWithValue("@caption", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCaption.Text) ? DBNull.Value : (object)txtCaption.Text;
                    cmd.Parameters.AddWithValue("@address1", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtAddress1.Text) ? DBNull.Value : (object)txtAddress1.Text;
                    cmd.Parameters.AddWithValue("@address2", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtAddress2.Text) ? DBNull.Value : (object)txtAddress2.Text;
                    cmd.Parameters.AddWithValue("@mobileno", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtMobile.Text) ? DBNull.Value : (object)txtMobile.Text;
                    cmd.Parameters.AddWithValue("@email", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtEmail.Text) ? DBNull.Value : (object)txtEmail.Text;
                    cmd.Parameters.AddWithValue("@gst", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtGstin.Text) ? DBNull.Value : (object)txtGstin.Text;
                    cmd.Parameters.AddWithValue("@cloudApi", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCloudApi.Text) ? DBNull.Value : (object)txtCloudApi.Text;
                    cmd.Parameters.AddWithValue("@username", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtUsername.Text) ? DBNull.Value : (object)txtUsername.Text;
                    cmd.Parameters.AddWithValue("@password", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtPassword.Password) ? DBNull.Value : (object)txtPassword.Password;
                    cmd.Parameters.AddWithValue("@selectusertype", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtcombox.Text) ? DBNull.Value : (object)txtcombox.Text;
                    //cmd.Parameters.AddWithValue("@uploadlogo", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(id1) ? DBNull.Value : (object)id1;
                    cmd.Parameters.AddWithValue("@uploadlogo", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(picUserimage.Source.ToString()) ? DBNull.Value : (object)picUserimage.Source.ToString();
                    cmd.Parameters.AddWithValue("@activationkey", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtActivationKey.Text) ? DBNull.Value : (object)txtActivationKey.Text;
                    cmd.Parameters.AddWithValue("@createdby", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCreatedby.Text) ? DBNull.Value : (object)txtCreatedby.Text;
                    cmd.Parameters.AddWithValue("@date", SqlDbType.Date).Value = string.IsNullOrWhiteSpace(txtDate.Text) ? DBNull.Value : (object)txtDate.Text;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Successfully saved", "Fill Field", MessageBoxButton.OK, MessageBoxImage.Information);
                    refresh();
                    btnUpdate.IsEnabled = false;
                    New_Add_Profile nap = new New_Add_Profile();
                    nap.Show();
                    this.Close();
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Posted 15-Sep-22 19:36pm

prashanth manoj

Updated 15-Sep-22 20:01pm

Add a Solution

Comments

PIEBALDconsult 16-Sep-22 14:45pm

That is an awful lot of needless duplication of needless code.

1 solution

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)

OriginalGriff · Answer 1 · 2022-09-15T20:01:00

Simple: the text box is not a date in a format that SQL can recognize. Remember, SQL Server doesn't often on the same computer that the application sending it data does (except in development) so it's Locale settings can be wildly different from the settings the User has specified and entered his data in.

Additionally, users make mistakes: they type dates wrong all the time!

The solution to both causes is the same, and is simple: validate and convert your dates when the user inputs them, and send them to SQL as DateTime values instead of strings. That way, the data sent is independent of the user preferences, and is guaranteed to be compatible between the two systems.

I'd also recommend that you write a helper method to reduce the clutter of that code:

C#

cmd.Parameters.AddWithValue("@caption", SqlDbType.VarChar).Value = string.IsNullOrWhiteSpace(txtCaption.Text) ? DBNull.Value : (object)txtCaption.Text;

Is a lot harder to read than:

C#

AddParameterValue(cmd, "@caption", txtCaption.Text);

and then sort out the text / null value in the helper. It's also much less prone to error ...