A Level project - updating db with data grid

[PiranhaTheShadowHero]

Hi people!

I've had a look at the existing threads, but either they don't quite match my question or I'm being way too blind and n00b like to understand.

My task:

Creating a library system using VB.Net and MS Access.

My problem: I can't figure out what I have to do in order to update the table with a new record (or even an existing record for that matter) that I've entered in the datagrid.

The book I used for a tutorial suggested that all I had to do was type "OleDbDataAdapter1.Update(DataSet1)" [yeah, I know, naming conventions.... I'll do it later ] but this clearly doesn't do the trick.

I'm fairly new to using Access even by itself, but I'm happy that I've managed to get the program to show in the datagrid what's contained in the tables.

Once I've got this problem solved, the rest of the database aspect will be a piece of cake, but I expect I'll be posting to ask about that barcode reader....

Thanks in advance!

P

 

[vis781]

Nice looking form!! Anyway This Bit....

SQLString = "SELECT * FROM " & Table
Adapter = New OleDbDataAdapter(SQLString, ConnectString)
Adapter.Update(dtViewTable)
Adapter.Fill(dtViewTable)
grdTableView.DataSource = dtViewTable
btnTools.Visible = True
OleDbDataAdapter1.Fill(DataSetAlpha1)

You've got two dataadapters there, Why? Your filling dtviewtable with the information from the database with one adapter then using a different one to update it, try using the same one. In the adapter.update.. your passing the dataset when you havent added the table you are modifying to it, pass the datatable instead. Lastly after specifying the SQL select command you call update on the dtviewtable, why?

 

[PiranhaTheShadowHero]

Everything there is copied from a VB.Net book...

Unfortunately working with databases is one of my weakest areas in computing...

I'll try this stuff now though.

 

[PiranhaTheShadowHero]

I'm totally flummoxed by this now... This is the error I get, and where the error occurs.

 

[vis781]

Post back with the forms code again and i'll take a look. This time i'll make the revisions and post it back to you with code comments.

 

[PiranhaTheShadowHero]

This is the code with some of the crap removed, like the timers to make the form expand in a sliding manner

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] TableInt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int16
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Table [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SQLString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtViewTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ConnectString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Provider = Microsoft.Jet.OLEDB.4.0; Data _[/SIZE]
[SIZE=2]Source = LibDB.mdb"
 
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnLoad_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnLoad.Click
TableInt = lstTables.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] TableInt
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 0
Table = "Books"
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 1
Table = "Items"
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 2
Table = "Staff"
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 3
Table = "StoredRecords"
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 4
Table = "StudentTable"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2]SQLString = "SELECT * FROM " & Table
Adapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(SQLString, ConnectString)
Adapter.Update(dtViewTable)
Adapter.Fill(dtViewTable)
grdTableView.DataSource = dtViewTable
btnTools.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]Adapter.Fill(DataSetAlpha1)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] TableViewer_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As_[/COLOR][/SIZE]
[SIZE=2]System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] lstTables_SelectedIndexChanged([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As_[/COLOR][/SIZE]
[SIZE=2]System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] lstTables.SelectedIndexChanged
btnLoad.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnUpdate_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As_[/COLOR][/SIZE]
[SIZE=2]System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnUpdate.Click
dtViewTable.NewRow()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
cmd.Connection = OleDbConnection1
cmd.CommandText = "INSERT INTO 'StudentTable' VALUES (?, ?, ?, ? ,?)"
cmd.Parameters.Add("@StudentID", OleDbType.Double, 6, "StudentID")
cmd.Parameters.Add("@StudentSurname", OleDbType.Char, 255, "StudentSurname")
cmd.Parameters.Add("@StudentFirstName", OleDbType.Char, 255, "StudentFirstName")
cmd.Parameters.Add("@StudentYear", OleDbType.Integer, 2, "StudentYear")
cmd.Parameters.Add("@StudentTutorGroup", OleDbType.Char, 1, "StudentTutorGroup")
Adapter.InsertCommand = cmd
Adapter.Update(DataSetAlpha1)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

I've also made as many changes as I could based on your advice.

 

[TechGnome]

Okay.... if you don't mind me being honest... that's some jacked up code there... let's see if we can't get this all sorted out....

First I'll take your update code as it is, point out what isn't right and make suggestions on fixing it.

Dim TableInt As Int16
Dim Table AsString
Dim SQLString AsString
Dim dtViewTable AsNew DataTable
Dim Adapter As OleDbDataAdapter
Dim ConnectString AsString = "Provider = Microsoft.Jet.OLEDB.4.0; Data _
Source = LibDB.mdb"
 
PrivateSub btnLoad_Click(ByVal sender As System.Object, ByVal e As _
System.EventArgs) Handles btnLoad.Click
TableInt = lstTables.SelectedIndex
SelectCase TableInt
Case 0
Table = "Books"
Case 1
Table = "Items"
Case 2
Table = "Staff"
Case 3
Table = "StoredRecords"
Case 4
Table = "StudentTable"
EndSelect
SQLString = "SELECT * FROM " & Table
Adapter = New OleDbDataAdapter(SQLString, ConnectString)

'OK, at this point, I assume what you want to do is simply load the grid, right?
Adapter.Update(dtViewTable) ' If that's the case, delete this line. At this point it's not doing anything usefull


Adapter.Fill(dtViewTable) 'Try filling this into the DataSet instead... like you did below... And give it a name too.
grdTableView.DataSource = dtViewTable 'Then set the Dataset as the datasource. Don't forget to set the DataMember of the grid to the name of the table.
btnTools.Visible = True
Adapter.Fill(DataSetAlpha1) 'Except this.... this needs to go where I noted above...
EndSub

 
PrivateSub TableViewer_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) HandlesMyBase.Load

