retrieve a single value from an OleDb

[medicenpringles]

Hello again,

I need some help with a simple OLEDB query. I need to return one value from an Access database. the code below is a snapshot of what i have so far.

Imports System.Data.OleDb
Public Class Prices
    
    Public Shared ReadOnly Property Price(ByVal Name As String, ByVal Size As String) As Double
        Get
            Dim connString As String = "Data Source=""C:\Documents and Settings\user\" & _
            "My Documents\Visual Studio Projects\Quiznos Register\Assoc. Files\Quiznos.mdb"";" & _<
            "Jet OLEDB:Engine Type=5;" & _
            "Provider=""Microsoft.Jet.OLEDB.4.0"";" & _
            "persist security info=False;"
            Dim conn As New OleDbConnection(connString)
            Dim cmdGetPrice As New OleDbCommand("SELECT Products.Price FROM Products Products WHERE" & _
            "(Products.Name='Classic Italian') AND (Products.Size='Small')", conn)

            Try
                If conn.State <> ConnectionState.Open Then
                    conn.Open()
                End If
            Catch ex As OleDbException
                MsgBox(ex.Message)
            End Try

            Select Case Name
                Case "Classic Italian"
                    Select Case Size
                        Case "Small"
                            Price = cmdGetPrice.ExecuteNonQuery    '<----- This is where it gives me the error.
			'other cases for other sizes
                    End Select
            ' other cases
            End Select
            Return Price

            conn.Close()

        End Get
    End Property
End Class

at the line "Price = cmdGetPrice.ExecuteNonQuery", i get an InvalidOperationException. what do i need to do to return this single value

thanks ahead of time,
stephen

-- modified at 12:47 Friday 26th August, 2005

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[medicenpringles]

oh, and sorry about that last post, i wasn't trying to be mean.

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[ThatRickGuy]

Err, for starters you are calling ExecuteNonQuery on a Query command... ExecuteNonQuery is used for Updates, Insterts, Deletes, and Selects that only return parameters, not data.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[medicenpringles]

but there is not ExecuteQuery (that i can find) and when i try ExecuteScalar, it gives me the same error.

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[medicenpringles]

oh, and the "& _<" in line 7 is (in the code) just "& _". typo.

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[RiverGuy]

.ExecuteScalar is the one you want. Why don't you wrap it in a try/catch block so you can see the error message.

 

[medicenpringles]

i get:
HIErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[medicenpringles]

IErrorInfo^

not HI

damn those typos

Main Language: Visual Basic .NET
Development Environment: Visual Studio .NET 2003

 

[BobRodes]

So, are you sure that small classic italian represents one and only one product?

Bob

 

[ThatRickGuy]

You might want to look at what executescalar returns. I have a feeling it's not a double.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

Does the query work in access?

try making a datareader and filling that and see if it gets any records.

BTW conn.close will never be executed since it after the return.



Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]