[RESOLVED] working with databases

[ecotec8286]

Hey Everyone! Im trying to modify a parameterized constructor for a "Stock class" so it will construct the desired "stock object" from a database rather than from a text file. After executing the fill command, im trying to see if the target stock code was found in the stock table. Im having a "No value given for one or more required parameters.", when I try to fill the table, any suggestions?

Heres my test stock constructor:

'test stock constructor
        Dim test As New stock("FRD")
        MessageBox.Show(test.StockName)

Heres my Stock class:

Public Class stock
    Private m_code As String   'stock code
    Private m_name As String   'stock name
    Private m_price As Double  'current price per share

    Public ReadOnly Property StockCode() As String
        Get
            Return m_code
        End Get
    End Property

    Public ReadOnly Property StockName() As String
        Get
            Return m_name
        End Get
    End Property

    Public ReadOnly Property StockPrice() As Double
        Get
            Return m_price
        End Get
    End Property

    Public Sub New()   'creates a default stock object
        m_code = ""
        m_name = "UNKNOWN"
        m_price = 0
    End Sub

'creates a stock object from input parameters
    Public Sub New(ByVal code As String)
        
        Dim selectstring As String
        Dim connectionstring As String
        Dim stockadapter As OleDb.OleDbDataAdapter
        Dim stocktable As DataTable

        connectionstring = "Provider = Microsoft.JET.OLEDB.4.0;" & "Data Source = barestern2003.mdb"

        'create select statement
        selectstring = "select * from stock where stk_code = " & code

        'create adapter
        stockadapter = New OleDb.OleDbDataAdapter(selectstring, connectionstring)

        'create and fill the member data table
        stocktable = New DataTable
        stockadapter.Fill(stocktable) 'Fill the data table

        holdcode = CStr(stocktable.Rows(0).Item(0))
        holdname = CStr(stocktable.Rows(0).Item(1))
        holdprice = CDbl(stocktable.Rows(0).Item(2))
        If holdcode = code Then 'this is the one we want
            m_code = code
            m_name = holdname
            m_price = holdprice
            found = True
        End If

Heres the database from where im trying to pull from, but obviously in database form:
stock
stk_code stk_name stk_price
AIG AGONY INVESTING GRP 4.5
BAHHF BAHAMIAN HEDGE FUND 50
CAYBC CAYMAN BANK CORP. 140
DRYMF DREYFUSS MUTUAL FUND 25.2
ENRON ENRON ENERGY 0.45
FRD FORD MOTOR CORP. 275
GLOM GLOBAL OMNISCIENCE 40
HMC HOMELOAN MORTGAGE CO 10
IBM ITTY BITTY MACHINES 400

[jmcilhinney]

First up, it's bad design to have an entity class incorporate the data access code to get its own data. It will work and if you're happy with it then you can stick with it but good design principles dictate that the data access code be separate to the data. If you want to create your own classes then you might consider the ECC pattern (Engine, Collection, Class) where for each entity you define three classes: one for the entity itself, one for a collection of entity objects and one for the data access code that retrieves and saves the entity data. The data access engine is responsible for getting the data and constructing the entity classes and also for saving the contents of the entity classes back to the database.

Anyway, with regards to your question, unless your stk_code column is numeric, the issue is that you aren't wrapping the value in single quotes. This is an example of why you should always use parameters rather than string concatenation: so you don;t have to remember what requires what delimiters. Follow the Blog link in my signature and read my post on ADO.NET Parameters for more information.