OleDBDataAdapter Commands problem - Xtreme Visual Basic Talk

05-29-2009, 12:20 AM

sgm

Junior Contributor

Join Date: Aug 2003

Location: AlBayda, Libya

Posts: 300

OleDBDataAdapter Commands problem

Hi
I'm trying to insert and update from a table in MSAccess I've declared everything: Connection, DataAdapter and Commands.
I didn't use the CommandBuilder instead I supplied my own Queries.
My problem is if I add three rows to my table all the three rows in my database have the same values from the first row. It seems like the parameters are stuck on the values from the first row.
how do I overcome this.
here is parts of the code

Code:

'This code 
Private Sub FillingOrderDetailsGrid(Optional ByVal OrderNo As Integer = 0)

        Conn.ConnectionString = My.Settings.CnnStr
        Dim cmdSalesOrderDetails As New OleDbCommand
        cmdSalesOrderDetails.Connection = Conn
        cmdSalesOrderDetails.CommandText = "SalesOrderDetailsByOrderNo"
        cmdSalesOrderDetails.CommandType = CommandType.StoredProcedure

        Dim parm As New OleDbParameter()
        parm.ParameterName = "@SalesOrderNo"
        parm.Direction = ParameterDirection.Input
        parm.OleDbType = OleDbType.Integer
        parm.Value = OrderNo
        cmdSalesOrderDetails.Parameters.Add(parm)
        daSalesOrderDetails.SelectCommand = cmdSalesOrderDetails

        cmdSalesOrderDetails = New OleDbCommand
        cmdSalesOrderDetails.Connection = Conn
        cmdSalesOrderDetails.CommandText = "UpdateSalesOrderDetailsByOrderNo"
        cmdSalesOrderDetails.CommandType = CommandType.StoredProcedure
        daSalesOrderDetails.UpdateCommand = cmdSalesOrderDetails
        With daSalesOrderDetails.UpdateCommand
            .Parameters.Add("@MealNo", OleDbType.Integer, 4, "MealNo")
            .Parameters.Add("@SalesOrderNo", OleDbType.Integer, 4, "SalesOrderNo")
            .Parameters.Add("@MealQuantity", OleDbType.Integer, 4, "MealQuantity")
            .Parameters.Add("@Price", OleDbType.Currency, 8, "Price")
            .Parameters.Add("@StartTime", OleDbType.Date, 4, "StartTime")
            .Parameters.Add("@FinishTime", OleDbType.Date, 4, "FinishTime")
            .Parameters.Add("@IsReady", OleDbType.Boolean, 1, "IsReady")
            .Parameters.Add("@IsPrinted", OleDbType.Boolean, 1, "IsPrinted")
            .Parameters.Add("@EmployeeNo", OleDbType.Integer, 4, "EmployeeNo")
        End With

        cmdSalesOrderDetails = New OleDbCommand
        cmdSalesOrderDetails.Connection = Conn
        cmdSalesOrderDetails.CommandText = "InsertSalesOrderDetails"
        cmdSalesOrderDetails.CommandType = CommandType.StoredProcedure
        daSalesOrderDetails.InsertCommand = cmdSalesOrderDetails
        With daSalesOrderDetails.InsertCommand
            .Parameters.Add("@MealNo", OleDbType.Integer, 4, "MealNo")
            .Parameters.Add("@SalesOrderNo", OleDbType.Integer, 4, "SalesOrderNo")
            .Parameters.Add("@MealQuantity", OleDbType.Integer, 4, "MealQuantity")
            .Parameters.Add("@Price", OleDbType.Currency, 8, "Price")
            .Parameters.Add("@StartTime", OleDbType.Date, 4, "StartTime")
            .Parameters.Add("@FinishTime", OleDbType.Date, 4, "FinishTime")
            .Parameters.Add("@IsReady", OleDbType.Boolean, 1, "IsReady")
            .Parameters.Add("@IsPrinted", OleDbType.Boolean, 1, "IsPrinted")
            .Parameters.Add("@EmployeeNo", OleDbType.Integer, 4, "EmployeeNo")
        End With

        cmdSalesOrderDetails = New OleDbCommand
        cmdSalesOrderDetails.Connection = Conn
        cmdSalesOrderDetails.CommandText = "DeleteSalesOrderDetailByOrderNo"
        cmdSalesOrderDetails.CommandType = CommandType.StoredProcedure
        daSalesOrderDetails.DeleteCommand = cmdSalesOrderDetails
        With daSalesOrderDetails.DeleteCommand
            .Parameters.Add("@SalesOrderNo", OleDbType.Integer)
        End With

All queries are in MSAccess
I'm using a DataTable with my DataAdapter since I'm dealing with a single table.
I'm filling the table with a parametrized query, and updating it with the same dataadapter.
I'm adding rows to my datatable and manipulating the table through code. I know that my data is there and every row has it's own values, I can see them when I debug my program.
I'm missing something with the parameters and I don't know what it's.
Does anybody have an idea.
thanks

Last edited by sgm; 05-29-2009 at 12:25 AM.

05-30-2009, 08:11 AM

sgm

Junior Contributor

Join Date: Aug 2003

Location: AlBayda, Libya

Posts: 300

I've found the solution
for some strange reason if you choose to put your query in msAccess your parameters will get stuck on the first set.
I moved the query to my VB code and changed the command type to text and everything worked without any problems.
The moral is if you want to use SPs use SQL server and forget MSAccess and OleDB.
Cheers