Datatables, using ADO
Datatables, using ADO
Datatables, using ADO
Datatables, using ADO
Datatables, using ADO
Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO
Datatables, using ADO Datatables, using ADO
Datatables, using ADO
Go Back  Xtreme Visual Basic Talk > > > Datatables, using ADO


Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2014, 07:01 PM
texmex texmex is offline
Centurion
 
Join Date: Dec 2003
Location: Australia
Posts: 171
Default Datatables, using ADO


Hi All,

I am going around in circles here and would really appreciate any help. I am a newbie in VB.net, I am using VS2010. I am converting some VB6 code to .net and I am experiencing a common problem trying to adapt jet4 commands and being able to use the returned data.

In the VB6 code I have 2 tables, Orders, and Details. If I have an order I read the ID number in orders and look up the details table to find a jpeg location and print it.

In VB.net I have managed to load the orders table into a data grid view and it displays nicely. Now I want to read the first line (row) of the DGV and find the ID and the quantity of montages to print. Here is where I need help there are 9 columns in the DGV table, if I need to find the values of each column in the first row how do I go about that. I am not sure how the datarow works in relation to a data table. Can I create 9 variables and load them from the datarow? or is there an easier way to do this? Any help is appreciated.
Reply With Quote
  #2  
Old 03-11-2014, 09:07 AM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

You can access the row in numerous ways.

The simplest being the use of a For Each loop

Code:
For each r as DataGridViewRow in DataGridView1.Rows
  r.Cells(0) 'is the first column it can also use the name for readibility
  r.Cells("Column1")
Next
With the For each, you may need to check to ensure that you are not on the edit row (the last row of the datagridview when AllowUserToAddRows is set to True)

If you're just targeting a specific row you could use:
Code:
DataGridView1.Rows(0).Cells(1) 'The second column of the first row
Or if you want to access the row that is selected
Code:
DataGridView1.CurrentRows.Cells(2) 'The selected Row column 3
These are just a few ways depending on exactly what you're doing.

As far as creating variables, you already have the information available as I showed above. That being said, how are you performing your SQL for the data access? If you have a relationship between the two tables, you can use a JOIN in your Query to bring in the associated row from the other table.

For example:
Quote:
Table1 (Employee)
EmployeeID (PK)
FirstName
LastName

