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.

[CODE=vbnet]

Public Class frmCustomerDeta ils

Dim objDS As New DataSet
Dim rowIndex As Integer = 0
Dim objConn As String = "Provider = Microsoft.Jet.O LEDB.4.0;" & "Data Source=F:\Datab ase-driven banking application\Dat abase-driven banking application\bin \CustomerDetail s.mdb"

Dim objSqlStr As String = "SELECT * FROM CustomerDetails "
Dim objDA As New OleDb.OleDbData Adapter(objSqlS tr, objConn)




Sub UpdateTextBoxes ()

txtCustomerID.T ext = CStr(objDS.Tabl es(0).Rows(rowI ndex)("Customer ID"))
txtFirstName.Te xt = CStr(objDS.Tabl es(0).Rows(rowI ndex)("FirstNam e"))
txtLastName.Tex t = CStr(objDS.Tabl es(0).Rows(rowI ndex)("LastName "))
txtAddress.Text = CStr(objDS.Tabl es(0).Rows(rowI ndex)("Address" ))
txtHomeNumber.T ext = CStr(objDS.Tabl es(0).Rows(rowI ndex)("HomeNumb er"))
txtMobileNumber .Text = CStr(objDS.Tabl es(0).Rows(rowI ndex)("MobileNu mber"))
txtNotes.Text = CStr(objDS.Tabl es(0).Rows(rowI ndex)("Notes"))

End Sub


Private Sub btnNext_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles btnNext.Click

If rowIndex < objDS.Tables(0) .Rows.Count - 1 Then
rowIndex = rowIndex + 1
UpdateTextBoxes ()

End If

End Sub

Private Sub btnPrevious_Cli ck(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnPrevious.Cli ck

If rowIndex > 0 Then
rowIndex = rowIndex - 1
UpdateTextBoxes ()
End If

End Sub

Private Sub txtFind_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) 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.Tabl es(0).Rows(i)(" LastName")) = lastname Then
ownerFound = True
rowIndex = i
UpdateTextBoxes ()

End If
Next

End Sub

Private Sub frmCustomerDeta ils_Load(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load

objDA.Fill(objD S)
objDA.Dispose()

DataGridView1.D ataSource = objDS.Tables(0)
UpdateTextBoxes ()

End Sub


Private Sub btnSave_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles btnSave.Click

Dim objRow As DataRow
Dim FirstName As String
Dim LastName As String

FirstName = txtFirstName.Te xt
LastName = txtLastName.Tex t

'Create a new DataRow object for this table
objRow = objDS.Tables("C ustomerDetails" ).NewRow
'Edit Each Field value
objRow.Item("Fi rstName") = FirstName
objRow.Item("La stName") = LastName



'Officially add the DataRow to our table
objDS.Tables("C ustomerDetails" ).Rows.Add(objR ow)

objDA.Update(ob jDS, "CustomerDetail s")


End Sub

Private Sub btnFirst_Click( ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnFirst.Click

rowIndex = 0
UpdateTextBoxes ()

End Sub

Private Sub btnCreate_Click (ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnCreate.Click






End Sub

Private Sub btnDelete_Click (ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnDelete.Click

If Not DataGridView1.C urrentRow.IsNew Row Then
DataGridView1.R ows.Remove(Data GridView1.Curre ntRow)
End If

End Sub
End Class

[/CODE]

[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
[code=vbnet]
if objDS.Tables("C ustomerDetails" ) is null
'something to do if dataset is null
end if
[/code]

[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 OleDbDataAdapte r, objDA has .InsertCommand and .UpdateCommand which wnat instances of OleDbCommand, create your SQL statements in those command objects and assign them to OleDbDataAdapte r

[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:
[code=vbnet]
Dim objSqlStr As String = "SELECT * FROM CustomerDetails "
[/code]

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?

[CODE=VB.NET]

Dim objDC As New OleDb.OleDbComm and(objDA)

[/CODE]