DanBAtkinson
asked on
Problem with SQL in ASP.NET (IError)
Hey there,
I'm writing a quick webpage which allows the user to run a number of queries. It relies on the querystring which designates an appropriate SQL string. 3 out of these 5 queries work. Two of them do not. Now, because I've got unlimited points, I'm going to open up a question for the other one. I've searched on the net for this problem but the error itself is really unusual and doesn't really help me much.
Here is the SQL string which I have used to create the query.
Else if ((Request.QueryString("del
SQLstring = "chair"
SQL = "SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session, tblRegister.Role FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID WHERE (((tblRegister.Role)=3))"
Along with the db connection stuff...
Dim dbconn As OleDbConnection = New OleDbConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader() <------- This line generates an error (IErrorInfo.GetDescription
Binding the datasource here:
Else If (SQLstring = "chair") Then
chair.DataSource = reader
chair.DataBind()
And finally, the output:
<% Else If ((Request.QueryString("del
<form runat="server">
<asp:DataList id="chair" runat="server" width="80%">
<ItemTemplate>
<center><h3><%#Container.D
<b>Affiliation:</b><%#Cont
<b>Session:</b><%#Containe
<b>Date:</b><%#Container.D
<b>Session Name:</b><%#Container.Data
<b>Role:</b><%#Container.D
<br><br><br>
</ItemTemplate>
</asp:DataList>
</form>
As said before, this code generates the following error:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
With this line:
reader = Cmd.ExecuteReader()
Anyone got any ideas on how to stop this? I've looked for help on the error but to no avail.
Thanks in advance.
I'm writing a quick webpage which allows the user to run a number of queries. It relies on the querystring which designates an appropriate SQL string. 3 out of these 5 queries work. Two of them do not. Now, because I've got unlimited points, I'm going to open up a question for the other one. I've searched on the net for this problem but the error itself is really unusual and doesn't really help me much.
Here is the SQL string which I have used to create the query.
Else if ((Request.QueryString("del
SQLstring = "chair"
SQL = "SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session, tblRegister.Role FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID WHERE (((tblRegister.Role)=3))"
Along with the db connection stuff...
Dim dbconn As OleDbConnection = New OleDbConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader() <------- This line generates an error (IErrorInfo.GetDescription
Binding the datasource here:
Else If (SQLstring = "chair") Then
chair.DataSource = reader
chair.DataBind()
And finally, the output:
<% Else If ((Request.QueryString("del
<form runat="server">
<asp:DataList id="chair" runat="server" width="80%">
<ItemTemplate>
<center><h3><%#Container.D
<b>Affiliation:</b><%#Cont
<b>Session:</b><%#Containe
<b>Date:</b><%#Container.D
<b>Session Name:</b><%#Container.Data
<b>Role:</b><%#Container.D
<br><br><br>
</ItemTemplate>
</asp:DataList>
</form>
As said before, this code generates the following error:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
With this line:
reader = Cmd.ExecuteReader()
Anyone got any ideas on how to stop this? I've looked for help on the error but to no avail.
Thanks in advance.
ASKER
I don't have IIS. All I've been doing is writing the code in Notepad and uploading it to my host.
then you'd need to check with your host to ensure that you have sufficient permissions to open the Access database, Some hosts do stipulate where the mdb file must be (in a different folder)...
ASKER
My host (Brinkster) requests that I place the file in a databases folder above the webroot.
On this ocassion I haven't done this as I wanted to quickly test the page.
Once I move it to there, will I have to change any permissions?
On this ocassion I haven't done this as I wanted to quickly test the page.
Once I move it to there, will I have to change any permissions?
ah with Brinkster =) They would have configured it for you already, so just move your db to where they requested.
ASKER
Ok. I've tried that and the db is inside the databases directory but I get the same error as before. The filepath is referenced correctly. I've even confirmed it with Brinkster. But the error remains.
At their request, I changed the db connection string to:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
The error is still:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
At their request, I changed the db connection string to:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
The error is still:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
you need to provide the full physical path of the file:
like c:\MyData\data.mdb.
like c:\MyData\data.mdb.
ASKER
So that means that I will have to ask them for the full filepath to my database?
exactly so.
using UNC paths is fraught with problems.
using UNC paths is fraught with problems.
The location of your mdb file should be on the same server as your web server, one would hope.
ASKER
It is on the same server yes. I've put this request to Brinkster in a LiveChat. I'll see what their response is before responding again.
ASKER
At the moment, they're telling me that the error is a coding issue.
if you do a cursory check on the error: 0x80004005 this will tell you many times over, it's a permissions issue.
ASKER
They reset the permissions for the directory (which changed nothing).
They simply keep telling me to use my absolute path using the following code:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
They simply keep telling me to use my absolute path using the following code:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
that's not an absolute path.
that's a UNC path as in \\<server_name>\<share>\<.
Ask them for a physical path as in "C:\data\data.mdb"
what's so hard for them to provide this to you??? or if they say it is a code problem, then get them to write an ASP.NET page with the path they themselves have provided.
Can't have your cake and eat it! To them I say!!
that's a UNC path as in \\<server_name>\<share>\<.
Ask them for a physical path as in "C:\data\data.mdb"
what's so hard for them to provide this to you??? or if they say it is a code problem, then get them to write an ASP.NET page with the path they themselves have provided.
Can't have your cake and eat it! To them I say!!
ASKER
I did so. They refused to give me the physical path. Simply saying:
You must use the absolute path in your website settings.
Is there another way around this?
You must use the absolute path in your website settings.
Is there another way around this?
well you can try this, but it depends on whether the file is really on your web server or they are fibbing you (I am very suspicious that they cannot provide an absolute physical file path)
Dim rawPath As String = Server.MapPath(Request.App
Dim targetPath As String = rawPath.Substring(0,rawPat
It goes one folder up and then to the databases folder.
Dim rawPath As String = Server.MapPath(Request.App
Dim targetPath As String = rawPath.Substring(0,rawPat
It goes one folder up and then to the databases folder.
typo
I am very suspicious as [sic] they cannot provide an absolute physical file path
I am very suspicious as [sic] they cannot provide an absolute physical file path
ASKER
How would I impliment this into the db connection?
This is currently:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
Would be:
Dim rawPath As String = Server.MapPath(Request.App
Dim targetPath As String = rawPath.Substring(0,rawPat
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
?
This is currently:
Dim filePath As String = "\\premfs***\sites\premium
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
Would be:
Dim rawPath As String = Server.MapPath(Request.App
Dim targetPath As String = rawPath.Substring(0,rawPat
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
?
yep, but you want to see what the targetPath is first like output it in a simple test.aspx file.
also,
Dim targetPath As String = rawPath.Substring(0,rawPat
also,
Dim targetPath As String = rawPath.Substring(0,rawPat
ASKER
Dim rawPath As String = Server.MapPath(Request.App
Dim targetPath As String = rawPath.Substring(0,rawPat
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
This changes the directory to the webroot.
Dim targetPath As String = rawPath.Substring(0,rawPat
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
This changes the directory to the webroot.
then they are using some fuzzy URL redirection.
All I can say is that it's permissions if you try their path and it doesnt pan out.
All I can say is that it's permissions if you try their path and it doesnt pan out.
ASKER
Ok thankyou. Is there anything I can do to change the query then? Since I can access the db ok with every other query from the database.
you can??
Try this for size:
SELECT
tblDelegate.DelegateName,
tblDelegate.DelegateAffili
tblSession.SessionID,
tblSession.SessionDate,
tblSession.Session,
tblRegister.Role
FROM
tblSession
INNER JOIN tblRegister ON tblRegister.SessionID = tblSession.SessionID
INNER JOIN tblDelegate ON tblDelegate.DelegateID = tblRegister.DelegateID
WHERE
tblRegister.Role = 3
SELECT
tblDelegate.DelegateName,
tblDelegate.DelegateAffili
tblSession.SessionID,
tblSession.SessionDate,
tblSession.Session,
tblRegister.Role
FROM
tblSession
INNER JOIN tblRegister ON tblRegister.SessionID = tblSession.SessionID
INNER JOIN tblDelegate ON tblDelegate.DelegateID = tblRegister.DelegateID
WHERE
tblRegister.Role = 3
ASKER
No. It gives exactly the same error. It's just strange that nearly every other query from the same db works but this one. Is it due to the inner join?
well it's a db issue, do you have the relationships correct between the 3 tables
ASKER
Well the queries appear ok in Access.
ASKER
Yes. I've gone over the relationships in Access. The query shows 7 results.
ASKER
Is it perhaps something to do with the reader?:
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
If the problem is with permissions, does the db need to write anything and if so, is it anything to do with those two lines?
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()
If the problem is with permissions, does the db need to write anything and if so, is it anything to do with those two lines?
I have read about similar problems when reserved words for access are used within a query .. these queries work when executed with access but fails through ODBC ... in ur query, i think the field "Role" is a reserved word .. try changing it or putting it within square brackets .. []
SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session, tblRegister.[Role] FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID WHERE (((tblRegister.[Role])=3))
just to be sure about this ie. whether the problem is due to reserved words, just modify the query to so as not use the field "Role" something like this
SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID
check if this works and returns all the rows ..
SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session, tblRegister.[Role] FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID WHERE (((tblRegister.[Role])=3))
just to be sure about this ie. whether the problem is due to reserved words, just modify the query to so as not use the field "Role" something like this
SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffili
tblSession.SessionDate, tblSession.Session FROM tblSession INNER JOIN (tblDelegate INNER JOIN tblRegister ON tblDelegate.DelegateID = tblRegister.DelegateID) ON tblSession.SessionID = tblRegister.SessionID
check if this works and returns all the rows ..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok .. i was able to find some reference which supports my initial assumption by searching the net ... googling ....
for
IErrorInfo.GetDescription reserved word
have a look at some of the links i got ..
http://www.devcity.net/Articles/85/1/ssteps2_addressbook.aspx
look at the "Comment #7 (Posted by Stan Loach ) "
https://www.experts-exchange.com/questions/21267891/IErrorInfo-GetDescription-failed-with-E-FAIL-0x80004005-what-does-this-mean.html
have a look at the links provided in the accepted answer ...
http://www.dotnet247.com/247reference/msgs/43/218952.aspx
http://www.mcse.ms/archive107-2004-1-286926.html
for
IErrorInfo.GetDescription reserved word
have a look at some of the links i got ..
http://www.devcity.net/Articles/85/1/ssteps2_addressbook.aspx
look at the "Comment #7 (Posted by Stan Loach ) "
https://www.experts-exchange.com/questions/21267891/IErrorInfo-GetDescription-failed-with-E-FAIL-0x80004005-what-does-this-mean.html
have a look at the links provided in the accepted answer ...
http://www.dotnet247.com/247reference/msgs/43/218952.aspx
http://www.mcse.ms/archive107-2004-1-286926.html
ASKER
After changing the session field to sessionName, it still does not work!
Thanks for the suggestion though. I thought that this could be on the right track!
Thanks for the suggestion though. I thought that this could be on the right track!
ASKER
lol. For some reason, now it works!
I put session in brackets and it didn't work, I called it:
[tblSession]![Session] AS SessionName
and it appeared to work! How odd!
ReJohhny is the winner of the points.
b1xml2, thanks for your help but in the end, it was the reserved word!
I put session in brackets and it didn't work, I called it:
[tblSession]![Session] AS SessionName
and it appeared to work! How odd!
ReJohhny is the winner of the points.
b1xml2, thanks for your help but in the end, it was the reserved word!
ASKER
https://www.experts-exchange.com/questions/21416628/SQL-like-query-problem.html
Is the second and final question of the problem I've had with the db if anyone is interested?!
Is the second and final question of the problem I've had with the db if anyone is interested?!
grant permissions to IUSR_<machine_name> to the database. You;d need read write, but you might as well grant Full control.
This applies if you only set anonymouse access to your web application.
If users are authenticated, then you'd need to set relevant permissions for the accounts to the database. This applies when Windows authentication is set in IIS, and impersonation is on.