Question

This is done in visual basic langauge. I created a query and i want to invoke this query from code.
for example, if i select the Company-owned radio button and hit the display button ONLY the information for company owned stores should be shown. same for the other radio button except it will only show the information for the Franchise ownership.

F. for Franchises
C. for company owned
please provide the appropriate code.
and select statements.

hon Mcescft Vsal Studio ProjectBuild Debug Team Tools Test Analyze Window Help Query Builder Stores Al Columns) Store City 1state .︾l sales Or. A Or.. Filter Sort Order Table Stores Stores Outp Sort Type Colume Store Cty Alias SELECT Store, City, State. Sales, Ownershsp FROM Stores Execute Query OK ze Window Help Start- TableAdapter Query Configuration Wizard Choose Methods to Generate The TableAdapter methods load and save data between your application and the database. Which methods do you want to add to the TableAdapter? Fill a DataTable Creates a method that takes a DataTable or DataSet as a parameter and executes the SOt statement or SELECT stored procedure entered on the previous page Method name FallBy Return a DataTable Creates a method that returns a new DataTable filled with the results of the SQL statement or SELECT stored procedure entered on the previous page. Method name GetData Cancel k Previous Next > Finish Main Form.vb t X Min Form.v Adalene ProjectM daleneDstaSet.asd btnDisplay 6 Option Strict on 7 Option Infer oFF 曰Public Class frwin Private Sub fraWain Load(sender As object, e As EventAngs) Handles MyBase.Load ine of code loads data into the AdaleneDataSet.Stores' table. You can sove, or remove it TODo: This li Me.StoresTableAdapter.Fill(Me.AdaleneDataset.Stores) End Sub Private Sub btntxit Click(sender As onject, e As EventArgs) Handles btnExit.Click 16 nPrivate Sub btntxit Click( onject Me.Close() End Sub 18 ivate sub btrDisplay Click(sender As Object, e As EventArgs) Handles btnDisplay.Click If radA11.Checked Then Stores TableAdapter.FiliAdalenebataSet Stores 22 9 End If 24 25 End Class 26 End Sub

Answer 1

The very first thing is to create the sql query using the query builder for the condition that has been mentioned i.e. what radio button option has been selected before clicking on the display button. We need to pass a parameter to the query that will determine the result set, ,meaning if we want to show only company owned stores then we need to pass the parameter that will filter out the records. The parameter is defined in the where clause with '@' symbol as mentioned below.

SQL QUERY

SELECT Store, City, State, Sales, Ownership

FROM Stores

WHERE Ownership = @Ownership OR @Ownership IS NULL

@Ownership is the parameter that we need to pass to the query to get the desired result set. If we want only company owned stores, @Ownership should be set to 'C'. If we want Franchise owned stores , @Ownership should be set to 'F'. In case we need all records @Ownership should be NULL or Blank.

We can even use LIKE statement instead of '=' when a similar match is expected instead of exact. In that case query would be :

SELECT Store, City, State, Sales, Ownership FROM Stores

WHERE (Ownership LIKE @Ownership +'%' )OR (@Ownership IS NULL)

As shown in the picture before the FillyBy method could be recreated with the SQL Query mentioned above and rename the method as FillByOwnership.

Next step would be to write the code lines that will trigger the query and fill the data adapter accordingly.

So here we need to modify the code in the display button click event handler method as below :

====================VB code =================================================

Private Sub btnDisplay_Click(sender As Object, e As EventArgs) Handles btnDisplay.Click

Try   

If radAll.Checked Then 'when radio button All is checked
       StoresTableAdapter.Fill(AdaleneDataSet.Stores)

   ElseIf radCompany.Checked Then 'when radio button Company Owned is checked
       StoresTableAdapter.FillByOwnership(AdaleneDataSet.Stores , 'C')

Else    'when radio button Franchises is checked

StoresTableAdapter.FillByOwnership(AdaleneDataSet.Stores , 'F')
   End If

Catch Ex As Exception

   MsgBox(Ex.Message)

End Try

End Sub

==========================================End=====================================

The above code would provide an idea how to solve the stated problem.