[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3843
  • Last Modified:

using parameters in Access insert query

I have the following code that isn't working right.  Its inserting the text @code instead of the value of codevalue.text.tostring()
OdbcConnection con = new OdbcConnection("DSN=ShipData");
            string sqlQuery = "INSERT INTO Address ([Code],[Company],[Name],[Phone],[Address],[Address2],[City],[State],[ZIP],[Country],[E-mail],[Fdx_accnt]) VALUES"
                            + " ('@code','@company','@contact','@phone','@addy','@addy2','@city','@state','@zip','@country','@email','@fdx_accnt')";
            OdbcCommand dbcommand = new OdbcCommand(sqlQuery, con);
            dbcommand.Parameters.Add("@code", OdbcType.VarChar,40).Value = codevalue.Text.ToString();
            dbcommand.Parameters.Add("@company", OdbcType.VarChar,40).Value = compvalue.Text.ToString();
            dbcommand.Parameters.Add("@contact", OdbcType.VarChar,40).Value = namevalue.Text.ToString();
            dbcommand.Parameters.Add("@phone", OdbcType.VarChar,40).Value = phonevalue.Text.ToString();
            dbcommand.Parameters.Add("@addy", OdbcType.VarChar,40).Value = addy1value.Text.ToString();
            dbcommand.Parameters.Add("@addy2", OdbcType.VarChar,40).Value = addy2value.Text.ToString();
            dbcommand.Parameters.Add("@city", OdbcType.VarChar,40).Value = cityvalue.Text.ToString();
            dbcommand.Parameters.Add("@state", OdbcType.VarChar,40).Value = statesdropdown.Text.ToString();
            dbcommand.Parameters.Add("@zip", OdbcType.VarChar,40).Value = zipvalue.Text.ToString();
            dbcommand.Parameters.Add("@country", OdbcType.VarChar,40).Value = countryvalue.Text.ToString();
            dbcommand.Parameters.Add("@email", OdbcType.VarChar,40).Value = emailvalue.Text.ToString();
            dbcommand.Parameters.Add("@fdx_accnt", OdbcType.VarChar,9).Value = fdx_accntvalue.Text.ToString();
            con.Open();
            try
            {
                int count = dbcommand.ExecuteNonQuery();
            }
            catch (OdbcException ex)
            {
                MessageBox.Show(ex.Message);
            }

Open in new window

0
casit
Asked:
casit
  • 2
  • 2
1 Solution
 
drichardsCommented:
I don't think you have the parameter syntax correct in your query.  I believe you do not use quotes around the parameter names.
            string sqlQuery = "INSERT INTO Address ([Code],[Company],[Name],[Phone],[Address],[Address2],[City],[State],[ZIP],[Country],[E-mail],[Fdx_accnt]) VALUES"
         + " (@code,@company,@contact,@phone,@addy,@addy2,@city,@state,@zip,@country,@email,@fdx_accnt)";

Open in new window

0
 
casitAuthor Commented:
Now its telling me 12 parameters are expected.
0
 
drichardsCommented:
So the syntax of the query is correct, and I don't see anything else blatantly incorrect.  I assume you validated that the command parameter collection has 12 items prior to ExecuteNonQuery?
0
 
casitAuthor Commented:
Yeah.  I even debugged it and then looked at the output of the paramters to make sure they had values and they did.  so I just put the variables right into the sql query.  Will have to figure it out later.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now