locked
Problem of finding the path of database table by odbc RRS feed

  • Question

  • User-458598543 posted

    Hello, how do you do ?

    I contact you because I've got a problem of using database by a web site. I use excel tables and ODBC driver.

    The error's message is : Exception Details: System.Data.Odbc.OdbcException: ERROR [42S02] [Microsoft][Pilote ODBC Excel] Le moteur de base de données Microsoft Jet n'a pas pu trouver l'objet 't_jp_localisation'. Assurez-vous que l'objet existe et que vous avez correctement saisi son nom et son chemin d'accès.

    Well, I'm certain that the ODBC parameters are good because the connection to the database is valid with the controls on a .aspx page but the sql controls and the connection to the tables as t_jp_localisation are not valid on the behind code pages .aspx.cs.

    The sample code is :

    • Web.Config :

          <add name="CS_DB_Jobersplace2_20180120" connectionString="Driver={Microsoft Excel Driver (*.xls)};dbq=G:\DataBase_Yomabeh_sql\Data_xls_20180122\DB_Jobersplace_20161221.xls;defaultdir=G:\DataBase_Yomabeh_sql\Data_xls_20180122;driverid=790;fil=excel 8.0;filedsn=C:\Users\yonga marc-antoine\Documents\Driver_Jobersplace2_20171230_D.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;readonly=0;safetransactions=0;threads=3;usercommitsync=Yes" providerName="System.Data.Odbc" />

    • .aspx page :

     <asp:SqlDataSource ID="SqlDataSource1"
                        ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>"
                        ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>"
                        SelectCommand="SELECT T_JP_Localisation_Region FROM [t_jp_localisation$] group by T_JP_Localisation_Region"
                        runat="server"></asp:SqlDataSource>         // the connection and the query are valids. 

    <asp:SqlDataSource ID="SqlDataSource3" ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>" ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>" runat="server"></asp:SqlDataSource>

    • .cs page :

     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();
                }         //  the control and the query are not valids.

    The site was developped on a computer with visual studio 2015 and the same site is installed and deployed on other computer on an usb external disk with the letter G:.

    So, thanks you for cooperation !

    Bye ...

    Tuesday, January 23, 2018 11:12 AM

Answers

  • User-458598543 posted

    Hi,

    Trying a solution, I change the object sqldatasource name SqlDataSource3 to SqlDataSource6 and the odbc error message disapeared. Meanwhile, I've got an other error's message as :

    Détails de l'exception: System.NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.

    [NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.]
    Jobersplace.WF3_CreationCptAbonne.ValiderRegion(Object sender, EventArgs e) in C:\Users\Windows7\Documents\Visual Studio 2015\Projects\JobersPlace\JobersPlace\WF3_CreationCptAbonne.aspx.cs:166
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9767618
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +204
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9997462
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1738

    The code is :

    • aspx page

    <asp:SqlDataSource ID="SqlDataSource1"
    ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>"
    ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>"
    SelectCommand="SELECT T_JP_Localisation_Region FROM [t_jp_localisation$] group by T_JP_Localisation_Region"
    runat="server"></asp:SqlDataSource>


    <asp:SqlDataSource ID="SqlDataSource6" ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>" ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>" runat="server"></asp:SqlDataSource>

    • cs code behind page

    protected void Page_Load(object sender, EventArgs e)
    {

    Bt_Region.Click += new EventHandler(ValiderRegion);

    }

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

    }

    Thanks for your answer !

    Rednuts72

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 12:24 PM

