Getting right record to fill dt

[ckeezer]

The idea here is that I have a seach form that contains a txt field, a btn and a dg. When the user starts to type in the txt field the dg filters the data. The user has to highlight a row for editing, then click on the btn. What is supposed to happen is that record is supposed to display on another form, customers1, and allow for changes.

right now, I can get everything to work, but when you click on the btn, it pulls the first record in the dt not the current row. I think I am just missing it here.

By the Way, THANKS VIS781 for all the help thus far.

here is the code for the form:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Search_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] [/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\SmartHouse1.mdb")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter("SELECT CustomerID, CompanyName, FirstName, LastName, PhoneNumber FROM Customers", conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable("Customers")
adapter.Fill(dt)
dgCompanys.DataSource = dt
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] txtCompanyName_TextChanged([/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] txtCompanyName.TextChanged
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dvFilter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataView = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]TypeOf[/COLOR][/SIZE][SIZE=2] dgCompanys.DataSource [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2] DataView [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dvFilter = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dgCompanys.DataSource, DataView)
[/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]TypeOf[/COLOR][/SIZE][SIZE=2] dgCompanys.DataSource [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2] DataTable [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dvFilter = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dgCompanys.DataSource, DataTable).DefaultView
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] txtCompanyName.TextLength > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dvFilter.RowFilter = "CompanyName LIKE '" & txtCompanyName.Text & "*'"
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]dvFilter.RowFilter = ""
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]dgCompanys.DataSource = dvFilter
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnEdit_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] btnEdit.Click
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] dgCompanys.CurrentRowIndex > -1 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] form [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Customers1
[/SIZE][SIZE=2][COLOR=#008000]'form.txtCustomerID = CType(dgCompanys.Item(dgCompanys.CurrentRowIndex, 0), Char)
[/COLOR][/SIZE][SIZE=2]form.Show()
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]MessageBox.Show("Please select a row for editing.", "No Row Selected")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

 

[ckeezer]

cjard, can you walk me through how you made your dataset? I am having an issue with recreating it in a new project.

 

[cjard]

I can! I did make a video of it but it was all jaffed up when i played it back. Im trying to find some time to make another one...


no time like the present. sorry about the size.. i split it in two.. stil ltrying to find a happy medium of screencording... and running VS in 800x600 is a killer!

 

[ckeezer]

Sry it took so long to get back to this... I needed a break and I am buying a house... so much fun.

I will take a look and if I have any other q's I will post them. Thank you for taking the time to do this for me.

 

[ckeezer]

When executing the search on my form I am getting the following error:
Conversion from string "hickory" to type 'Integer' is not valid.

on this line:

CustomersTableAdapter.FillByCustomerID(SmartHouseDSInstance.Customers, txtLastName.Text)

I am using the same code that you did for the search btn, my form looks a little different, but that is it. Any Ideas?


Never mind... I figured it out.. it is in my ds, I have it set to the CustomerID, which is an integer... So I need to change it to the last name and it should work!

 

[ckeezer]

Cjard,
How would I code it to search on any field that the user may type in?
here is what I thought would work... but of course is not:

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] txtCompanyName.Text [/SIZE][SIZE=2][COLOR=#0000ff]IsNot[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]CustomersTableAdapter.FillByCompanyName(SmartHouseDSInstance.Customers, txtCompanyName.Text)
[/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] txtLastName.Text [/SIZE][SIZE=2][COLOR=#0000ff]IsNot[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]CustomersTableAdapter.FillByLastName(SmartHouseDSInstance.Customers, txtLastName.Text)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

 

[cjard]

Well, it can be those, but you need to program them. Remember the process of adding a query to the tableadapter?

I right clicked the table adapter, added query:

SELECT * FROM whatever WHERE column = ?

Then I chose the name to call this in VB, because i had said WHERE column = ? I chose to call it FillByColumn. I could have called it FillAccordingToColumn


Ends up, it creates a method in vb that you can call, you pass in the relevant parameters and off it goes
You cant just say:

MyTableAdapter.FillBySquiggleAndBobbleAndMaybeAlsoBloop(myDT, squiggleTextbox.Text, bobbleTextbox.Text, bloopTextbox.text)

Do you understand why?



Creating a query that will search on any field is a challenge for sure. I solved it by having a view on the database that selects all the columns a screen might show. Then I build an SQL using a header fragment and multiple parameters. Here is the pseudocode:

clear datatable
add one row to DT
let user type into bound controls
strSQL = "SELECT * FROM table WHERE 1=1"
for each cell in the row
  if the cell has a value
    strSQL = strSQL & " AND " & cell's columnname & " = ?"
    add to parameters, name is column name, value is column value
  end if
next cell
 
add the build sql to a new data adapter
use it to select and fill a datatable

Its complex, but basically it is building at runtime the same thing you see at design time if you view the code of the table adapter SELECT command being built.. it jsut has a varying number of parameters thats all

 

[ckeezer]

Well, it can be those, but you need to program them. Remember the process of adding a query to the tableadapter?

I did add the query to the tableadapter, see the screen shot below. That is why I was wondering why it would not work with an if...then statement.

I will try the code that you gave in your last post and see what I come up with.

 

[cjard]

I did add the query to the tableadapter, see the screen shot below. That is why I was wondering why it would not work with an if...then statement.

Oh, it will work, but the thing is.. you then have a limtied numbe rof searchoptions.. youre either searching on LastName or firstname or this or that.. Unless you programmed one table adapter method for every combination (which is N! such that N = the number of textbox on show) and had an equally complex IF..

You'd probably want to build a query dynamically

Let me know how you get on

 

[ckeezer]

Rrrrrrrr...

Ok, I can search on my text box all day long. But I cannot get any changes to save back to the db.

I am using dataset.update() to try and get it there. Can you explain why this would not be working.

Here is the code for my form:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Form1_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] [/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load
[/SIZE][SIZE=2][COLOR=#008000]'TODO: This line of code loads data into the 'SmartHouseDS1.Orders' table. You can move, or remove it, as needed.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OrdersTableAdapter.Fill([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].SmartHouseDS1.Orders)
[/SIZE][SIZE=2][COLOR=#008000]'TODO: This line of code loads data into the 'SmartHouseDS1.Customers' table. You can move, or remove it, as needed.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].CustomersTableAdapter.Fill([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].SmartHouseDS1.Customers)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_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] Button1.Click
SmartHouseDS1.Orders.Clear()
CustomersTableAdapter.FillByLastName(SmartHouseDS1.Customers, txtLName.Text)
OrdersTableAdapter.ClearBeforeFill = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] ro [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SmartHouseDS.CustomersRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] SmartHouseDS1.Customers.Rows
OrdersTableAdapter.FillByCustomerID(SmartHouseDS1.Orders, ro.CustomerID)
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]OrdersTableAdapter.ClearBeforeFill = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] ToolStripButton1_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] ToolStripButton1.Click
CustomersTableAdapter.Update(SmartHouseDS1.Customers)
OrdersTableAdapter.Update(SmartHouseDS1.Orders)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

