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?](http://meta.stackoverflow.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Jun 04 '14 at 03:01

score 2 · Answer 1 · answered Jun 04 '14 at 03:02

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 04 '14 at 03:02

jmcilhinney

50,448
5
26
46

You might like to read this detailed explanation of mine: http://jmcilhinney.blogspot.com.au/2009/08/using-parameters-in-adonet.html – jmcilhinney Jun 04 '14 at 03:04
This is not really advisable. See http://stackoverflow.com/questions/9155004/difference-with-parameters-add-and-parameters-addwithvalue and http://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue 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). – Satwik Nadkarny Jun 04 '14 at 03:14
@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. – jmcilhinney Jun 04 '14 at 03:18
@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. – Satwik Nadkarny Jun 04 '14 at 03:24
@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`. – jmcilhinney Jun 04 '14 at 03:29
@jmcilhinney As I said before, this is not a personal opinion but something agreed upon by a lot of other developers. You can see that in the links I have posted.You may choose to disagree but certainly a lot of developers in the community advise on not using this. – Satwik Nadkarny Jun 04 '14 at 03:30
@SatwikNadkarny, if those developers are advising that for the same reason you seem to be then they are wrong. Certainly the link you provided earlier does so and is therefore wrong. AddWithValue sends the data as the type you provide, plain and simple. If you don;t want the data sent as `nvarchar` then don't provide a `String`. It's that simple. – jmcilhinney Jun 04 '14 at 03:32
@SatwikNadkarny, I think that I may have misunderstood slightly what you were saying but I also think that you are under a misconception. `AddWithValue` will infer a `SqlDbType` of `NVarChar` for any `String`, but it's not going to for other types. If you pass an `int` then it infers `Int` and if you pass a `double` it infers `float`. That's why `dbCmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = Convert.ToInt32(memberID);` is pointless when `dbCmd.Parameters.Add("@MemberID", Convert.ToInt32(memberID));` produces exactly the same result. – jmcilhinney Jun 04 '14 at 03:42
@jmcilhinney FYI, Microsoft themselves suggest not to use AddWithValue. Don't believe me?Read this http://msdn.microsoft.com/en-us/magazine/ee236412.aspx. Read the line which says "When you're using parameterized queries, it's a bad idea to use Parameters.AddWithValue.". – Satwik Nadkarny Jun 04 '14 at 03:44
Read the ensuing paragraph to understand the reason. Also, read the line "Another good habit to get into for string-based data types is to always specify the length of the parameter. By not specifying the length when you're using ParameterCollection.AddWithValue, you can have as many different queries in the plan cache as you have different string lengths. Now that's plan cache pollution in a big way". – Satwik Nadkarny Jun 04 '14 at 03:45
@jmcilhinney Certainly, it can't be wrong if Microsoft themselves are stating it. I hope you get the point. By no means do I wish to argue. If you still wish to disagree, well, then that's your prerogative :) – Satwik Nadkarny Jun 04 '14 at 03:45
@jmcilhinney They do produce the same result. I used the word 'advisable' and not wrong. Nowhere did I say that your code doesn't work. Its just that its not advisable i.e. its not a best practice. – Satwik Nadkarny Jun 04 '14 at 03:48
So the issue really seems to be around strings specifically and, I have to admit, I did learn a few things from one of your links. – jmcilhinney Jun 04 '14 at 04:08

Satwik Nadkarny · Answer 2 · 2014-06-04T03:33:50.590

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;
    }
}

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. — jmcilhinney, Jun 04 '14 at 03:13
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, Jun 04 '14 at 03:19
@jmcilhinney Read my comment as well as the links I provided on your answer to better understand why. — Satwik Nadkarny, Jun 04 '14 at 03:20

adding value into sql using textboxes

2 Answers2