Query WHERE = value in a textbox

[John Cassell]

Hi There,

Can someone tell me how to execute this query please..

SELECT [Job Number], [Order Number], [Start Date], [Tank Number], [Completed Date], Customer, [Labour Charge], [Materials Charge], [Materials Cost],
[Invoice Number], Locked, LockedByUser
FROM Jobs
WHERE ([Job Number] = 'Job_NumberTextBox')

'' I have a textbox called Job_NumberTextBox and I would like the query to only load the job which is in the text box. If I swap 'Job_NumberTextBox' for the number 1 in the WHERE statement and execute the query it will return job number 1 but If i put 1 in the text box and then run the query it returns nothing..

Can someone please help?

Thanks

John

 

[cjard]

You havent said which database system youre using..

If you read the DW2 link in my signature, it will tell you this, including showing you how to make a parameterized query

See the section on Fetching Data into Your Application


ps, i just know some bright spark is going to post an example that says: "SELECT ... FROM .... WHERE [Job Number] = '" & Job_NumberTextBox.Text & "';"
All i can say to this is no, no, no, never ever ever do that. Thats how we did it in the '80s and nobody looks like Hasselhof any more either! (There are NO good reasons for writing sql code like this. Please dont do it)

 

[John Cassell]

I am using an Access database.

Every post I have seen has described the method you told me not to do so thanks for the heads up and I will look through your link.

Thanks

John

 

[John Cassell]

Hi Again,

I am really struggling with this. I cannot see anything in your link which refers to my problem and as I say everything else refers to the 80's method.

Would appreciate if you could provide any other info.

Thanks

john

 

[Arg81]

in your query you will use a parameter. In SQL this is using @

I think in Access it is ?

I.E, you add a query to your tableAdapter like:

SELECT [Job Number], [Order Number], [Start Date], [Tank Number], [Completed Date], Customer, [Labour Charge], [Materials Charge], [Materials Cost], 
[Invoice Number], Locked, LockedByUser
FROM Jobs
WHERE [Job Number] = ?

When it comes to name queries, give them some logic - don't just leave as FillBy() and GetBy() - for the example above I'd name FillByJobNumber and GetByJobNumber

Now on your form when you call this query and it's FillBy() method, you also provide the parameter.

In most cases, you would have a textbox and a button. Your textbox is where you enter this "parameter" value, and upon pushing the button loads the data based on this value.

Private Sub button1.click(........)
 
me.[B]TableAdapter[/B].FillByJobNumber(me.[B]dataset.datatable[/B], [COLOR=olive]me.textbox1.text[/COLOR])
 
End Sub

So, the text in green is that of the parameter passing across to the dataAdapter to use to fill the dataTable.
The words in bold are those which you will need to replace with your equivilent values.

Hope you understand that and can follow it though OK. I'm not too sure on the symbol needed for Access parameters, I think it's ? but am not 100% sure - personally I work on SQL databases!

 

[John Cassell]

Excellent,

Thank you so much. Worked Perfectly first time. Really appreciate that!!

and thanks again to Cjard for your input.

Cheers

John

 

[cjard]

The relevant article was:

http://msdn2.microsoft.com/en-us/library/hbsty6z7(VS.80).aspx

Somewhere else in there it also details some info like what vbnewb posted.. namely that you can go to the dataset designer and add a query to the tableadapter. You will probably find the two routes achieve the same end.

I personally use VBNewBs method!

 

[Arg81]

glad to be of help

I love this forum, I had no programming experience and had to self teach ADO.Net and VB.net (2003 first) and this place has been my shrine of knowledge!!!

 

[cjard]

VBNewB has a name change. I had a fiver on VBOldB, you goon! Youre buying the next round..

 

[Arg81]

haha! Needed something I could remember and something that had no relevance whatsoever to VB!