All replies

  • User753101303 posted

    Hi,

    And you are 100% sure you have a worksheet named 't_jp_localisation ? G: is a USB drive on the web server ? Keep in mind this is C# code that runs on a server  and it won't access to a client side G: drive.

    Also you have a DSN that points to a user profile ? And still the connection information is in your connection string ? Could it be that you have a conflicting information inside the filedsn and maybe it takes precedence ?

    Tuesday, January 23, 2018 11:53 AM
  • User-458598543 posted

    Hi,

    Well the web page and the database are stocked in a usb external disk. I choose this external disk because if I'm not in the error the usb bus is more faster than ide bus.

    Do you know the process to initialize and open the external disk as server ?

    Thanks you !

    Tuesday, January 23, 2018 12:45 PM
  • User753101303 posted

    Where is this USB drive? You'll never be able to access to this disk if it is not plugged directly on the web server. If you want to process a client side Excel file, you would have to upload the Excel file to the web server and then  process this file.

    I'm not an harware guy but I doubt that for now IDE vs USB speed should be a real concern and the best thing you could do to optimize your site.

    Try maybe to rename the Excel file. Do you still have the same error ? It might indicate that for now you are not processing the file you think (and that this file may perhaps not contain the expected woorksheet).

    Tuesday, January 23, 2018 1:03 PM
  • User-1716253493 posted

    Commonly Internal faster then external.

    IDE is about 133 mbps sata 1500-6000 mbps, usb 1 is about 1.5 mbps, usb 2 about 60 mbps

    Tuesday, January 23, 2018 1:12 PM
  • User-458598543 posted

    Well, I've got a real question, the connection and the query is valid on .aspx page but the connection and the query is not valid on the .cs page. Do you know why ?

    :)

    Tuesday, January 23, 2018 1:49 PM
  • User-458598543 posted

    Hello oned_gk,

    Thanks for your answer but this answer is going to joint a debat on why has been build serveral bus with différents speeds otherwhise the technology is based on copper-brass (cuivre) ? The speed of the bus depends too on the manufacturers strategies. For my computer, With reflexion, I think that the speed of the usb bus (v 2.0)  is less faster than IDE BUS bus but in the future I can change of computer with usb 3.1 associated to more than 10 Gbps. 

    Actually, I would choose usb external disk for storage the web site because the ide disk is used for others services.

    Truly yours,

    Tuesday, January 23, 2018 2:10 PM
  • User-458598543 posted

    Sir oned_gk,

    quizz the usb bus v 2.0 has got with faith a theoric speed of 480 Mbps and a ... speed between 30 to 60 Mbps.

    Rednuts72

    Tuesday, January 23, 2018 2:35 PM
  • User753101303 posted

    You don't select the same field ? Could it be that the Excel driver just returns some generic error whenever an error happens ???

    Start maybe with the simplest query you can. Does it work? Not sure if supported by Excel but I would try perhaps https://stackoverflow.com/questions/8695349/odbcconnection-getschematables-not-working

    It might exposes all sheets and could allow to make sure whether or now you have a sheet named this way.

    Edit: ah not sure but if I remember when a column name is missing or is used twice, you may perhaps have a sheet named this way but still the ODBC driver could be unable to expose this sheet properly. I would double check the workbook and would perhaps add a dead simple new sheet. At least if it works, I would then be 100% sure I'm trying to read the correct file.

    Tuesday, January 23, 2018 3:14 PM
  • User-458598543 posted

    Hi,

    Severals solutions stand to me, I can create a file for each table with his connection or I can try to recopy the script in a new page. I'm trying the best. I will put a new message on the forum when I will finish the operation.

    Tuesday, January 23, 2018 5:09 PM
  • User-458598543 posted

    Hello,

    I'm trying several solutions to set the table t_jp_localisation and this table is not recognised and opened. After reflexion and testings, I saw that the error's message of the connection to the table is not on the code behind .cs page because when I change the parameters of the query as the name of the table, I've got the same error message : 

    Détails de l'exception: System.Data.Odbc.OdbcException: ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 't_jp_localisation'.  Make sure the object exists and that you spell its name and the path name correctly.

    So is it a compilation error or do you know if there is a virtual or temporary connection ?

    Thanks you !

    Tuesday, January 30, 2018 3:10 AM
  • User-458598543 posted

    Hi,

    Trying a solution, I change the object sqldatasource name SqlDataSource3 to SqlDataSource6 and the odbc error message disapeared. Meanwhile, I've got an other error's message as :

    Détails de l'exception: System.NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.

    [NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.]
    Jobersplace.WF3_CreationCptAbonne.ValiderRegion(Object sender, EventArgs e) in C:\Users\Windows7\Documents\Visual Studio 2015\Projects\JobersPlace\JobersPlace\WF3_CreationCptAbonne.aspx.cs:166
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9767618
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +204
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9997462
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1738

    The code is :

    • aspx page

    <asp:SqlDataSource ID="SqlDataSource1"
    ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>"
    ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>"
    SelectCommand="SELECT T_JP_Localisation_Region FROM [t_jp_localisation$] group by T_JP_Localisation_Region"
    runat="server"></asp:SqlDataSource>


    <asp:SqlDataSource ID="SqlDataSource6" ConnectionString="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120 %>" ProviderName="<%$ ConnectionStrings:CS_DB_Jobersplace2_20180120.ProviderName %>" runat="server"></asp:SqlDataSource>

    • cs code behind page

    protected void Page_Load(object sender, EventArgs e)
    {

    Bt_Region.Click += new EventHandler(ValiderRegion);

    }

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

    }

    Thanks for your answer !

    Rednuts72

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 30, 2018 12:24 PM
  • User-1716253493 posted

    Try like this

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="...">
                <SelectParameters>
                    <asp:Parameter Name="param1" DefaultValue="0" />
                </SelectParameters>
            </asp:SqlDataSource>

    cs

    SqlDataSource1.SelectParameters["param1"].DefaultValue = TextBox1.Text;

    or

    SqlDataSource1.SelectParameters[0].DefaultValue = TextBox1.Text;

    Define the parameters in aspx page, set defaultvalues from cs code

    Wednesday, January 31, 2018 3:40 AM
  • User-458598543 posted

    Hello,

    Thanks for your answer, Following your thinking, I delete the selectparameters to see the query comportment and I've got the same error's message :

    Détails de l'exception: System.NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.

    So I can say that the Null reference is not associated to the selectparameters.

    After research, I see that the click's button event is charged on an ancient folder of the solution which I cancelled :

    Jobersplace.WF3_CreationCptAbonne.ValiderRegion(Object sender, EventArgs e) in C:\Users\Windows7\Documents\Visual Studio 2015\Projects\JobersPlace\JobersPlace\WF3_CreationCptAbonne.aspx.cs:166

    The real folder of the solution or the project is placed in an other folder of the path which indicated above so the problem is probably a conflict between the real folder and the ancient folder of the solution.

    You probably knows the solution of this problem.

    Rednuts72

    Wednesday, January 31, 2018 9:07 AM
  • User-458598543 posted

    Hello,

    Following my tasks to resolve the odbc error, I prefer closing this forum thread because the odbc error seems resolved. I will open an other forum thread with a new asking about problems of loading the project, cs page or functions controls after building.

    Thanks you !

    Thursday, February 1, 2018 8:37 AM