Retrieve Data using Dataset and Access 2000 DB 1

[JPimp]

I have done this multiple times with SQL and Oracle, but cannot get it to work with an Access DB.

This is what I have for code:

Dim cmdSelect = New OleDbCommand("Select Limit, Value From PartSpecs Where [PART No_:] = '" & strPID & "'", conMetrics)

        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmdSelect)

        Dim myDataSet = New DataSet

        myDA.Fill(myDataSet, "PartSpecs")

        Dim Row As DataRow

        If myDataSet.Tables("PartSpecs").Rows.Count > 0 Then

            Row = myDataSet.Tables("PartSpecs").Rows(0)

        End If

If Row.Item("Value") Is DBNull.Value Then
            LengthUpper.Text = String.Empty
        Else
            LengthUpper.Text = Row.Item("Value")
        End If

When I run it, it complains that Object reference not set to an instance of an object on line:
If Row.Item("Value") Is DBNull.Value Then

Any ideas why this does not work with Access DB?

Thanks in advance,

Kai-What?

 

[jmeckley]

because Row is null. it's only set if a row existed in the table. you need to either
1. check for a null row before checking for a null field, or
2. nest the if statement together.

There are 2 things that I would change.
1. your gui is talking directly to the database. the GUI shouldn't know anything about a database. it should talk to service that delegate that responsiblity appropiately. This is know as seperation of concerns, or single responsibility principle.

2. your using injected sql instead of parameterized queries.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JPimp]

Thanks jmeckly for your advice, I am still a novice programmer when it comes to .NET, so I really appreciate the input.

I was able to retrive a value that has data, and I populated the value in a textbox so I could see what I am working with, but when I set my case code (see below), it does not color the cell red as I want it to, and I think it is because I am checking a textbox?

If Row.Item("Value") Is DBNull.Value Then
            LengthUpper.Text = String.Empty
        Else
            LengthUpper.Text = Row.Item("Value")
        End If

Next sub:

  If e.Item.ItemType <> ListItemType.Header And _
e.Item.ItemType <> ListItemType.Footer And _
e.Item.ItemType = ListItemType.Item Or _
e.Item.ItemType = ListItemType.AlternatingItem Then

            Dim PartNo As Object
            Dim LengthTopXD As Object
            Dim LengthBottomXD As Object
            Dim WidthLeftYD As Object
            Dim WidthRightYD As Object
            Dim SWBMinYD As Object
            Dim SWBMaxYD As Object

            PartNo = DataBinder.Eval(e.Item.DataItem, "Part No_:")
            LengthTopXD = DataBinder.Eval(e.Item.DataItem, "Length Top XD")
            LengthBottomXD = DataBinder.Eval(e.Item.DataItem, "Length Bottom XD")
            WidthLeftYD = DataBinder.Eval(e.Item.DataItem, "Width Left YD")
            WidthRightYD = DataBinder.Eval(e.Item.DataItem, "Width Right YD")
            SWBMinYD = DataBinder.Eval(e.Item.DataItem, "SWB Min YD")
            SWBMaxYD = DataBinder.Eval(e.Item.DataItem, "SWB Max YD")


  Select Case LengthUpper.Text

                Case Is > LengthUpper.Text
                    e.Item.Cells(3).BackColor = Color.Red
            End Select

Kai-What?

 

[jmeckley]

use an if statement instead of a case statement to set the background color.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JPimp]

Thanks, I did and it worked great!

Kai-What?