I have all of the bindings that I need to the db on the form. Thanks,
Chuck

 

[cjard]

did you EndEdit() on the bindingsources before you update?

When data is edited, it is "lifted" out of the data source and into the bindingsource (it can be seen in bindingsource.list).
It gains a status of "Detached" and is not subject to any constraints
If you dont terminate the current edit, then the data remains detached
Some actions such as navigating to another data row automatically cause an EndEdit, which puts back the lifted data.
You cannot commit detached rows to a database, because they arent present in the data source youre sending to the database

Call CustomersBindingSource.EndEdit() before you Update() the customers table, and likewise for the orders:

PrivateSub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
CustomersBindingSource.EndEdit()
OrdersBindingSource.EndEdit()

CustomersTableAdapter.Update(SmartHouseDS1.Customers)
OrdersTableAdapter.Update(SmartHouseDS1.Orders)
EndSub

 

[ckeezer]

I will try this tomorrow. Thanks for explaining why I needed to do the step.. that is a huge help in understanding ADO.net.

 

[cjard]

the following page may help:

http://www.web-enable.com/technology/ado1.asp

 

[ckeezer]

You are a mad man!!! Thank you for the help and the ADO lessons for everyone that posted.

 

[ckeezer]

Cjard,
The data seems to be saved in the dataset, but not in the database. When I am in the app and update a record and click save, it saves it and I can go to any other record and back again and the data is updated. However, when I close and reopen the app, the data is back to its original state.

Why would it not be updating the db?

 

[cjard]

Read from post 12 onwards in this thread:
http://www.vbdotnetforums.com/showpost.php?p=41143&postcount=12