CODEWITHSUNDEEP
asp.netsqlvb.net
user1134409
user1134409

Reputation: 31

Variable isn't taking the data from the SQl query

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

Upvotes: 1

Views: 376

Answers (2)

competent_tech
competent_tech

Reputation: 44971

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 & ";"

Upvotes: 1

xQbert
xQbert

Reputation: 35343

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 & ";"

Upvotes: 0

Related Questions