How to get a DataTable using parameters?

[tombihn]

I saw another post where jmcilhinney graciously helped another programmer with fixing a coding issue and suggested using parameters. The link to his blog in his post was very helpful and I'm trying to change my code in to use parameters instead of the previous method. In my previous method, I concatenated the sql query and included the dtBeginDate variable in the query. It is querying an Access database. The method I was using involved creating a dataadapter object from this query then filling my DataSet, ds, with this table called "WorkOrders". I never do an update with this data. The purpose is only to get the data from the database.

My question is how can I use a command object to pass a parameter and add a table to the dataset. Below is the code I have so far, but I'm stuck trying to figure out where to go from here.

            Dim dtBeginDate As Date = Today().Date.AddYears(-1)
            Dim SQL_RecentWorkOrders As String = "SELECT workorders.CustID as WO_CustID, " +
                                                        "workorders.[Work Order Number] as WO_Number, " +
                                                        "workorders.Date as WO_Date, " +
                                                        "customer.lastname as CustLastName, " +
                                                        "workorders.OperLast, " +
                                                        "workorders.OperFirst, " +
                                                        "workorders.FieldID as WO_Desc, " +
                                                        "workorders.Acres, " +
                                                        "workorders.Grid " +
                                                 "FROM   workorders " +
                                                 "INNER JOIN customer " +
                                                 "ON     workorders.custid = customer.custid " +
                                                 "WHERE  workorders.Date> @BeginDate ORDER BY workorders.[Work Order Number] DESC"

            ' Old code:  daAccess = New OleDbDataAdapter(SQL_RecentWorkOrders, cnAccess)
            ' Old code:  daAccess.Fill(ds, "WorkOrders")

            Dim cmd As New OleDbCommand(SQL_RecentWorkOrders)
            cmd.Parameters.AddWithValue("@BeginDAte", dtBeginDate)
            cmd.ExecuteNonQuery()
            ds.Tables.Add(....

How do I get a table into my DataSet at this point?

 

[JohnH]

daAccess = New OleDbDataAdapter(SQL_RecentWorkOrders, cnAccess)

When you create the adapter like that the select command object is initialized (first parameters is selectCommandText), you can then get that from SelectCommand property and attach the parameters by its Parameters property. See OleDbDataAdapter.SelectCommand Property (System.Data.OleDb)

 

[tombihn]

Thanks for the quick response JohnH.

Here's what I have so far:

            Dim daAccess As New OleDbDataAdapter()
            Dim dtBeginDate As Date = Today().Date.AddYears(-1)
            Dim SQL_RecentWorkOrders As String = "SELECT workorders.CustID as WO_CustID, " +
                                                        "workorders.[Work Order Number] as WO_Number, " +
                                                        "workorders.Date as WO_Date, " +
                                                        "customer.lastname as CustLastName, " +
                                                        "workorders.OperLast, " +
                                                        "workorders.OperFirst, " +
                                                        "workorders.FieldID as WO_Desc, " +
                                                        "workorders.Acres, " +
                                                        "workorders.Grid " +
                                                 "FROM   workorders " +
                                                 "INNER JOIN customer " +
                                                 "ON     workorders.custid = customer.custid " +
                                                 "WHERE  workorders.Date> @BeginDate ORDER BY workorders.[Work Order Number] DESC"

            Dim cmd As New OleDbCommand(SQL_RecentWorkOrders, cnAccess)
            cmd.Parameters.AddWithValue("@BeginDAte", dtBeginDate)
            daAccess.SelectCommand = cmd

I am still struggling to how to add this into a dataset table. Any help will be appreciated.

EDIT:
Immediately after posting this, I realized this was very close to my original path. I added the following and it worked!

daAccess.Fill(ds, "WorkOrders")