Incorrect syntax when pass parameter

Question

Error display as "Incorrect syntax near '@Cmp_DocPath', if i use comment Line code I got the Error as "An sqlparameter with parametername '@Cmp_DocPath' is not contained by this sqlparametercollection".How i get the Filename of the AsyncFileUpload AJAX control ?

    protected void BtnCmpApproval_Click(object sender, EventArgs e)
    {
      SqlConnection SqlCon = new SqlConnection(GetConnectionString());
        string query = "INSERT INTO User_Info2 VALUES     (@lblCmpUserName,@txtCmpName,
      @txtRegCountry,@txtCmpRegNo,@txtCmpEstdate,@txtCmpAddress,@ddlAddrIn)";
        try
        {
            SqlCon.Open();
            SqlCommand cmd = new SqlCommand(query, SqlCon);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@UserName", lblCmpUserName.Text);
            cmd.Parameters.AddWithValue("@Cmp_Name", txtCmpName.Text);
            cmd.Parameters.AddWithValue("@Commercial_RegNo", txtRegCountry.Text);
            cmd.Parameters.AddWithValue("@Comm_Country", txtCmpRegNo.Text);
            cmd.Parameters.AddWithValue("@Cmp_EstablishDate", txtCmpEstdate.Text);
            //cmd.Parameters.AddWithValue("@Cmp_DocPath", AFU1.FileName);
            cmd.Parameters["@Cmp_DocPath"].Value=AFU1.FileName;
            cmd.Parameters.AddWithValue("@txtCmpAddress", txtCmpAddress.Text);
            cmd.Parameters.AddWithValue("@ddlAddrIn", ddlAddrIn.SelectedItem.Text);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
           throw new Exception(ex.Message);
        }
        finally
        { 
           SqlCon.Close();
        }
    }

Steve · Accepted Answer · 2012-06-14 09:09:51Z

1

You have set these parameters in the Sql Insert String (7 parameters)

@lblCmpUserName,
@txtCmpName,       
@txtRegCountry,
@txtCmpRegNo,
@txtCmpEstdate,
@txtCmpAddress,
@ddlAddrIn

You have added these parameters in the SqlCommand (8 parameters)

@UserName   -> Missing
@Cmp_Name   -> Missing
@Commercial_RegNo ->Missing
@Comm_Country  -> Missing
@Cmp_EstablishDate ->Missing
@Cmp_DocPath  ->Missing
@txtCmpAddress  ->Found it  !!
@ddlAddrIn  ->Found it!!!!

As you can see you miss many more than one. I suppose you are confusing the controls names for the parameters name. You should change the names present in your Insert String to the same names added to your parameter collection.

string query = "INSERT INTO User_Info2 VALUES (@UserName,@Cmp_pName, " + 
               "@Comm_Country, @Commercial_RegNo,@Cmp_EstablishDate," + 
               "@txtCmpAddress,@ddlAddrIn);

Also you add the Parameter Cmp_DocPath", but I can't find it anywhere in your Insert string.

edited Jun 14, 2012 at 9:09

answered Jun 14, 2012 at 8:56

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

Hari Over a year ago

Thanks for all.Now i added the Parameter Cmp_DocPath.Now new Error is display as "Must declare the scalar variable "@lblCmpUserName"".I'm using Visual studio 2010 and sql server 2008

Steve Over a year ago

When you write a SqlCommand like in the code above, it is like you are saying to NET Runtime. "Hey, I'm sending a string to SqlServer with placeholders for the parameters present in the command collection. Please substitute the placeholders with the parameters values you will find in the collection". Now NET Runtime search your string for placeholders, but there is no parameter corrisponding to the placeholder @lblCmpUserName, so it complains with you. (and stop at the first one)

Klaus Byskov Pedersen · Accepted Answer · 2012-06-14 08:54:42Z

0

You are not adding a parameter with the name @Cmp_DocPath, your code simply assumes that it is already there, and it's telling you that it's not. You should add the parameter the same way that you add the other parameters, eg.:

cmd.Parameters.AddWithValue("@Cmp_DocPath", AFU1.FileName);

answered Jun 14, 2012 at 8:54

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

3 Comments

Hari Over a year ago

Thanks for all.Now i added the Parameter Cmp_DocPath.Now new Error is display as "Must declare the scalar variable "@lblCmpUserName"".I'm using Visual studio 2010 and sql server 2008

Klaus Byskov Pedersen Over a year ago

@Hari, yeah. look at the code. In your query you have something called @lblCmpUserName, however, further down you simply name it @UserName. Those names have to match.

Hari Over a year ago

stackoverflow.com/questions/11062491/…

Damith · Accepted Answer · 2012-06-14 09:06:02Z

0

There should be a match between query parameter names and parameter names you give when adding parameter values. check all parameters names, types and count of parameters same as what you give in query.

sample code:

try
{
    string query = "INSERT INTO User_Info2 VALUES (@UserName,@Cmp_Name,@Commercial_RegNo,@Comm_Country,@Cmp_EstablishDate,@Cmp_DocPath, @txtCmpAddress,@ddlAddrIn)";
    using (SqlConnection SqlCon = new SqlConnection(GetConnectionString()))
    {
        SqlCon.Open();
        using (SqlCommand cmd = new SqlCommand(query, SqlCon))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@UserName", lblCmpUserName.Text);
            cmd.Parameters.AddWithValue("@Cmp_Name", txtCmpName.Text);
            cmd.Parameters.AddWithValue("@Commercial_RegNo", txtRegCountry.Text);
            cmd.Parameters.AddWithValue("@Comm_Country", txtCmpRegNo.Text);
            cmd.Parameters.AddWithValue("@Cmp_EstablishDate", txtCmpEstdate.Text);
            cmd.Parameters.AddWithValue("@Cmp_DocPath", AFU1.FileName);
            cmd.Parameters.AddWithValue("@txtCmpAddress", txtCmpAddress.Text);
            cmd.Parameters.AddWithValue("@ddlAddrIn", ddlAddrIn.SelectedItem.Text);
            cmd.ExecuteNonQuery();
        }

    }

}
catch (Exception ex)
{
    throw new Exception(ex.Message);
}

edited Jun 14, 2012 at 9:06

answered Jun 14, 2012 at 8:55

Damith

63.2k13 gold badges106 silver badges154 bronze badges

1 Comment

Hari Over a year ago

Thanks for all.Now i added the Parameter Cmp_DocPath.Now new Error is display as "Must declare the scalar variable "@lblCmpUserName"".I'm using Visual studio 2010 and sql server 2008.

Collectives™ on Stack Overflow

Incorrect syntax when pass parameter

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related