Need help getting currency value from an access DB

[mic_y]

Hi guys, still busy with the damn hotel project thingy for university.

I am now having a new issue, and the problem is as follows:

I have a roomtype table, where each roomtype has a specific price. The field in access is set as currency datatype.

Now, I have a query reading this value from the database to a variable of type Decimal. The code to do this is as follows:

    Function GetRoomPrice(ByVal intRoomType As Integer) As Decimal
        Dim m_Conn As New OleDb.OleDbConnection
        Dim dbCMD As New OleDb.OleDbCommand

        m_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\GHMRS.mdb"
        m_Conn.Open()
        dbCMD.CommandText = "SELECT RatePerPerson FROM RoomType WHERE RoomTypeID = '" & intRoomType.ToString & "'"
        dbCMD.Connection = m_Conn

        GetRoomPrice = CDec(dbCMD.ExecuteScalar)

        m_Conn.Close()
    End Function

Now, when running, I keep getting an OleDB exception stating that "Data type mismatch in criteria expression."

If anybody could please help with this, would be greatly appreciated

 

[mic_y]

Hey, ok never mind... realised what the issue was me being slightly stupid...

Had too many ' in the query...

 

[dequadin]

You must always use parameterized queries when doing inline SQL like your code. Not only is it safer, but it's more difficult to make those extra/misplaced ' ' 's mistakes.

You should also maybe consider wrapping you OleDb stuff in using statements to ensure they are disposed of correctly.

Check out the following refactoring of your code, this is how I would have done it:

Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Function GetRoomPrice(ByVal roomType As Integer) As Decimal
    Dim roomPrice As Decimal = 0
    Dim connectionBuilder As New OleDbConnectionStringBuilder()
    connectionBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"
    connectionBuilder.DataSource = "C:\HMRS.mdb"
    Using dbConnection As New OleDbConnection(connectionBuilder.ToString())
        Try
            dbConnection.Open()
            Dim sql As String = String.Format("SELECT RatePerPerson FROM RoomType WHERE RoomTypeID = '{0}'", "@RoomTypeID")
            Using dbCommand As New OleDbCommand(sql, dbConnection)
                ' Not sure what your specific OleDbType type should be...
                dbCommand.Parameters.Add("@RoomTypeID", OleDbType.VarChar)
                dbCommand.Parameters("@RoomTypeID").Value = roomType.ToString()
                roomPrice = Convert.ToDecimal(dbCommand.ExecuteScalar())
            End Using
        Catch ex As OleDbException
            ' Maybe make room price -1 here to indicate an error
            roomPrice = -1
        End Try
    End Using
    Return roomPrice
End Function

 

[mic_y]

ah, awesome... thanks for that changed it, and will keep to this method in the future...

 

[dequadin]

ah, awesome... thanks for that changed it, and will keep to this method in the future...

Cool no problem, you should ask Little Bobby Tables why unparameterized SQL is bad! And I'm all about spreading the using statements' IDisposable goodness

Out of interest, why are you using Acess as your DB and not SQL Express/Compact?

 

[mic_y]

Out of interest, why are you using Acess as your DB and not SQL Express/Compact?

Well, it took me a while to formulate an answer to this question without expressing my full hate for the decisions made by Wits Faculty of Commerce School of information systems, but basically, we were told that Access is a requirement, and not an option.

Given the choice, I would have used SQL Server, since I would much rather get some experience with that... but yeah

 

[FarligOpptreden]

Cool no problem, you should ask Little Bobby Tables why unparameterized SQL is bad! And I'm all about spreading the using statements' IDisposable goodness

I'm ALL for IDisposable goodness these days and the using statement's usefulness has made my life SO much easier!

 

[dequadin]

Well, it took me a while to formulate an answer to this question without expressing my full hate for the decisions made by Wits Faculty of Commerce School of information systems, but basically, we were told that Access is a requirement, and not an option.

Given the choice, I would have used SQL Server, since I would much rather get some experience with that... but yeah

If it's a requirement then there's not much you can do. The only reason I ask is because I'm busy with SQL Compact at the moment - and it's awesome.

You can/should-be-able-to/must use this method to solve your previous problem

Something like:

sql = String.Format("INSERT INTO Reservation ({0}, {1}, {2}) VALUES (@{0}, @{1}, @{2})", 
                      "ReservationDate, "StartDate", "EndDate")

Using dbCommand As New OleDbCommand(sql, dbConnection)
        dbCommand.Parameters.Add("@ReservationDate", OleDbType.Date)
        dbCommand.Parameters("@ ReservationDate").Value = System.DateTime.Now.Date
'etc...

 

[dequadin]

I'm ALL for IDisposable goodness these days and the using statement's usefulness has made my life SO much easier!

w0ot!