0

I'm very new to programing. I'm using Visual Basic 2010. I have an Access data source named CoreDataSet, it has three tables: Rates, Vendors, Zones. I have binded the Vendors table to two Combo boxes, when a Vendors' Name is selected the first three digits of their zip code appears in a label next to the corresponding combo box. I want to take these two labels (or the two 3 digit zipcodes) and cross reference to the "Zones" table. The zones table has a list of three digit zip across the top (column names) and three digit zipes in the second column and looks like the following (example):

ID| ZIP | 020    |  021   | 022 ...
--+-----+--------+--------+-----------
1 | 020 | ZONE 1 | ZONE 2 | ZONE 3 ...
2 | 021 | ZONE 2 | ZONE 1 | ZONE 3 ...
3 | 022 | ZONE 3 | ZONE 2 | ZONE 1 ...

So shipping from zipcode 022 to 021 would be ZONE 3, and 022 to 022 would Zone 1. The goal is to select two Vendors (one in each combo box) and the form display in a label 1 and 2 their 3 digit zips (done) and the UPS zones applicable to them in label 3 (not done). Is cross referencing even possible? Please help, Thanks.

I got half of it to work. I've done a query (QueriesTableAdapter.query1)as follows:

Select [020]
From Zones
Where (Zip = ?)

My VB code is:

Private Sub Button2_Click(ByVal...blablabla)
    Dim BTR As CoreDataSetTableAdapters.QueriesTableAdapter
    BTR = New CoreDataSetTableAdapters.QueriesTableAdapter()
    Dim returnValue As String
    Dim zip1 As String
    zip1 = Val(Label2.text)
    returnValue - CTYPE(BTR.query1(zip1), String)
    Label3.Text = returnValue
End Sub

This will select the zone from 020 shipping to whatever zip that is entered into label2, and it works fine. However, I need to select various "ship froms" not just 020... 

Select [" + Label1 + "]
From Zones
Where (Zip = ?)

does not work. How do I use a parameter in the select clause or get it to recognize the value of Label1 as the column to select?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1681199
  • 1
  • 2

1 Answers1

0

Basically you want a query like so:

SELECT zones.[020]
FROM zones
WHERE zones.[zip]="021"

Now all you have to do is tun the query against a connection substituting the fields (columns) and values with the labels.

For example:

s= "SELECT zones.[" + Label1 + "] FROM zones WHERE zones.[zip]='" + Label2 + "'"

If label2 can be edited, you will need a parameter query.

Your table design is a touch strange.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • When I substitute "zones.[020]" with "[020]" it works. When I use every combination of " [" + Label1 + "] " it doesn't. How do i get it to understand it's a variable? – user1681199 Oct 01 '12 at 20:02