Probably asked 1000 times...

[Arg81]

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]

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]

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]

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]

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]

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

 

[Arg81]

cool, I'll give that a try in a minute.

I've actually found another post that you started - MyPDM??

I've taken the download from that and reverse-engineered it, the example in that is very similar to what I'm trying to do!

thanks
Luke

 

[DavidT_macktool]

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]

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]

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]

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]

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