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]

You will need a SQL UPDATE, INSERT INTO, and DELETE commands. Techgnome has some ADO.Net tutorials in his sig or you can just google for SQL command and go from there.

 

[PiranhaTheShadowHero]

I'd read about that somewhere...

I'll check the tutorials when I've got a bit more time on my hands, but can I safely ask for some example code here?

Thanks again

P

 

[vis781]

dim cmd as new oledbcommand

cmd.commandtext = "UPDATE TableName SET ColumnName = ?, columnname = ?, etc"
cmd.connection = your oledbconnection
cmd.parameters.add("@columnname1", oledbtype.(the datatype), fieldsize,source column name as string)

This is a crude expample and it's difficulto to write a specific example as it depend on the datatype. However you'll need to look into parameterised command. The question mark in the above example acts as a placeholder for the value in the parameter. Paraeters must be added in the order that they appear in you query. I'll try to find you a good link....

 

[vis781]

Thinking about it. Heres a link to a MSDN site where you can download 101 examples. In there you will find loads of info about using ADO.Net...

http://www.microsoft.com/downloads/details.aspx?familyid=08e3d5f8-033d-420b-a3b1-3074505c03f3&displaylang=en

 

[PiranhaTheShadowHero]

Nice, thank you I'll take a look this evening and get back to you!

P

 

[PiranhaTheShadowHero]

Okay, I've taken a look at the examples, but I still don't see anything about using the changes made to the datagrid.

The way I'm seeing this is that the datagrid is a vehicle for representing the data in the way that MS Access itself does. Sure enough, I'm able to fill the grid with the currently existing data, but I can't in my mind see a way to reverse the process - using the changes in the datagrid to update the database.

The table I'm testing with has the columns:

StudentID FirstName LastName YearGroup Tutor

I've been trying to create a new row in the DataGrid, and subsequently using the OleDbDataAdapter1.Update(DataSet1), as the book I used directed me.

Hope this is a bit more specific; this problem is driving us crazy!

P

 

[vis781]

Right so you are using a dataadpater to fill your datatable with information from the database. You are then setting the datasource of the datagrid to the datatable to display the information within it. Then you are trying to add a new row to the datagrid and send the changes back to the database? So Make sure you have added some rows to your datatable and being sure that you have filled in the fields with any constraints or it will throw an exception. What you need then is an SQL INSERT command. It will look something like this...

Dim cmd as new oledbcommand
cmd.commandtext = "INSERT INTO 'The Table Name' VALUES (?, ?, ?, ? ,?)
cmd.parameters.add("@StudentID", The Datatype for this field,the size of this field,"SudentId (If this is the 
original field/column name)
 add the rest of the parameters for each question mark in the SQL statement, being sure to add the correct 
datatype,fieldsize and original column name.

Then set the insertcommand on the dataadapter you used to fill the datatable to the oledbcommand we just made.

YourDataadapter.InsertCommand = cmd

Then You can call the update method of the dataadapter...

YourDataAdapter.Update(Dataset11)

 

[PiranhaTheShadowHero]

Awesome! Thank you, I'll give it a shot this evening after school!

P

 

[vis781]

Forgot to say the you'll also have to set the connection property of the oledbcommand.....

cmd.connection = your oledbconnection

 

[PiranhaTheShadowHero]

Okay, I've had a stab at the second part of it, but I'm stuck on the first part. My code is as follows:

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/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] [/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)
OleDbDataAdapter1.InsertCommand = cmd
OleDbDataAdapter1.Update(DataSetAlpha1)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

But as you said, there's something I'm not doing that's causing it to throw an exception, which reads:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.

Aaaaand I can't figure out what I need to do to iron that one out.

I'm really grateful for all your help so far, I just wish that I knew a bit more of what I was doing myself...

P.

 

[vis781]

Looking at that, it shouldn't even compile because the final argument in the parameters should be a string. Secondly i cant understand what this line...

dtViewTable.NewRow()

is supposed to be doing.

 

[PiranhaTheShadowHero]

That line was my attempt at trying to solve the problem the exception was talking about.

Perhaps if I post all the code from that form, will that be able to clear a few things up?

 

[vis781]

Yea, why not. But can you do me a favor and not make it go of the end of the screen. It's a real pain when you have to keep scrolling it accross

 

[PiranhaTheShadowHero]

[COLOR=#0000ff][SIZE=2]Dim Ta[/SIZE][/COLOR][SIZE=2]bleInt [/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 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]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]OleDbDataAdapter1.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 As _ [/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]
[/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][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SizeFlag [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnTools_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] btnTools.Click
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] SizeFlag = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]SizeFlag = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]Timer2.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]SizeFlag = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/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] Timer2_Tick([/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] Timer2.Tick
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width > 712 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width - 5
[/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width <= 712 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width = 712
Timer2.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/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] Timer1_Tick([/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] Timer1.Tick
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width < 856 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width + 5
[/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width >= 832 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Width = 856
Timer1.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] UpdateFlag [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/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] _[/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")
OleDbDataAdapter1.InsertCommand = cmd
OleDbDataAdapter1.Update(DataSetAlpha1)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

Please excuse the screwy formatting, I've tried to keep at as narrow as possible but I'm using 1200x800; not sure how it looks to other people.
I hope the picture attatches too...!