winform C# using sql server 2008

Question

I have a problem with database I am using winform in c# when i enter data through form it create a space in name column before data so i want to remove it can anyone help me name column has datatype varchar whereas contact no has numeric datatype it won't create space before it

My Code:

private void btnAddNew_Click(object sender, EventArgs e)
{
        string Gender = "";

        if (radioButton1.Checked)
        {
            Gender = "Male";
        }
        else if (radioButton2.Checked)
        {
            Gender = "Female";
        }

        if (txtName.Text == "")
        {
            MessageBox.Show("Enter the customer name.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Stop);
            txtName.Focus();
        }
        else if (Gender == "")
        {
            MessageBox.Show("Enter the gender.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Stop);
            grpGender.Focus();
        }
        else if (txtAddress.Text == "")
        {
            MessageBox.Show("Enter the Address.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Stop);
            txtAddress.Focus();
        }
        else if (txtContact.Text == "")
        {
            MessageBox.Show("Enter Contact No.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Stop);
            txtContact.Focus();
        }
        else
        {
            SqlConnection con = new SqlConnection("Data Source=RANJEETMAURYA;Initial Catalog=Project;Integrated Security=True");
            con.Open();

            SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
                     (Date, Contact_No, Name, Gender, Address, Email_ID)
VALUES(' " + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + " ',' " + txtName.Text + " ',' " + Gender + " ',' " + txtAddress.Text + " ',' " + txtContact.Text + " ',' " + txtEmail.Text + " ')", con);
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Customer Information Added Successfully.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
            SQLFunctions.Refresh(this.dataGridView1);
            clear();
        }
}

can you show the code and what you've tried? Has the input in the form got the space or do you believe it is being added as you say? — user2046117
– user2046117, Commented Feb 19, 2014 at 8:39
Might help if you can show an example. Its not clear what you mean. — Fred
– Fred, Commented Feb 19, 2014 at 8:39
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Feb 19, 2014 at 8:42

Sudhakar Tillapudi · Accepted Answer · 2014-02-19 08:46:35Z

Problem : You are adding extra spaces while providing parameter values in INSERT INTO statement as below:

        |
        |
       >
VALUES(' " + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + " ',' " + txtName.Text    
+ " ',' " + Gender + " ',' " + txtAddress.Text + " ',' " + txtContact.Text + " ',' " +     
txtEmail.Text + " ')", con);

Suggestion: your query is open to sqlinjection attacks so i would suggest you to use Parameterised queries to avoid them.

Try This: using Parameterised Queries

Replace This:

SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
(Date, Contact_No, Name, Gender, Address, Email_ID)
VALUES(' " + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + " ',' " + txtName.Text + " ',' " + Gender + " ',' " + txtAddress.Text + " ',' " + txtContact.Text + " ',' " + txtEmail.Text + " ')", con);

With This:

 SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
    (Date, Contact_No, Name, Gender, Address, Email_ID)
    VALUES(@Date,@Contact,,@Name,@Gender,@Address,@Email)", con);

 cmd.Parameters.AddWithValue("@Date",this.dateTimePicker1.Value.ToString("MM/dd/yyyy"));
 cmd.Parameters.AddWithValue("@Contact",txtContact.Text);
 cmd.Parameters.AddWithValue("@Name",txtName.Text);
 cmd.Parameters.AddWithValue("@Gender",Gender);
 cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
 cmd.Parameters.AddWithValue("@Email", txtEmail.Text);

Damith · Accepted Answer · 2014-02-19 08:43:59Z

0

remove the spaces

" ',' " + txtName.Text.Trim() + " ',' " + Gender + " ',' "

to

"','" + txtName.Text.Trim() + "','" + Gender + "','"

but You better delete all these string concatenation and start using SQL parameters

answered Feb 19, 2014 at 8:43

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Comments

Hessam Abbasi · Accepted Answer · 2014-02-19 08:48:08Z

You have some additional spaces right after and before "'". Remove them, because it will directly inserts into database.

Use this :

 SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
                     (Date, Contact_No, Name, Gender, Address, Email_ID)
VALUES('" + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + "','" + txtName.Text + "','" + Gender + "','" + txtAddress.Text + "','" + txtContact.Text + "','" + txtEmail.Text + "')", con);
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Customer Information Added Successfully.", "Dairy Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
            SQLFunctions.Refresh(this.dataGridView1);
            clear();

Shell · Accepted Answer · 2014-08-23 04:35:12Z

look at this line

VALUES(' " + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + " ',' " + txtName.Text + " ',' " + Gender + " ',' " + txtAddress.Text + " ',' " + txtContact.Text + " ',' " + txtEmail.Text + " ')", con);

you have placed a space before and after the single quotation. for example

' " + this.dateTimePicker1.Value.ToString("MM/dd/yyyy") + " ',

Actually you have specified space after and before all the value assignment.

BTW, this is not a good method to Insert/Update/Delete operation in database. You should use Parenthesized Query instead.

SqlCommand Cmd = Connection.CreateCommand();
Cmd.CommandText = "Insert Into [TableName](Column1,Column2,Column3)Values(@Column1,Column2,Column3)";
Cmd.Parameters.Add("@@Column1", SqlDbType.Int).Value = 1;
Cmd.Parameters.Add("@@Column1", SqlDbType.Varchar).Value = "Shell";
Cmd.Parameters.Add("@@Column1", SqlDbType.SmallDateTime).Value = System.DateTime.Now;
Cmd.ExecuteNonQuery();

Collectives™ on Stack Overflow

winform C# using sql server 2008

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related