VS 2008 and Access 2003

[TChaiyaphanVBF]

Imports System.Data.OleDb
 
Public Class highScore
    Dim dbConnection As OleDb.OleDbConnection
    Dim dbCommand As OleDb.OleDbCommand
    Dim dbAdapter As OleDb.OleDbDataAdapter
    Dim ConnectionString As String = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source = SSADatabase.mdb"
    Dim dtHighScore As DataTable
    Dim SelectedData As String = "highScore"
 
    Private Sub getData()
        dbConnection = New OleDb.OleDbConnection(ConnectionString)
        dbAdapter = New OleDb.OleDbDataAdapter
        dtHighScore = New DataTable
        dbCommand = New OleDb.OleDbCommand(SelectedData)
        dbCommand.CommandType = CommandType.TableDirect
        dbAdapter.SelectCommand = dbCommand
        dbAdapter.SelectCommand.Connection = dbConnection
        dbConnection.Open()
        dbAdapter.Fill(dtHighScore)
        DataGridView.DataSource = dtHighScore
    End Sub
 
    Private Sub highScore_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
        getData()
    End Sub
 
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim objRow As DataRow
        Dim tableName As String = "highScore"
        Dim objDataSet As New DataSet
        If txtName.Text = "" Then
            MsgBox("You need to fill out your name")
            Exit Sub
        End If
        Dim newAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM " & tableName & "", ConnectionString)
        objDataSet.Clear()
        newAdapter.FillSchema(objDataSet, SchemaType.Source, tableName)
        newAdapter.Fill(objDataSet, tableName)
        objRow = objDataSet.Tables(tableName).Rows.Find(objDataSet.Tables(tableName).Rows.Count + 1)
        objRow.Item("Name") = txtName.Text   '' <----- This line here
        objRow.Item("Score") = myTotalScore
 
    End Sub

Note the last 3rd line i get this error when i try to save data
Object reference not set to an instance of an object.
It never happens to me before and it works on a different application that i made.

My DB looks like this
ID = auto number & primary no
Name = Text
Score = number

What is going on???

Please help me!

T Chaiyaphan

[RhinoBull]

It's the Find method that failed - if nothing is returned then your objRow object won't be set:

objRow = objDataSet.Tables(tableName).Rows.Find(objDataSet.Tables(tableName).Rows.Count + 1)


Run it in debug to see what's value you're searching for:

objDataSet.Tables(tableName).Rows.Count + 1 <<< this value might not be there.


Regardless you need Try - Catch block in your procedure to capture (and handle) exceptions.

[TChaiyaphanVBF]

Yes you are right it returns nothing on:

objRow = objDataSet.Tables(tableName).Rows.Find(objDataSet.Tables(tableName).Rows.Count + 1)

objRow = "Nothing"
How can i fix this?

[TChaiyaphanVBF]

Dim objRow As DataRow
        Dim tableName As String = "highScore"
        Dim objDataSet As New DataSet
        If txtName.Text = "" Then
            MsgBox("You need to fill out your name")
            Exit Sub
        End If
        Try
            dbAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & tableName & "", ConnectionString)
            objDataSet.Clear()
            dbAdapter.FillSchema(objDataSet, SchemaType.Source, tableName)
            dbAdapter.Fill(objDataSet, tableName)
            objRow = objDataSet.Tables(tableName).Rows.Find(objDataSet.Tables(tableName).Rows.Count + 1)
            objRow.Item("Name") = txtName.Text
            objRow.Item("Score") = myTotalScore
            getData()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

i get an error
Object reference not set to an instance of an object

While not? it is right isnt it

[TChaiyaphanVBF]

Dim objRow As DataRow
        Dim tableName As String = "highScore"
        Dim objDataSet As New DataSet
        If txtName.Text = "" Then
            MsgBox("You need to fill out your name")
            Exit Sub
        End If
        Try
            dbAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & tableName & "", ConnectionString)
            objDataSet.Clear()
            dbAdapter.FillSchema(objDataSet, SchemaType.Source, tableName)
            dbAdapter.Fill(objDataSet, tableName)
            objRow = objDataSet.Tables(tableName).Rows.Add
            Dim rowNum = objDataSet.Tables(tableName).Rows.Count
            objRow = objDataSet.Tables(tableName).Rows.Find(rowNum)
            objRow.Item(0) = rowNum
            objRow.Item(1) = txtName.Text
            objRow.Item(2) = myTotalScore
            getData()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

I dont get errors from doing this but when it doesnt save to database and objRow isnt null anymore

[RhinoBull]

Well, I only pointed why you're getting that error and how you should be handling it but you'll have to determine why record you expected to be in the table isn't there on your own - you are the one who should know your database best.

[RunsWithScissors]

I tend not to use the DataAdapter myself, but it looks to me like you need to:

A. Specify an INSERT command
B. Call the dbAdapter.Update method after adding your new row.

Unless you have controls bound to your dataset, you might want to examine whether the DataSet/DataAdapter approach is the most efficient.

jmchilhinney has a number of posts in his code bank related to data access. Here is one:

http://www.vbforums.com/showthread.php?t=469872

[RhinoBull]

You don't have to go to code bank - this forum has great ado tutorials with samples.

btw, I never use any bindings what so ever - they are nothing but pain. Direct select/insert/update/delete imho is much better approach.

[RunsWithScissors]

You don't have to go to code bank - this forum has great ado tutorials with samples.

btw, I never use any bindings what so ever - they are nothing but pain. Direct select/insert/update/delete imho is much better approach.

Yup, What Rhinobull said, on both counts.

That said, the tutorial of jmc's I provided as a link is one of the better ones, even though tere are great tutorials here in the FAQ as well.

Bound controls ARE a pain!!

[TChaiyaphanVBF]

If txtName.Text = "" Then
            MsgBox("You need to fill out your name")
            Exit Sub
        End If
        Try
            ''This code is doing my head in''
            Dim objRow As DataRow
            Dim tableName As String = "highScore"
            Dim objDataSet As New DataSet
            dbConnection = New OleDb.OleDbConnection(ConnectionString)
 
            dbAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & tableName & "", ConnectionString)
            Dim NewCommand = New OleDb.OleDbCommandBuilder(dbAdapter)
            dbAdapter.FillSchema(objDataSet, SchemaType.Source, tableName)
            dbAdapter.Fill(objDataSet, tableName)
            objRow = objDataSet.Tables(tableName).Rows.Add
            Dim rowNum = objDataSet.Tables(tableName).Rows.Count
            objRow = objDataSet.Tables(tableName).Rows.Find(rowNum)
 
            objRow.Item(0) = rowNum
            objRow.Item(1) = txtName.Text
            objRow.Item(2) = myTotalScore
            dbAdapter.Update(objDataSet, tableName)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Ok this works and anyway thanks for the helps folks