• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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("delegate") = "chair")) Then
          SQLstring = "chair"
          SQL = "SELECT tblDelegate.DelegateName, tblDelegate.DelegateAffiliation, tblSession.SessionID,
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=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("delegates.mdb"))
        dbconn.Open()
        Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
        Dim reader As OleDbDataReader
        reader = Cmd.ExecuteReader()  <------- This line generates an error (IErrorInfo.GetDescription failed with E_FAIL(0x80004005)).


Binding the datasource here:

          Else If (SQLstring = "chair") Then
            chair.DataSource = reader
            chair.DataBind()


And finally, the output:

<% Else If ((Request.QueryString("delegate") = "chair")) %>

    <form runat="server">
      <asp:DataList id="chair" runat="server" width="80%">

        <ItemTemplate>
          <center><h3><%#Container.DataItem("DelegateName")%></h3></center>
          <b>Affiliation:</b><%#Container.DataItem("DelegateAffiliation")%><br>
          <b>Session:</b><%#Container.DataItem("SessionID")%><br>
          <b>Date:</b><%#Container.DataItem("SessionDate")%><br>
          <b>Session Name:</b><%#Container.DataItem("Session")%><br>
          <b>Role:</b><%#Container.DataItem("Role")%>
          <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.
0
DanBAtkinson
Asked:
DanBAtkinson
  • 18
  • 15
  • 3
1 Solution
 
b1xml2Commented:
it's a NTFS permissions issue.

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.


0
 
DanBAtkinsonAuthor Commented:
I don't have IIS. All I've been doing is writing the code in Notepad and uploading it to my host.
0
 
b1xml2Commented:
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)...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DanBAtkinsonAuthor Commented:
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?
0
 
b1xml2Commented:
ah with Brinkster =) They would have configured it for you already, so just move your db to where they requested.

0
 
DanBAtkinsonAuthor Commented:
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***\***********\database\delegates.mdb"
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & filePath & ";")

The error is still:

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
0
 
b1xml2Commented:
you need to provide the full physical path of the file:

like c:\MyData\data.mdb.
0
 
DanBAtkinsonAuthor Commented:
So that means that I will have to ask them for the full filepath to my database?
0
 
b1xml2Commented:
exactly so.
using UNC paths is fraught with problems.
0
 
b1xml2Commented:
The location of your mdb file should be on the same server as your web server, one would hope.
0
 
DanBAtkinsonAuthor Commented:
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.
0
 
DanBAtkinsonAuthor Commented:
At the moment, they're telling me that the error is a coding issue.
0
 
b1xml2Commented:
if you do a cursory check on the error: 0x80004005 this will tell you many times over, it's a permissions issue.
0
 
DanBAtkinsonAuthor Commented:
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***\*********\database\delegates.mdb"
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & filePath & ";")
0
 
b1xml2Commented:
that's not an absolute path.
that's a UNC path as in \\<server_name>\<share>\<...directory >. Are they mad?
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!!
0
 
DanBAtkinsonAuthor Commented:
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?
0
 
b1xml2Commented:
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.ApplicationPath)
Dim targetPath As String = rawPath.Substring(0,rawPath.LastIndexOf("\") + 1) & "databases\data.mdb"

It goes one folder up and then to the databases folder.
0
 
b1xml2Commented:
typo

I am very suspicious as [sic] they cannot provide an absolute physical file path
0
 
DanBAtkinsonAuthor Commented:
How would I impliment this into the db connection?

This is currently:

Dim filePath As String = "\\premfs***\sites\premium***\**********\database\delegates.mdb"
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & filePath & ";")
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.ApplicationPath)
Dim targetPath As String = rawPath.Substring(0,rawPath.LastIndexOf("\") + 1) & "databases\data.mdb"
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & targetPath & ";")
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()

?
0
 
b1xml2Commented:
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,rawPath.LastIndexOf("\") + 1) & "databases\delegates.mdb" <-- to match your mdb file name
0
 
DanBAtkinsonAuthor Commented:
Dim rawPath As String = Server.MapPath(Request.ApplicationPath)
Dim targetPath As String = rawPath.Substring(0,rawPath.LastIndexOf("\") + 1) & "databases\data.mdb"
Dim dbconn As OleDbConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & targetPath & ";")
dbconn.Open()
Dim Cmd As OleDbCommand = New OleDbCommand(SQL,dbconn)
Dim reader As OleDbDataReader
reader = Cmd.ExecuteReader()

This changes the directory to the webroot.
0
 
b1xml2Commented:
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.

0
 
DanBAtkinsonAuthor Commented:
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.
0
 
b1xml2Commented:
you can??
0
 
b1xml2Commented:
Try this for size:

SELECT
      tblDelegate.DelegateName,
      tblDelegate.DelegateAffiliation,
      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
0
 
DanBAtkinsonAuthor Commented:
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?
0
 
b1xml2Commented:
well it's a db issue, do you have the relationships correct between the 3 tables
0
 
DanBAtkinsonAuthor Commented:
Well the queries appear ok in Access.
0
 
DanBAtkinsonAuthor Commented:
Yes. I've gone over the relationships in Access. The query shows 7 results.
0
 
DanBAtkinsonAuthor Commented:
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?
0
 
RejojohnyCommented:
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.DelegateAffiliation, tblSession.SessionID,
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.DelegateAffiliation, tblSession.SessionID,
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 ..

0
 
RejojohnyCommented:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbidbinfo__getkeywords.asp
looks like "Role" is not the reserved word but it is "session" change this field or put this within the square brackets ...
0
 
RejojohnyCommented:
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 ) "

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21267891.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
0
 
DanBAtkinsonAuthor Commented:
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!
0
 
DanBAtkinsonAuthor Commented:
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!
0
 
DanBAtkinsonAuthor Commented:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21416628.html

Is the second and final question of the problem I've had with the db if anyone is interested?!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 18
  • 15
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now