locked
Problem of sql excel odbc query lauching RRS feed

  • Question

  • User-458598543 posted

    Hello,

    I've got an error's message following the launching of query. The error's message is :

    Détails de l'exception: System.Data.Odbc.OdbcException: ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 3.

    The code is :

    protected void ValiderRegion(object sender, EventArgs e)
    {
    string StringDDL_Region_Selected = DDL_Region.SelectedValue;
    if (StringDDL_Region_Selected != null)
    {
    SqlDataSource3.SelectParameters.Clear();
    SqlDataSource3.SelectCommand = "SELECT T_JP_Localisation_NumDptEtDpt FROM [t_jp_localisation$] WHERE T_JP_Localisation_Region = ?RegionJ group by T_JP_Localisation_NumDptEtDpt";
    SqlDataSource3.SelectParameters.Add("?RegionJ", DDL_Region.SelectedValue);
    DDL_Departement.DataSourceID = "SqlDataSource3";
    DDL_Departement.DataValueField = "T_JP_Localisation_NumDptEtDpt";
    DDL_Departement.Visible = true;
    Bt_Departement.Visible = true;
    SqlDataSource3.Dispose();
    }

    Thanks for your help !

    Monday, February 5, 2018 5:16 PM

Answers

  • User-458598543 posted

    Hello,

    I've found the solution to erase the error's "too few parameters". I place the character "?' in the query and I place a '@' in the parameters.

    The new code is :

    T_JP_Localisation_Region = ? group by T_JP_Localisation_NumDptEtDpt";

    SqlDataSource3.SelectParameters.Add("@RegionJ", DDL_Region.SelectedValue);

    Thanks for your contribution, bye !

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 8, 2018 8:34 AM

All replies

  • User475983607 posted

    This error message commonly indicates a parameter is missing or empty.  The error message is expecting 3 parameters but the source code shown only has one parameter.  Are you sure you are showing us the code that goes with the error?

    Have you verified DDL_Region.SelectedValue has a value and is not an empty string?  Have you tried using the Visual Studio debugger to step through the code to look for logical errors?

    Monday, February 5, 2018 7:28 PM
  • User-458598543 posted

    Hello,

    Thanks for your answer, following your directives, I can certificate that the code that I'm showing to you is the real code. The DDL_Region.SelectedValue object has got a value because StringDDL_Region_Selected which equals to DDL_Region.SelectedValue is not null. Otherwhise, the debugger returns the same error's message.

    Perhaps, it's an error of parameter's form :

    SqlDataSource3.SelectCommand = "SELECT T_JP_Localisation_NumDptEtDpt FROM [t_jp_localisation$] WHERE T_JP_Localisation_Region = ?RegionJ group by T_JP_Localisation_NumDptEtDpt";                          // ?RegionJ
    SqlDataSource3.SelectParameters.Add("?RegionJ", DDL_Region.SelectedValue);               // ?RegionJ

    You have probably the solution ?

    Monday, February 5, 2018 9:55 PM
  • User475983607 posted

    Thanks for your answer, following your directives, I can certificate that the code that I'm showing to you is the real code. The DDL_Region.SelectedValue object has got a value because StringDDL_Region_Selected which equals to DDL_Region.SelectedValue is not null. Otherwhise, the debugger returns the same error's message.

    What is the value of the drop down?

    Why do you think the SqlDataSource SQL expects 3 parameters?  Are you using the Visual Studio debugger to verify the state of local variables?

    Keep in mind we can only see the code and errors you post.  

    Monday, February 5, 2018 10:34 PM
  • User269602965 posted

    T_JP_Localisation_Region = ?RegionJ group by T_JP_Localisation_NumDptEtDpt";
    SqlDataSource3.SelectParameters.Add("?RegionJ", DDL_Region.SelectedValue);

    Try

    T_JP_Localisation_Region = ?RegionJ group by T_JP_Localisation_NumDptEtDpt";
    SqlDataSource3.SelectParameters.Add("RegionJ", DDL_Region.SelectedValue);

    remove the bind variable indicator from the parameter name.

    Tuesday, February 6, 2018 12:22 AM
  • User-458598543 posted

    Expected 3 parameters probably came from all the values behind the ? are recognised as 3 independant parameters (= ?RegionJ (1) group by (2) T_JP_Localisation_NumDptEtDpt (3)). The debugger doesn't help me, the local variable window debbuger shows nothing.

    I'm trying to escape the ? in SelectParameters, I've "hélas !" the same error's message.

    Tuesday, February 6, 2018 12:46 AM
  • User269602965 posted

    If you are trying to query an Excel spreadsheet, consider using OLEDB instead of the deprecated ODBC.

    Thursday, February 8, 2018 1:47 AM
  • User-458598543 posted

    Hello,

    I've found the solution to erase the error's "too few parameters". I place the character "?' in the query and I place a '@' in the parameters.

    The new code is :

    T_JP_Localisation_Region = ? group by T_JP_Localisation_NumDptEtDpt";

    SqlDataSource3.SelectParameters.Add("@RegionJ", DDL_Region.SelectedValue);

    Thanks for your contribution, bye !

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 8, 2018 8:34 AM
  • User269602965 posted

    Which defies explanation.  other than it just being treated as substitution, not classic bind variable.

    So it worked without error, but does the Region.SelectedValue get passed?

    Thursday, February 8, 2018 2:14 PM