Question How to use a form control to provide a query variable?

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
I am new to Visual Studio.

I have a VS project connected to a Microsoft Access 2007 database (flex.accdb). I am using VS 2005. Old but good!

I have created a windows form (form1) that includes a control that contains the network user name (from environment.username). Form1 also contains a button. On clicking the button a second form opens (form2) that contains a datagridview which is bound to a view from the data source. One of the columns in this view is the user name.

What I want to do is filter the datagridview to show only the records containing the user name displayed on form1.

In QAccess this would have been a doddle. But I cannot get it to work in Vusial Studio 2005. All your ideas and help gratefully received.
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
It doesn't matter. The only user to see form2 will be the logged on user. So the only data they will retrieve, from either the dataset or the database will be their own records. If one way is quicker than the other then we should venture down the quickest I guess.

Thanks
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
It does matter. The two are quite different so you need to decide which way you want to go first. The first option means that you will definitely retrieve all the data but you will never retrieve any data more than once, while the second option means that some data may never be retrieved but some may be retrieved more than once. Which is preferable depends on how much data there is and how it will be used.
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
I think I need to explain what I am trying to do. The system I am developing is a personnel time-recording system. The datagridview in form2 is populated from a query (
SELECT tblTimeData.MnthID, tblPersonnel.UserN, tblTimeData.DoM, tblTimeData.AMon, tblTimeData.AMoff, tblTimeData.PMon, tblTimeData.PMoff, tblTimeData.Adj, tblTimeData.AbsCodeID, tblTimeData.Notes
FROM tblPersonnel
INNER JOIN (tblMnth INNER JOIN tblTimeData ON tblMnth.MnthID = tblTimeData.MnthID)
ON tblPersonnel.UserID = tblMnth.UserID
WHERE (((tblMnth.Submitted)=False))
ORDER BY tblTimeData.MnthID DESC , tblPersonnel.UserN, tblTimeData.DoM
;). This selects all the data from tblTimeData that is still 'live', i.e. the employee can still enter and amend times. So, this query selects ALL records in tblTimeData that are 'live' regardless of who the employee is.
What I want to achieve is to filter the above query to only show records on form2 that are attributed to the employee logged-on to the system, i.e. environment.username by filtering on tblPersonnel.UserN. The logged-on user name is present in a control on form1.

Having written all this I think I have grasped what you mean and believe I should be picking option 1. Am I right?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
It actually sounds like you should be going for option 2. There's no point getting all the data if you know for a fact that the user will never view most of it. So, basically, you need to add a parameter to the WHERE clause that lets you filter at the database.
VB.NET:
SELECT SomeColumn
FROM SomeTable
WHERE SomeOtherColumn = @SomeParameter
You then set the value of that parameter in your VB code.
VB.NET:
myCommand.Parameters.AddWithValue("@SomeParameter", someValue)
You can add as many parameters as you need, but it seems like you only need one.
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
I have tried setting a parameter, but encountered problems with the syntax. The reference literature doesn't help by referring to @ as a parameter symbol for SQL Server data sources and ? for Ms Access sources. My data source is Ms Access so I tried using ? but to no avail.

Maybe I'll try @ to see if anything changes.

Thanks for the help so far.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
You can user either, depending on the circumstances. Telling us that you encountered an error without telling us exactly what you did and what the error message was doesn't help you or us.
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
Sorry, been away from the PC. Also sorry but I went off at a tangent and didn't really think through your advice.

Can we go back a step or two please? My database is in MS Access. What I am struggling with is how to set a parameter on a query that is in the database itself. Should I be thinking of creating the query within VS?
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Read the DW2 link in my form, section "Creating a Form to Search Data"

It should have access as well as sqlserver examples. If there are only sqlserver examples, replace the @parameter with ? viz

SELECT * FROM tbl WHERE col = @param
SELECT * FROM tbl WHERE col = ?

when you design your query. The rest of the stuff you dont have to worry about wiring up, because the IDE does it for you
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
Thanks CJARD. I have tried this already but got bogged down with the parameter. In my case the form control already has a value that I want to pick up and use as the parameter, but I cannot get the control.value into the parameter. That's the vital bit that I am struggling with.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
It's a simple assignment of one property to another, e.g.
VB.NET:
myCommand.Parameters.AddWithValue("@SomeColumn", myControl.Text)
VB.NET:
myCommand.Parameters("@SomeColumn").Value = myControl.Text
One point to note is that the value you assign must be the correct type for the parameter. In the examples above, I have assigned the Text property of a control, which assumes that parameter should be a text type. If it's not then you must convert the data you have appropriately. For example, if your parameter expects a number and you have the Text of a TextBox then you must convert the String to a number first and then assign it to the parameter.

If you're using TableAdapters then you don't even have to do that. The IDE generates methods where each method parameter corresponds to a query parameter. For instance, if you have a query:
VB.NET:
SELECT * FROM MyTable WHERE ID = @ID
then that will correspond to a FillBy method with a parameter for the ID. You simply call the method and pass your parameter value as an argument, e.g.
VB.NET:
myAdapter.FillByID(myDataTable, CInt(myControl.Text))
 

PhilC

Member
Joined
Sep 15, 2010
Messages
7
Programming Experience
Beginner
I seem to keep getting an error when configuring the TableAdapter. Mty query is

SELECT UserID, UserN FROM tblPersonnel WHERE UserN = @UserParam

and the error is:

Error in WHERE clause near '@'. Unable to parse text.

Does this mean that a parameter cannot be a string?
 
Joined
Oct 7, 2010
Messages
7
Programming Experience
1-3
Hi,

I normally don't blind any of my data to a control, I use code to add my rows to my data grid.

Why not show us how you are blinding your data to your control and we can suggest the filtering.

Jim
 
Top Bottom