using parameters in Access insert query

Posted on 2008-11-09
Last Modified: 2013-12-17
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();




                int count = dbcommand.ExecuteNonQuery();


            catch (OdbcException ex)




Open in new window

Question by:casit
    LVL 19

    Accepted Solution

    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


    Author Comment

    Now its telling me 12 parameters are expected.
    LVL 19

    Expert Comment

    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?

    Author Comment

    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.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    In my previous two articles we discussed Binary Serialization ( and XML Serialization ( In this article we will try to know more about SOAP (Simple Object Acces…
    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now