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.
<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.
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:.
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
?
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 ?
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).
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 ?
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.
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.
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.
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 ?
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>
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.
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.
Member
52 Points
175 Posts
Problem of finding the path of database table by odbc
Jan 23, 2018 11:12 AM|Rednuts72|LINK
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 :
<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" />
<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>
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 ...
All-Star
47890 Points
17756 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 11:53 AM|PatriceSc|LINK
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 ?
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 12:45 PM|Rednuts72|LINK
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 !
All-Star
47890 Points
17756 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 01:03 PM|PatriceSc|LINK
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).
All-Star
52433 Points
15637 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 01:12 PM|oned_gk|LINK
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
Suwandi - Non Graduate Programmer
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 01:49 PM|Rednuts72|LINK
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 ?
:)
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 02:10 PM|Rednuts72|LINK
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,
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 02:35 PM|Rednuts72|LINK
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
All-Star
47890 Points
17756 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 03:14 PM|PatriceSc|LINK
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.
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 23, 2018 05:09 PM|Rednuts72|LINK
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.
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 30, 2018 03:10 AM|Rednuts72|LINK
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 !
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 30, 2018 12:24 PM|Rednuts72|LINK
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 :
<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>
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
All-Star
52433 Points
15637 Posts
Re: Problem of finding the path of database table by odbc
Jan 31, 2018 03:40 AM|oned_gk|LINK
Try like this
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="..."> <SelectParameters> <asp:Parameter Name="param1" DefaultValue="0" /> </SelectParameters> </asp:SqlDataSource>cs
or
Define the parameters in aspx page, set defaultvalues from cs code
Suwandi - Non Graduate Programmer
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Jan 31, 2018 09:07 AM|Rednuts72|LINK
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
Member
52 Points
175 Posts
Re: Problem of finding the path of database table by odbc
Feb 01, 2018 08:37 AM|Rednuts72|LINK
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 !