adding value into sql using textboxes

Question

I'm trying to add values into my database using text boxes.

private void btnAdd_Click(object sender, EventArgs e)
{
    try
    {
        string MemberID = txtMember.Text;
        string FirstName = txtFirstName.Text;
        string LastName = txtLastName.Text;
        string Phone = txtTelephone.Text;
        string Email = txtEmail.Text;

        sql = " INSERT INTO A_Member ( MemberID, LastName, FirstName, Phone, Email) VALUES ( @Member, @LastName, @FirstName, @Phone, @Email);";
        dbCmd = new OleDbCommand(sql, dbConn);

        // Execute query
        dbCmd.ExecuteNonQuery();
    }
    catch (System.Exception exc)
    {
        MessageBox.Show(exc.Message);
        return;
    }
}

When i try to use the add button it says "no value given for one or more parameters.

is this something within my .cs or .mdb file? or can i change something in this part of the code?

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Jun 4, 2014 at 3:01

jmcilhinney · Accepted Answer · 2014-06-04 03:02:05Z

2

You have correctly used parameters in your SQL code but you haven't then added those parameters to your command, e.g.

dbCmd.Parameters.AddWithValue("@LastName", lastNameTextBox.Text);

You must add a parameter to the command for each place-holder that appears in your SQL code.

answered Jun 4, 2014 at 3:02

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

jmcilhinney Over a year ago

You might like to read this detailed explanation of mine: jmcilhinney.blogspot.com.au/2009/08/…

Satwik Nadkarny Over a year ago

This is not really advisable. See stackoverflow.com/questions/9155004/… and stackoverflow.com/questions/21110001/… to understand why. There are plenty of other articles (search on google) as well which suggest not to use this due to the implicit conversion of the value method (it sends nvarchar value to the database).

jmcilhinney Over a year ago

@SatwikNadkarny, I disagree. You should not be relying on your parameter types to validate your data. If there are restrictions on the type and/or size of the data then they should have already been applied before you get to this point. If you try to use data of the wrong type or size then you're going to get an exception either way, just a different type of exception.

Satwik Nadkarny Over a year ago

@jmcilhinney You are not seeing the point. The point is not to 'validate' the data. Validation should happen way before you try to send the data to the database. I hope you'll agree to this. The point is the implicit conversion of the value to nvarchar. Certainly your database design will not consist of all columns of all tables in nvarchar format.

jmcilhinney Over a year ago

@SatwikNadkarny, there is no "implicit conversion". The data is sent as the type of value you provide. Of course it will be sent as nvarchar if the value you pass is a String. That's why you don't pass a String as the value if that's not what you want to store in the database. That would be stupid. If, for example, you want to store a number in an int column then you explicitly convert your data to an int if it isn't an int already. AddWithValue infers the type of the parameter from the value so, if the value you provide is an int, the parameter type inferred is Int32.

|

Satwik Nadkarny · Accepted Answer · 2014-06-04 03:33:50Z

1

private void btnAdd_Click(object sender, EventArgs e)
{
    try
    {
        string memberID = txtMember.Text.Trim();
        string firstName = txtFirstName.Text.Trim();
        string lastName = txtLastName.Text.Trim();
        string phone = txtTelephone.Text.Trim();
        string email = txtEmail.Text.Trim();

        sql = "INSERT INTO A_Member ( MemberID, LastName, FirstName, Phone, Email) VALUES ( @Member, @LastName, @FirstName, @Phone, @Email);";
        dbCmd = new OleDbCommand(sql, dbConn);
        dbCmd.Parameters.Add("@MemberID",SqlDbType.Int32).Value = Convert.ToInt32(memberID);
        dbCmd.Parameters.Add("@LastName",SqlDbType.Varchar,30).Value = lastName;
        dbCmd.Parameters.Add("@FirstName",SqlDbType.Varchar,30).Value = firstName;
        dbCmd.Parameters.Add("@Phone",SqlDbType.Int32).Value = Convert.ToInt32(phone);
        dbCmd.Parameters.Add("@LastName",SqlDbType.Varchar,30).Value = email;

        // Execute query
        dbCmd.ExecuteNonQuery();
    }
    catch (System.Exception exc)
    {
        MessageBox.Show(exc.Message);
        return;
    }
}

edited Jun 4, 2014 at 3:33

answered Jun 4, 2014 at 3:12

Satwik Nadkarny

5,1432 gold badges26 silver badges41 bronze badges

3 Comments

jmcilhinney Over a year ago

If they're only being used once, it's probably better to use AddWithValue than Add unless you need to specify a type for the parameter that's different to what would otherwise be inferred from the value.

Satwik Nadkarny Over a year ago

Its not a question of 'once'. AddWithValue does implicit conversion of the value (it sends nvarchar value to the database). You would always be better off explicitly specifying the type of the parameter.

Satwik Nadkarny Over a year ago

@jmcilhinney Read my comment as well as the links I provided on your answer to better understand why.

Collectives™ on Stack Overflow

adding value into sql using textboxes

2 Answers 2

13 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related