Solved

Key and Description Values

Posted on 2008-06-23
17
251 Views
Last Modified: 2013-11-26
Sorry if this question seems simple but I am struggling moving from VB6 to VB.NET 2008, but I will eventually get there.

I have a couple of grids that display data from access right now  ms sql later.  The data is from a query and not directly from 1 table.  I allow the user to make changes to the data  how can I update the actual tables.

One table has key values only and the other tables have the key values and names.

So I allow the user with a combo box to select the names in the grid and I now want to write back to the database the key values.

For an example I will use vehicles.

Table1  contains key values only

PrimaryKey
ColorKey
ModelKey
MakeKey

I then have supporting tables for Color, Make, and Model that contains Keys and descriptions.

Thanks for the help



0
Comment
Question by:wsadfilm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21849098
If you are asking how to get the selected key from the ComboBox, you can using the DisplayMember to set the column for the display text, and ValueMember to the key column.  Then, you can get the SelectedValue to get the key information for the selected entry.
0
 

Author Comment

by:wsadfilm
ID: 21853417
My question isnt so much how to get the key values from the combo boxes  I have that.  It is what is the best practice in updating / writing back to the database.

Since my datatable is based off  of a query and not the actual tables  updating that shows the information in the grid for the user but it does not get updated in the database.

Do I open another datatable and add or update records in that and update the information to the database and if so how do I update my existing query datatable?

I am doing something like the below to update the data in the grid.

        dv.AllowNew = True
        Dim drv As DataRowView = dv.AddNew()
        drv("Color") = cmbColor.Text
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21854109
Ok, how are you getting data from the database?  Are you using something like an SqlDataAdapter?
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:wsadfilm
ID: 21854184
I am using a Table Adapter to fill the dataset Table and then using a dataview for my binding.

        TableAdapter.Fill(DataSet.Table)
        DV = New DataView(DataSet.Table)
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 21854203
If you are using a TableAdapter to fill the typed DataSet, then you should use the Update method to write the changes back to the database:

TableAdapter.Update(DataSet.Table)
0
 

Author Comment

by:wsadfilm
ID: 21854274
When I try that I get update is not a member of ...

I think it is because the dataset.table is based on a query linking several tables and not just a single table.

Example:
SELECT tblMain.Key, tblColor.ColorName, tblMake.MakeName, tblModel.ModelName
FROM ((tblMain INNER JOIN tblColor ON tblMain.ColorKey = tblColor.ColorKey) INNER JOIN tblMake ON tblMain.MakeKey = tblMake.MakeKey) INNER JOIN tblModel ON tblMain.ModelKey = tblModel.ModelKey

In VB.NET 2008 IDE I click on Data Sources and then Edit Dataset With Designer I can add a TableAdapter and a Query.  What I added does not have an update command  only a Select Command.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21854710
Does the table element have a Primary Key identified with a 'key' symbol in the designer?  If it doesn't, the designer will not be able to create an UpdateCommand from the SelectCommand query.

What I usually do is to set up a 1:1 correspondence between the tables and the TableAdapters, and then define a relationship between them.  Add a TableAdapter for tblMain, and another for tblModel, and then create a relationship between them on ModelKey.
0
 

Author Comment

by:wsadfilm
ID: 21855062
First Thank You for your help so far&

All of my tables have a Primary Key identified; I also have a few queries setup and I have even setup a primary key on them by right clicking and selecting Set Primary Key in the designer.

When I right click on my query and go to configure& I see my sql statement and I can click on advanced options.  I check Generate Insert, Update and Delete statements and then click OK.

Back at the Table Adapter Configuration Wizard I click Next and I can choose which methods I what to add to the TableAdapter.  The last method is for updates but it is greyed out.

The above is all for the queries.  If I look at my actual tables I have an Update command for each of them tblMain, tblColor, tblMake, tblModel

I have attached a couple of screen shots in a pdf.
ScreenShots.pdf
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21856192
1) You didn't show a screen shot from the results part of the wizard that indicates what gets generated:  Select,  Update, Delete, etc.

2) Just because your tables have a primary key doesn't mean that the DataSet designer will recognize them.  Once the TableAdapter is created, there should be a 'key' symbol for the table element indicating the primary key column.
0
 

Author Comment

by:wsadfilm
ID: 21856472
I have attached a couple additional screen shots.

1) Will show the designer window.  vMain is what I am using dataset.vMain
2) Will show the final step on what is being created.
ScreenShots1.pdf
ScreenShots2.pdf
0
 

Author Comment

by:wsadfilm
ID: 21856510
I know that I may be going about this wrong and so if my approach is incorrect please let me know.  

I am not sure what the best practice is or the proper way of handling data in VB.NET.  I would consider myself a pro when it comes to VB6 and ADO but I am completely lost with .NET.

I can not believe it is taken me so long to change some of my habits and to learn .NET

Thanks Again
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21856597
The designer can see the primary key, but it still isn't generating an UpdateCommand, DeleteCommand, and InsertCommand for the internal adapters.  

What is the datatype type that you are working with?
0
 

Author Comment

by:wsadfilm
ID: 21857200
I am not sure what you mean by the datatype.

Just for my own knowledge, you don't think because vMain is a query instead of table has nothing to do with this.

Each of the individual tables have insert, update, and delete commands just not vMain.
0
 

Author Comment

by:wsadfilm
ID: 21857593
If I do this it will work.  Is this the best practice?

        Me.TblMainTableAdapter.Fill(Me.TestDataSet.tblMain)
        TestDataSet.tblMain.Rows(0)("ColorKey") = 1
        TblMainTableAdapter.Update(TestDataSet.tblMain)
        Me.VMainTableAdapter.Fill(Me.TestDataSet.vMain)

I am opening up tblmain and updating the records directly then calling the update command and finally re-populating my query.
0
 

Author Comment

by:wsadfilm
ID: 21857826
I think this PAQ is similar to my problem.  I don't quite understand the solution though.

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22751986.html
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 21861540
What don't you understand?  What you showed above it perfectly fine.
0
 

Author Comment

by:wsadfilm
ID: 21861819
Thanks for your help and pointers things are working - at least this part.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

742 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question