I have an Access Database which I'll call David.mdb, behind my Visual Studio 2008 front end. It has Just one Table called "Product and Uses". The table has a number of fields. Product Name, Under 12 Months, Over 12 Months, etc. [The Products are suitable for different ages]. On Form1, I have a text box with 4 buttons (First,Last,Next,Previous). I have OleDbDataAdapter1, OleDbConnection1 and DsProducts1 associated with the form. The Textbox named txtProducts is databound to DsProducts1-Products and Uses-Product Name. The following code works fine, it loads the form with the first product and cycles through the product names.
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DsProducts1.Clear()
OleDbDataAdapter1.Fill(DsProducts1)
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
txtProduct.BindingContext(DsProducts1, "Product and Uses").Position = 0
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
txtProduct.BindingContext(DsProducts1, "Product and Uses").Position = Me.BindingContext(DsProducts1, "Product and Uses").Count - 1
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
txtProduct.BindingContext(DsProducts1, "Product and Uses").Position -= 1
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
txtProduct.BindingContext(DsProducts1, "Product and Uses").Position += 1
End Sub
My question is that I want to write Queries so that only those product Names that are suitable for a particular age appear.
I can write them using the Query builder
SELECT [Product Name]
FROM [Product and Uses]
WHERE ([Over 12 Months] = true)
From the Query Builder I can execute SQL and it works fine but I don't know how to write that Query as workable code in the form.
Hope this makes sense.
Regards,
David
Loading

Abhimanyu K VatsaPosted Aug 7, 2010, 6:26 AM
If you are working with such a high level project using VS you should know that database rule say that database table name will never contain any space and so on. Correct it first to get right back. We have multiple option to make it possible.
You can compare both years like user's dob year to current year OR you can use the
MONTHS_BETWEEN(current_date,dob)
in your sql query.
I hope my post will help you.
thanks
David CarterPosted Aug 14, 2010, 3:37 AM
An OleDBDataAdapter was added to the form and a Dataset was generated.
OleDbDataAdapter.SelectCommand.CommandText = "Enter your query here"
Eg. I want to list those products that are suitable for children over 10 years and over 40Kg.
OleDbDataAdapter.SelectCommand.CommandText = "Select ProductName From Product_and_Uses Where(Over10Years = True) and (Above40Kg = True)"
and then use the following to fill the databound control with the result of the query
DataSet.Clear()
OleDbDataAdapter.Fill(DataSet)
Regards,
David
Abhimanyu K VatsaPosted Aug 12, 2010, 1:43 AM
How socking your photograph is? I will love to see it? upload it plz
thanks
David CarterPosted Aug 11, 2010, 9:15 PM
I have now uploaded it and it should appear soon.
D.
Abhimanyu K VatsaPosted Aug 11, 2010, 2:24 AM
Thanks for Accepting as Answer.
Why you are not using your photograph in your profile.
Thanks
David CarterPosted Aug 7, 2010, 7:19 AM