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]

 

[vis781]

Ckeezer, you are almost there. You can get the current row and the information stored therein form the column index. All you need to do is pass you new form the info. I don't understand what you are asking here?

 

[ckeezer]

I guess my question is this, do I need the select statement, that is populating the adapter, to pull all of the info that I want displayed on the new form?
Right now it is olny pulling CustomerID, CompanyName, FirstName, LastName, PhoneNumber. But there are 13 fields in all that I need populated on the new form. Since everything is comming from this select statment, do I then need to put all 13 fields in the dg inorder to set the field properties.

Example:

[COLOR=#0000ff][SIZE=2][COLOR=#008000]'Select all fields to display in the dg
[/COLOR][/SIZE][SIZE=2][/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([/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Customers"[/COLOR][/SIZE][SIZE=2], conn)
[/SIZE][SIZE=2][COLOR=#008000]'Set each field to a variable of the select statement
[/COLOR][/SIZE][SIZE=2]form.txtCustomerID = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dgCompanys.Item(dgCompanys.CurrentRowIndex, 0), [/SIZE][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][SIZE=2])
Form.txtfirstname = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dgCompanys.Item(dgCompanys.CurrentRowIndex, 1), [/SIZE][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][SIZE=2])
Form.txtlastname = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dgCompanys.Item(dgCompanys.CurrentRowIndex, 2), [/SIZE][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]'And so on[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]

[/COLOR][/SIZE]
One reason that I do not want to do this is because I only want the user to see 5 of the 13 columns in the dg.

hope that makes some sense
[/COLOR]

 

[vis781]

I'm still a bit confused. You have a form with a populated datagrid? The when the user selects a row in the datagrid you then want to show another form with a bunch of textboxes on it and display some datafrom that row? Do you then want to be able to update the datasource from that form? or the original form with the datagrid on it?

 

[aBsOlUt]

vis,

I think what he is trying to say is that while he is selecting 5 columns in his main form to show in his dataGridView, on his second form he wants all (13) columns to show for the selected record. He is wondering if he needs to update his Select statement on the adapters constructor.

If your database is relatively small you may as well select all the columns and hide the ones you do not need in your dataGrid. I also see you are using .NET 1.1 in which is not very easy to hide a column in a dataGrid as you do not have the Columns property...

Other solutions are welcome!

 

[ckeezer]

I have just upgraded to VS 2005, yesterday it got installed. I was trying to do exactly what you say, just hide the columns in the dg.... now that I have 2005 I will look into the Columns property.

Thanks

 

[ckeezer]

Looked into the columns property, it seems if you set the ColumnMapping = hidden, that it is like the column is not there, even to the datatable. So that is getting me 1/2 way there. I can get the columns hidden, but then I cannot populate my form based on the values in the dg.

kind of a catch 22 on that one.

any other ideas?

 

[vis781]

You can set the actual column of the datagridview's visibility like this...

Me.DataGridView1.Columns(Column Index).Visible = False

 

[ckeezer]

Vis,
I will try that in a min.

Q: Now that I have the search form working, I can filter records by what the user types. The user must select a record in the dg inorder to edit a record, and when they click edit, the record pulls up in my customer form for editing.

P: I cannot update the db. The Customers form was created with the wizard and I am using the update function that was generated for the form. Why would it not be working? I have attached the project for you to take a look at, so I do not have to post a bunch of code.

Thanks

 

[ckeezer]

I tried what u suggested about the datagridview.... here is what my code looks like.. I am still getting errors:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] rbtnCommercial_CheckedChanged([/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] rbtnCommercial.CheckedChanged[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection([/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OleDb.4.0;Data Source="[/COLOR][/SIZE][SIZE=2] & Application.StartupPath & [/SIZE][SIZE=2][COLOR=#800000]"\SmartHouse1.mdb"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Customers where companyname is not null"[/COLOR][/SIZE][SIZE=2], conn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable([/SIZE][SIZE=2][COLOR=#800000]"Customers"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dgv1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataGridView[/SIZE]
[SIZE=2]lblCompanyName.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]lblLastName.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]txtCompanyName.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]txtLastName.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]btnEdit.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]btnAdd.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]adapter.Fill(dt)[/SIZE]
[SIZE=2]dgCompanys.DataSource = dt[/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"CustomerID"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"Title"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"Address"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"City"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"State"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"Zip"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"FaxNumber"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2]dgv1.Columns([/SIZE][SIZE=2][COLOR=#800000]"EmailAddress"[/COLOR][/SIZE][SIZE=2]).Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

 

[vis781]

It's because the default name of the DataGridViewcolumn isn't just the column name, it's your column name then the fully qualified name of the column

dgv1.Columns("CustomerID").Visible = False

The actual name of the above column is

CustomerIDDataGridViewTextBoxColumn

Don't ask me why MS thought this was a good idea. If you do it by column index it's a lot easier..

dgv1.Columns(0).Visible = False

 

[cjard]

As for the direction to take with this now, I suggest that you make a new form but pass it the existing data table and binding source in use on the search form; that way it gets access to the same data and the bindingsource.current property can give access to the chosen row avoiding a requery

 

[ckeezer]

I am still trying to figure out how to update the changes to the database. I understand the basics, now that I have my ADO.NET book from MS, but all of the examples they give deal with one form. I can get it to update all day long on one form... I am passing the info to a new form, but then need to update that same datatable on the previous form inorder to update the db... am I right or is my line of thinking wrong?

I understand how a dataadapter is filled with dataset and how that is fillied via a select statement. I understand how to display in the info on the opposite form, but I cannot find anything close to updating the information in the db with the data on the secondary form.

What is the easiest way to accomplish this task? Everything else I have is working... so far... after this it is onto learning about Crystal Reports!!! Now that I have VS2005

 

[vis781]

All you need is a DataAdapter and a Valid update query and your are on your way. How far have you got with this?

 

[cjard]

I am still trying to figure out how to update the changes to the database. I understand the basics, now that I have my ADO.NET book from MS, but all of the examples they give deal with one form. I can get it to update all day long on one form...

Consider this:

You have form1 and form2
Each form contains bound controls
Controls are bound to bindingsources

You can select the row on form1, this causes form1's bindingsource to move position
Now you can open form2 and pass to it, form1's bindingsource
Form2's binding source can indeed point to another binding source for its data, so why not point it to the one you passed in from form1 ?
After editing the data, call endedit on the binding source and close form2

If form2 was opened modally, then execution of form1 will resume, you can write the changes back to the database using whatever update procedure you have in place on form 1 that works "all day long"


-
The challenge is to get both forms to share the same data. In old VB you'd have shoved everything in a global public static module or something. In OO we dont do that; we pass information around. Form2 needs access to the data, pass it the data (Form2 shouldnt *take* access to the data, it should be given it)