Table2 (PhoneNumbers)
PhoneNumberID
EmployeeID (FK to the EmployeeTable)
PhoneNumber
You could get this phone number by (I'll use * for simplicity)
Code:
SELECT * FROM Employees INNER JOIN PhoneNumbers ON Employees.EmployeeID = PhoneNumbers.EmployeeID
This would return a row containing data from each table. It would also return multiple rows if more than 1 phone number was entered into table 2 for that employeeID.
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict
Reply With Quote
  #3  
Old 03-11-2014, 10:06 AM
Gruff's Avatar
GruffDatatables, using ADO Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

textmex,

Charlie hit the nail on the head. It makes more sense to join your data from both tables in the first place. It is also much more efficient.

One other thing. The DataGridView is meant to be an interface for your DataTable, not the end repository. I would just grab my data directly from the DataTable itself.

The objects are similar to the DataGridView except you use Item() instead of Cells()
Code:
For each r as DataRow in DataTable.Rows r.Item(0) 'is the first column it can also use the name of the column r.Item("Column1") Next DataTable.Rows(0).Item(1) 'The second column of the first row
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #4  
Old 03-11-2014, 07:05 PM
texmex texmex is offline
Centurion
 
Join Date: Dec 2003
Location: Australia
Posts: 171
Default Thanks for the replies

Hi All,

Thank you for the replies. As previously stated I am a complete novice on this stuff. I will look up the join info once I have the software going. The first table has the details of an individual and his images, he could have a group image, an individual image, and a composite image. The second table is the orders table and there could be more than one order for an individual on the first table, mum, dad, a grand parent who knows, each order could have any combination of images with any combination of quantities. All of that stuff is working for now.

I have a print server looking for orders which are ready to print, (there is a ready check box, and a done check box on each order) So I populate the data grid view so the operator can see orders are ready (ready check box checked and done check box unchecked), in my software I pick up the first row, get the image locations and print the orders, then I want to update the done check box.

I have muddled my way through using
Code:
Dim ImgOrder As DataRow = dtb.Rows(0) 'loads the order into ImgOrder from orders table Dim OrdQuery As String = "SELECT * FROM Individuals WHERE ID = " & CDec(ImgOrder("ID")) & "" Dim Ordtb As New DataTable Using Odad As New OleDbDataAdapter(OrdQuery, Conn) Odad.Fill(Ordtb) End Using Dim OrDets As DataRow = Ordtb.Rows(0) 'should only be 1 row, loads image location details in

and now understand that I can use the ImgOrder just like a recordset is used
Code:
If ImgOrder("Montage") <> 0 Then

but now I am trying to workout how to update the database with
ImgOrders("DONE") = TRUE
I am finding difficult to wrap my head around the differences between the handling of the recordsets in vb6 and vb.net
Reply With Quote
  #5  
Old 03-12-2014, 08:49 AM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

It's hard to follow exactly as we don't know what all is contained in the grid exactly.

Lets start with this:
Populate a datatable using Join so that you have all the information on the row. This should be filled using a WHERE Ready = True AND Done = False.

At this point you have a datatable filled only with rows that are ready but not marked done. For the sake of explanation, we'll use dt for datatable

Code:
For each r as DataRow in dt.rows
  'since we know each row is ready to process...
  'get jpeg info from r("TheColumnNameThatHoldsThisInfo") and process it
  r("Done") = True 'You just marked the Done column on the current row to true
  Dim cmd as new OledbCommand(Update <the table that has the done column> SET [Done] = True WHERE UserID = ?"
  cmd.Parameters.AddWithValue("UserID", r("Users.UserID"))
  da.UpdateCommand = cmd
  da.Update(dt) 'You just sent the changed row back to the database
Next
da.dispose
Now if you displayed this information in a DataGridView where you could see the [Done] column, you would see them automatically marked true because you set this column above and the datasource will refresh with the change.
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict
Reply With Quote
  #6  
Old 03-13-2014, 05:53 AM
texmex texmex is offline
Centurion
 
Join Date: Dec 2003
Location: Australia
Posts: 171
Default

Thanks again CharlieMay,

I have another problem that I need to fix first before I can get back to saving the done to the database record. It might take me a while to fix, but I will let you know how I go once I have tried your suggestions.
Reply With Quote
  #7  
Old 03-13-2014, 06:03 PM
texmex texmex is offline
Centurion
 
Join Date: Dec 2003
Location: Australia
Posts: 171
Default

Hi CharlieMay,

I am still having a few problems. I am inside an if statement (when the row count is greater than zero I have orders to process). When I try and adapt my code to your commands I cant get to my data adapter (the error message says its not declared even though it is declared.) Do I need to make a new data adapter or am I doing something basic wrong?

edited: Ok I got over the data adapter problem, I am getting an error on the line
Code:
Odad.UpdateCommand = cmd
the error state "Object reference not set to an instance of an object". Odad is my Orders data adapter

here is my version of your code
Code:
'update the database dtb.Rows(0).Item("Printed") = True Dim cb As New OleDb.OleDbCommandBuilder(Odad) ImgOrder("Printed") = True 'this is the datarow Dim cmd As New OleDbCommand("Update Orders SET [Printed] = True WHERE ID = " & CDec(ImgOrder("ID")) & "") cmd.Parameters.AddWithValue("ID", ImgOrder("Printed")) Odad.UpdateCommand = cmd Odad.Update(dtb)

am I doing something obvious wrong?

Last edited by texmex; 03-13-2014 at 09:17 PM. Reason: made progress
Reply With Quote
  #8  
Old 03-14-2014, 09:05 AM
CharlieMay's Avatar
CharlieMay CharlieMay is offline
Regular
 
Join Date: Sep 2009
Location: Indiana
Posts: 89
Default

If you are still using the Using statement, then it is disposed of when the End Using is reached. You will need to restructure your code to not dispose of the dataadapter since you're not done using it.

So.
Declare the dataadapter in a wider scope
get rid of that using block that instantiates the dataadapter and its connection.

Code:
dim Odad as OledbDataAdapter '(at the Class scope)
replace the Using with...
Code:
Odad = New OleDbDataAdapter(OrdQuery, Conn)
Odad.Fill(Ordtb)
__________________
I thought I knew what I was doing...
...And then I turned on Option Strict
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Datatables, using ADO
Datatables, using ADO
Datatables, using ADO Datatables, using ADO
Datatables, using ADO
Datatables, using ADO
Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO Datatables, using ADO
Datatables, using ADO
Datatables, using ADO
 
Datatables, using ADO
Datatables, using ADO
 
-->