Probably asked 1000 times...

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Hi Guys,

I'm new to this and I have searched so please pardon my ignorance if this question has been asked in the past...

I'm developing an internal system that accesses SQL server 2000 via ADO.net.
I've got my head round ADO.net pretty much so now, and my main difficulties are coming from VB.net...

For my app I want to build a similar app to that of MS Access - i.e. a form with a subform showing related data to a certain field on the main form.
I understand that subforms do not exist in VB.net, and that the way forward is DataGrids.

The problem is my database has a lot of lookup fields, i.e. EmployeeID, CustomerID, WorkByID, CreatedByID, etc etc, and in a DataGrid appears as a number, which to the users means nothing...

Now, is there a way of creating a form to a form, i.e. linked, that the first form has all of the Customer Details (I know how to use ComboBoxes to display the relevant fields from the lookup tables) and then call a form which brings up the matching Requests for that Customer, again with ComboBoxes replacing the ID fields.

either that or is there a way of editing a DataGrid so that it will show foreign key information in those fields instead?

Thanks for the help guys,

Luke
IT Support Technician
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
You could use the datagrid event currentcellchange to get the id datagrid.item(datagrid.currentrowindex,0) where 0 is the collumn number of the id. Then use that id to load another datagrid.

TPM
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Try Inner Join

Me.SqlSelectCommand2.CommandText = "SELECT Part.[Part ID], Part.[Part Number], Part.Revision, Detail.[Assy Part ID], " & _
"Detail.[Detail Number], Detail.[Detail Part ID], Detail.[Qty per Assy], Detail.R" & _
"eport FROM Detail INNER JOIN Part ON Detail.[Detail Part ID] = Part.[Part ID] " & _
"WHERE (Detail.[Assy Part ID] = @partid)order by Detail.[Detail Number]"

I modified the select statement and schema to include the field my id field references. In this case "part id" means nothing - "part number" is what everyone recognizes. Be careful not to update the insert, update and delete statements. I do it through the SQL wizard twice. Once with the join to get the select statement, and a second time to get the insert, update and delete statements. Does that make sence?

In the datagrid I just 'hide' the id fields and 'show' the desired fields (.visible = false).

Hope this helps...
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Hi David,

Yep that makes sense, I didn't realise that a datagrid could be program coded - I'm still new to this!!

The only thing I don't understand is your bit about the updates - there will be a need for the user to add new child rows - but I'd also need them to be able to make a selection, that's why I thought a seperate form with combo boxes would be a better approach....

thanks for the info
Luke
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Right I've been playing about all morning and I've managed to get your code converted to what I require and then generate a DS and bind a DataGrid to it...

Now the bit I can't figure out is I want a form with a Combo box selecting Customers. In the datagrid I've just created, how do I set it so that I can have the child data shown for the selected customer in the combo-box?

I'm still hoping there is a way to create a sub-form type system (or a linked form I should say) that has data from table1 on form1 and data from table2 on form2 and the two forms relationally linked by the CustomerID field.

If someone can point me in the right direction of doing things that way, I will be very very very grateful! (I'd prefer to create this app within VB.net instead of Access Project)...

Cheers guys,
Luke
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Here ya go

I take the selected id and store it in a public variable. Add a Module to your project called 'PublicVariables'. Add this statement in the module.

Public iCurrentPartid As Integer

this statement and module makes this variable 'iCurrentPartId' available to all forms in the project.

On FORM1 set iCurrentPartid to the selected grid row id value.
iCurrentPartid = DetailGrid.Columns("detail part id").Value

call FORM2

use an SQL adapter and generate a select statement with a
"WHERE (Detail.[Assy Part ID] = @partid)" clause in it
when using the sql wizard enter "=@partid" in the criteria column of the query builder

then on form2 you can set the parameter to the public variable and load the correct data.

SqlSelectCommand1.Parameters("@partid").Value = iCurrentPartid
Try
Me.LoadDataSet()
Catch eLoad As System.Exception
System.Windows.Forms.MessageBox.Show(eLoad.Message)
End Try

then bind whichever controls you want to this dataset of just the records that match the Form1 part id

hope this helps
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Sweet...

Very good, let me know how it works. Paszt has been a huge help in my development, his code is real sharp, and he knows his stuff.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Hi David,

hmmm....After reverse engineering the whole code, I got the program to a point where it loads up the initial grid screen.

I noticed Paszt basically used the windows form wizard to create the two forms then add the rest of the code - I had to check as for some reason my data isn't loading into the dataset. I've created the BusinessClass module with a connection to my SQL database on my desktop, an SQL DataAdaptor with the three tables I'm pulling info from, I've generated a dataset based on the DataAdaptor, and also added a DataSet to the BusinessClass module to the typed dataset mentioned above.

Your program had no connection to any databases so I assume my problem is a bit of code that hasn't initialised the dataset correctly.

I created another form from the DataForm wizard and have checked through everything in the LOAD and FILL dataset sections, and it matches word for word what I have set in this program.

Any help much appriciated!!!
Luke
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Two things i'm thinking it may be...

1. what's the use of having a dataview in the BusinessClass module? I haven't figured out what this does yet....

2. when I connect to the SQL server DB with the Form Wizard, it uses OleDBConnection instead of SQLConnection. I've set mine up with SQLConnection...

I know I'm replying to myself, have a tendancy of posting when a thought pops in my head so that I know I've got it covered!
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
The dataview in the BusinessClass module is there so it can be shared by all forms in the project, sorted and filtered and altered and still be used by all forms. Once the dataview is loaded, in the main form, all forms can access the dataview without reloading a dataset of the same thing on all forms. Example: 3 forms use the PART dataset. You could use 3 datasets, one on each form, of the same table to fill 3 different grids. Or you could load the dataset once set it to the dataview in the BusinessClass and bind all 3 grids to the dataview.

I use the dataform wizard also. The first thing I do after completing is delete the connection, adapter and the dataset. Then I drag an SQL adapter onto the form and run through the SQL setup wizard. There I can add selection criteria and sort order. Then I replace all OleDB with SQL in the code.
Then I update the datafrom wizard's reference to objdsPart to 'dsPart1' (or just 'dsPart' in some spots) - dsPart1 is the dataset name attached to the sql adapter.
I copy all the load, update, and delete code and save it in notepad somewhere. Almost every form I create uses this code - only the dataset name changes.
Then I double click on the form header to open a form load event and copy the code for the LOAD button event. That way the dataset loads on form creation. Sometimes I have to set any SQl parameters just before the try-load statement. I have about 45 forms in my project - still growing - and 90% of them were created in this way. Cut, Copy, and Paste are a programmers best friend...

PS I don't use the Business Class Example, I had way to many datasets going on And it was less confusing for me to have them on the form that uses them.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Cheers David I'll have to have a go at doing it that way later! I may just start afresh instead of editing everything that was in Paszt example.

I've also downloaded Tier Developer. Couldn't get my head around it first, but once I've let it create all my forms and links I've then opened the project in VB.Net to edit all the cr*p and make it look quite peachy...
good bit of software.

Thanks for your help, you are one of those invaluable developers that will help to the end, too many a time I've come across people not willing to help out but instead hint as they don't want to share what they have learnt.

Thanks again!
Luke
 
Top Bottom