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

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 ...

Convert nvarchar to date error through

1 solution

Solution 1

Add your solution here

Preview 0

Convert nvarchar to date error through

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us