How to get a DataTable using parameters?

tombihn

Member
Joined
Jan 17, 2011
Messages
10
Programming Experience
5-10
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

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,605
Location
Norway
Programming Experience
10+

tombihn

Member
Joined
Jan 17, 2011
Messages
10
Programming Experience
5-10
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")
 
Top Bottom