Databases with DataGridView

[javatech007]

I am creating a bank customer's details application. The information is stored on a Microsoft Access file and I was able to make the information appear on my VB application through text boxes and a DataGridView but I am not able to save,edit or delete records. Does anybody have any ideas. Below is my code.

 
Public Class frmCustomerDetails
 
    Dim objDS As New DataSet
    Dim rowIndex As Integer = 0
    Dim objConn As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=F:\Database-driven banking application\Database-driven banking application\bin\CustomerDetails.mdb"
 
    Dim objSqlStr As String = "SELECT * FROM CustomerDetails"
    Dim objDA As New OleDb.OleDbDataAdapter(objSqlStr, objConn)
 
 
 
 
    Sub UpdateTextBoxes()
 
        txtCustomerID.Text = CStr(objDS.Tables(0).Rows(rowIndex)("CustomerID"))
        txtFirstName.Text = CStr(objDS.Tables(0).Rows(rowIndex)("FirstName"))
        txtLastName.Text = CStr(objDS.Tables(0).Rows(rowIndex)("LastName"))
        txtAddress.Text = CStr(objDS.Tables(0).Rows(rowIndex)("Address"))
        txtHomeNumber.Text = CStr(objDS.Tables(0).Rows(rowIndex)("HomeNumber"))
        txtMobileNumber.Text = CStr(objDS.Tables(0).Rows(rowIndex)("MobileNumber"))
        txtNotes.Text = CStr(objDS.Tables(0).Rows(rowIndex)("Notes"))
 
    End Sub
 
 
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
 
        If rowIndex < objDS.Tables(0).Rows.Count - 1 Then
            rowIndex = rowIndex + 1
            UpdateTextBoxes()
 
        End If
 
    End Sub
 
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
 
        If rowIndex > 0 Then
            rowIndex = rowIndex - 1
            UpdateTextBoxes()
        End If
 
    End Sub
 
    Private Sub txtFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFind.Click
 
        Dim rowcount As Integer
        rowcount = objDS.Tables(0).Rows.Count - 1
 
        Dim ownerFound As Boolean = False
        Dim lastname As String = InputBox("Enter a last name Please!", "Search")
        For i As Integer = 0 To objDS.Tables(0).Rows.Count - 1
            If CStr(objDS.Tables(0).Rows(i)("LastName")) = lastname Then
                ownerFound = True
                rowIndex = i
                UpdateTextBoxes()
 
            End If
        Next
 
    End Sub
 
    Private Sub frmCustomerDetails_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        objDA.Fill(objDS)
        objDA.Dispose()
 
        DataGridView1.DataSource = objDS.Tables(0)
        UpdateTextBoxes()
 
    End Sub
 
 
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
 
        Dim objRow As DataRow
        Dim FirstName As String
        Dim LastName As String
 
        FirstName = txtFirstName.Text
        LastName = txtLastName.Text
 
        'Create a new DataRow object for this table
        objRow = objDS.Tables("CustomerDetails").NewRow
        'Edit Each Field value
        objRow.Item("FirstName") = FirstName
        objRow.Item("LastName") = LastName
 
 
 
        'Officially add the DataRow to our table
        objDS.Tables("CustomerDetails").Rows.Add(objRow)
 
        objDA.Update(objDS, "CustomerDetails")
 
 
    End Sub
 
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
 
        rowIndex = 0
        UpdateTextBoxes()
 
    End Sub
 
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
 
 
 
 
 
 
    End Sub
 
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If Not DataGridView1.CurrentRow.IsNewRow Then
            DataGridView1.Rows.Remove(DataGridView1.CurrentRow)
        End If
 
    End Sub
End Class
 
 

[kenobewan]

There are a number of problems with this code. Suggest debugging and trying to solve some first. Thanks.

[javatech007]

It actually only throws up one error in run time and that is line 84. The error is 'Object reference not set to an instance of an object.'

[kunal pawar]

Problem is due to ur dataset object objDS
At time of Add, Delete or Edit event u r dataset is null. so u must store dataset in session and on each event take it from session and then do operation u want.

[javatech007]

Problem is due to ur dataset object objDS
At time of Add, Delete or Edit event u r dataset is null. so u must store dataset in session and on each event take it from session and then do operation u want.

Hi thanks,

How would I do that if u dont mind telling me??

[Plater]

Hi thanks,

How would I do that if u dont mind telling me??

I see when you reqest the table from your dataset that you give the table a name. However, no where do I see you assigning that name to the table you have. My guess would be that since there is no table with that name in the dataset, it throws that error.

As for checking for null, unsure how in VB, but I think it's like

if objDS.Tables("CustomerDetails") is null
'something to do if dataset is null
end if
 

[javatech007]

Excellent I've got it to sabe in run time but does'nt stayed saved in the database. It adds to the DataGridView in run time but when i start the application again the new records are deleted.

When i do add a new record in run time a marker comes up in the datagrid saying ' Update requires a valid InsertCommand when passed DataRow collection with new rows.'

Anyone have any ideas??

[kenobewan]

Do you have an insert command in your dataadapter? If so what is it?

[javatech007]

I don't think so. All my code is above. Im not familar with insert command!

[Plater]

I don't think so. All my code is above. Im not familar with insert command!

Well that would be why it is telling you it doesn't have the insert command, because it doesn't!

You will need to create an SQL INSERT statement for your DataAdapter

[javatech007]

alright very good,

how and where would i put the code??

[Plater]

alright very good,

how and where would i put the code??

As an OleDbDataAdapter, objDA has .InsertCommand and .UpdateCommand which wnat instances of OleDbCommand, create your SQL statements in those command objects and assign them to OleDbDataAdapter

[javatech007]

should i still be using sql statements if my information comes from an access database!??

[Plater]

Well considering SQL is what you use to talk to pretty much ALL databases, yes.
You are already using an SQL SELECT statement:

Dim objSqlStr As String = "SELECT * FROM CustomerDetails"
 

You even include the Sql in the variable name...

[javatech007]

Should this be the code so, I put it in the first few lines with the other OleDb's?

 
Dim objDC As New OleDb.OleDbCommand(objDA)
 
 

[Plater]

Something like:

Dim objSqlInsertStr As String = "INSERT INTO CustomerDetails (NameOfColumn1, NameOfColumn2, etc) VALUES (@Variable1, @Variable2, etc)"
Dim objDI As New OleDb.OleDbCommad(objSqlInsertStr, objConn)
'You will need to look up how to use the INSERT command 
objDA.InsertCommand = objDI