EndSub

PrivateSub lstTables_SelectedIndexChanged(ByVal sender As_
System.Object, ByVal e As System.EventArgs) Handles lstTables.SelectedIndexChanged

  btnLoad.Enabled = True
EndSub


PrivateSub btnUpdate_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles btnUpdate.Click

  'This is the next problem child. This is for adding a new row to the datatable
  'Normaly after issuing this command, one then sets the values of the fields for the newly added row.
  'If you aren't adding rows, don't use it.
  dtViewTable.NewRow()


  Dim cmd AsNew OleDbCommand
  cmd.Connection = OleDbConnection1
  cmd.CommandText = "INSERT INTO 'StudentTable' VALUES (?, ?, ?, ? ,?)"
  cmd.Parameters.Add("@StudentID", OleDbType.Double, 6, "StudentID")
  cmd.Parameters.Add("@StudentSurname", OleDbType.Char, 255, "StudentSurname")
  cmd.Parameters.Add("@StudentFirstName", OleDbType.Char, 255, "StudentFirstName")
  cmd.Parameters.Add("@StudentYear", OleDbType.Integer, 2, "StudentYear")
  cmd.Parameters.Add("@StudentTutorGroup", OleDbType.Char, 1, "StudentTutorGroup")
  Adapter.InsertCommand = cmd

  'In addition to setting the InsertCommand object, you should also do the same for the Update, and Delete too.
  'But wait! There's hope. Since the select is a simple one, building your additional command (INsert, Update & delete) is very easy.
  'Look up the CommandBulder object (I think I also used it in my tutorials too) to build the additional statements.
  'It's easy to use and will take out much of the code where mistakes can happen.

  Adapter.Update(DataSetAlpha1) 'At this point, the datatable in the grid does not correspond to the datatable in here. This won't do anything.

EndSub
EndClass

I hope the items I've pointed out help.

-tg

 

[PiranhaTheShadowHero]

Ah this looks brilliant, thank you both for all the help so far! Unfortunately it's a bit late in the evening for me to work at this, but I'll take a look tomorrow, and I'll let you know how I got on!

Thank you very much again!

P.

 

[PiranhaTheShadowHero]

Hi, just got back from England last night, so I haven't had time to look again at this problem, but I'll do so later this afternoon, and I'll doubtless have more questions.

Thanks for the continuing support!

 

[PiranhaTheShadowHero]

Problem Solved

Hi,

I've managed at long last to solve the problem, it was as simple as realising that I should use 1 OleDbAdapter per table, not trying to use one to deal with all the tables... :S

Program itself is looking great now, bluetooth barcode reader working and all!

Thanks again for your help!

Guy