Variable isn't taking the data from the SQl query

Question

I have an SQL set up to query a database and find the sum of the amounts in the table, however it's not returning anything, all the data field are correct and also the query is correct in how it should run, I think the issue is passing it to the variable for the data reader, if anyone can correct this I would be greatful.

This is the code, I think it is something to do with the datareader:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---

System.Data.OleDb.OleDbException: Syntax error in FROM clause. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at HSBC.CheckBal(Int64 accountnumber) at HSBC.CheckBalance(Int64 accountnumber) --- End of inner exception stack trace ---

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: Syntax error in FROM clause. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at HSBC.CheckBal(Int64 accountnumber) at HSBC.CheckBalance(Int64 accountnumber) --- End of inner exception stack trace ---

This is the code that is being called.

    'Creates a service web method

    Private Function CheckBal(ByVal accountnumber As Long) As String

        'Database(drivers, connections And commands)
        Dim BalanceDr As OleDbDataReader
        Dim BalanceConn As OleDbConnection
        Dim BalanceCmd As OleDbCommand

        'Database connection string
        Dim Connx As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/HSBC.mdb") & ";"

        'SQL

        Dim SQL As String = "SELECT Transaction_Amount FROM Transaction WHERE Account_Number =" & accountnumber & ";"

        Dim bal As String

        'Open the connection to the database
        BalanceConn = New OleDbConnection(Connx)
        BalanceConn.Open()
        BalanceCmd = New OleDbCommand(SQL, BalanceConn)

        'Create a DataReader that will return information.
        BalanceDr = _
        BalanceCmd.ExecuteReader(CommandBehavior.CloseConnection)

        If BalanceDr.Read() Then
            'A row was returned
            bal = BalanceDr(0)
        Else
            'A row was not returned
            bal = "No Balance For Account Found"
        End If
        BalanceDr.Close()
        Return SQL
    End Function
End Class

does the application have write access to the database folder? without it it can't create the .ldb file thus no locks, no access no queries no nothing. step though and after you open the connection check the /database folder and see if the ldb file is there. if not, permissions are missing. If its web I can't remember if its the I_User or W_User for IIS. — xQbert
– xQbert, Commented Jan 9, 2012 at 0:18
In the database under permissions it has read design, modify design, administer, read data, update data, insert data and delete data ticked but it's saying it's running as admin. Also I don't have a ldb file in the database folder. The permissions are there as I have another service running that is saving to the database and an external database also — user1134409
– user1134409, Commented Jan 9, 2012 at 0:23
the .ldb file is created when the connection is opened. Open the .mdb manually and you'll see access creates a .ldb in that folder. as for the permissions; if this is a website, you need the I_USER or I_Wuser to have write permissions to the database folder. Whatever the account the webservice is running under needs permissions to the folder to write. example — xQbert
– xQbert, Commented Jan 9, 2012 at 0:25

competent_tech · Accepted Answer · 2012-01-09 01:42:39Z

1

The problem is that Transaction, which is also a table name in your database is a reserved word; this is why you get the syntax error.

Changing your SQL statement to enclose the table name in square brackets will resolve the issue:

Dim SQL As String = "SELECT Transaction_Amount FROM [Transaction] WHERE Account_Number =" & accountnumber & ";"

answered Jan 9, 2012 at 1:42

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xQbert · Accepted Answer · 2012-01-09 00:40:48Z

How Permissions are set.

Below is a quick summary of how permissions work and what Server Admins need to do to set them in case you are curious.

ASP pages are accessed by anonymous users via the web browser When users access these pages via a web browser IIS will use (by default) a Windows NT account called iUSR_ or "everyone".

To set the permissions you have to be on the server and browse to the folder the database is in. Then right click and choose properties. Then you give that account change or modify permissions on the folder that the database is in. Change or modify means... r w x d (read/write/execute/delete). In the future when you put another database in that same directory it will usually inherit the permissions and you will not have to ask again. If you delete the directory and recreate it you will lose it's permissions and they will have to be set again.

Hopefully this gives you a better idea of what is going on when you can't get your ASP scripts and databases working correctly. SOURCE

Option 2) not sure of jet treats transaction as a reserved word. try putting it in []'s

SELECT Transaction_Amount FROM [Transaction] WHERE Account_Number =" & accountnumber & ";"

Collectives™ on Stack Overflow

Variable isn't taking the data from the SQl query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related