Using a label to load a datagrid

timharroun

New member
Joined
Jan 3, 2010
Messages
1
Programming Experience
Beginner
Hello. I'm using Visual Basic in Visual Studio Express 2008. I'm creating a form that works with an Access 2007 database with 5 tables, though for this only two are important: Customer and Project.

The Customer table has a unique CustomerID auto-number. The Project table describes relationships between two customers. PrimaryID and SecondaryID both correspond to a CustomerID number from the Customer table. Any given ID number can exist as a Primary or Secondary ID, so if "Bob's Company" ID is 1, and "Fred's Company" ID is 2, there can be two projects, one having "Bob's Company" as Primary and "Fred's Company" as Secondary, and the other vice versa.

The user navigates through the Customer table by means of a combo box. They choose a customer name from the box, which populates a label with that customer's ID number. I have a View Projects button with the intent that once the user chooses the company, they can click View Projects and a DataGrid will appear which contains only the records from the Project table in which this specific Customer number appears (either as Primary or Secondary). So basically, with all that explanation, I'm trying to figure out how to get the View Projects button to take the CustomerIDLabel.Text and use that to populate the DataGrid.

I tried using Query Builder:

VB.NET:
SELECT     Project.Project_ID, Project.PrimaryID, Project.SecondaryID, Project.[Terminated?], Project.TerminatedDate, Project.PaymentMethod,
                      Project.PaymentCardOrCheckNumber, Project.PaymentExpDate, Project.ProjectNotes
FROM         Customer INNER JOIN
                      Project ON Customer.CustomerID = Project.PrimaryID
WHERE       Customer.CustomerID = 'CustomerIDLabel1.Text'

But I get an error message "No value given for one or more required parameters." I assume this has to do with the query not recognizing CustomerIDLabel1, because if I put a specific company ID value in the WHERE statement it works (i.e., "WHERE Customer.CustomerID = 649").

I'm a beginner and maybe this is an easy problem, but I've had no luck finding help online, and I've been looking for quite some time. Any help or guidance about how to proceed would be greatly appreciated.

Thank you.
 
Did you try to use that string yourself and concatenate it on the end.
VB.NET:
Dim sql As string = "SELECT Project.Project_ID, Project.PrimaryID," & _
   "Project.SecondaryID, Project.[Terminated?], Project.TerminatedDate," & _
   "Project.PaymentMethod, Project.PaymentCardOrCheckNumber," & _
   "Project.PaymentExpDate, Project.ProjectNotes" & _
   "FROM Customer INNER JOIN" & _
   "Project ON Customer.CustomerID = Project.PrimaryID" & _
   "WHERE Customer.CustomerID ='" &  CustomerIDLabel1.Text & "'"

And of course parametrizating your query is safer.
 
If CustomerId is an numeric data type; why are you treating it as a text value in your where clause?

VB.NET:
WHERE       Customer.CustomerID = 'CustomerIDLabel1.Text'
 
Create the query on your dataTable.
Parameterise your query, for SQL it's something like "WHERE CustomerID = @CustomerID"
^ Your parameter is then @CustomerID
When prompted call it "FillByCustomerID"

I do something similar on my forms, and you call this parameter when you fill your dataTable:
VB.NET:
Me.CustomerTableAdapter.FillByCustomerID(Me.MyDataSet.Customer, Me.CustomerIDLabel1.Text)
 
Rename your "Terminated?" column to be called "IsTerminated"

Never use anything other than letters, numbers and maybe underscores in your column names; no spaces, no hyphens, no weird characters, no characters that are used for wildcards etc
 
Back
Top