none
What is wrong with my "Update" procedure?? RRS feed

  • Question

  • I have struck with this for two days and I still can't get it work. The code is not breaking. When I execute the procedure with a control event, it did not have any problem but I checked the database and it did not update the database. Here is the code......

     

    Sub UpdateSales(ByVal InvoiceID As Integer, ByVal CustomerID As Integer, ByVal Region As String, ByVal SalesDate As Date, ByVal ShipDate As Date, ByVal GSTExempt As Boolean, _

    ByVal Salesman As String, ByVal ShippingCost As Double, ByVal ShippedTo As String, ByVal ShippedToAddress As String, ByVal ShippedToCity As String, _

    ByVal ShippedToPostalCode As String, ByVal GST As Double, ByVal Net As Double, ByVal Gross As Double, ByVal Contact As String, ByVal Status As String, ByVal LastUpdatedOn As Date)

    ' Open Connection

    Dim cnJetDB As OleDbConnection = New OleDbConnection(connectionString)

    cnJetDB.Open()

    ' Command to Update Sales Details Records

    Dim cmdUpdate As New OleDbCommand()

    Dim paraInvoiceID As New OleDbParameter()

    Dim paraCustomerID As New OleDbParameter()

    Dim paraRegion As New OleDbParameter()

    Dim paraSalesDate As New OleDbParameter()

    Dim paraShipDate As New OleDbParameter()

    Dim paraGSTExempt As New OleDbParameter()

    Dim paraSalesman As New OleDbParameter()

    Dim paraShippingCost As New OleDbParameter()

    Dim paraShippedTo As New OleDbParameter()

    Dim paraShippedToAddress As New OleDbParameter()

    Dim paraShippedToCity As New OleDbParameter()

    Dim paraShippedToPostalCode As New OleDbParameter()

    Dim paraGST As New OleDbParameter()

    Dim paraNet As New OleDbParameter()

    Dim paraGross As New OleDbParameter()

    Dim paraContact As New OleDbParameter()

    Dim paraStatus As New OleDbParameter()

    Dim paraLastUpdatedOn As New OleDbParameter()

    cmdUpdate.CommandText = "UPDATE Tbl_Sales SET Tbl_Sales.CustomerID = CustomerID, Tbl_Sales.Region = Region, " & _

    "Tbl_Sales.SalesDate = SalesDate, Tbl_Sales.ShipDate = ShipDate, Tbl_Sales.GSTExempt = GSTExempt, " & _

    "Tbl_Sales.Salesman = Salesman, Tbl_Sales.ShippingCost = ShippingCost, Tbl_Sales.ShippedTo = ShippedTo, " & _

    "Tbl_Sales.ShippedToAddress = ShippedToAddress, Tbl_Sales.ShippedToCity = ShippedToCity, " & _

    "Tbl_Sales.ShippedToPostalCode = ShippedToPostalCode, Tbl_Sales.GST = GST, Tbl_Sales.Net = Net, " & _

    "Tbl_Sales.Gross = Gross, Tbl_Sales.Contact = Contact, Tbl_Sales.Status = Status, Tbl_Sales.LastUpdatedOn = LastUpdatedOn " & _

    "WHERE (((Tbl_Sales.InvoiceID)=[InvoiceID]))"

    cmdUpdate.Connection = cnJetDB

    With paraInvoiceID

    .ParameterName = "InvoiceID"

    .OleDbType = OleDbType.Integer

    .Size = 20

    .Value = IIf(InvoiceID = Nothing, Nothing, InvoiceID)

    End With

    cmdUpdate.Parameters.Add(paraInvoiceID)

    With paraCustomerID

    .ParameterName = "CustomerID"

    .OleDbType = OleDbType.Integer

    .Size = 20

    .Value = IIf(CustomerID = Nothing, Nothing, CustomerID)

    End With

    cmdUpdate.Parameters.Add(paraCustomerID)

    With paraRegion

    .ParameterName = "Region"

    .OleDbType = OleDbType.VarChar

    .Size = 20

    .Value = IIf(Region = "", " ", Region)

    End With

    cmdUpdate.Parameters.Add(paraRegion)

    With paraSalesDate

    .ParameterName = "SalesDate"

    .OleDbType = OleDbType.Date

    .Size = 40

    .Value = IIf(SalesDate = Nothing, Nothing, SalesDate)

    End With

    cmdUpdate.Parameters.Add(paraSalesDate)

    With paraShipDate

    .ParameterName = "ShipDate"

    .OleDbType = OleDbType.Date

    .Size = 40

    .Value = IIf(ShipDate = Nothing, Nothing, ShipDate)

    End With

    cmdUpdate.Parameters.Add(paraShipDate)

    With paraGSTExempt

    .ParameterName = "GSTExempt"

    .OleDbType = OleDbType.Boolean

    .Size = 10

    .Value = IIf(IsDBNull(GSTExempt), Nothing, GSTExempt)

    End With

    cmdUpdate.Parameters.Add(paraGSTExempt)

    With paraSalesman

    .ParameterName = "Salesman"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(Salesman = "", " ", Salesman)

    End With

    cmdUpdate.Parameters.Add(paraSalesman)

    With paraShippingCost

    .ParameterName = "ShippingCost"

    .OleDbType = OleDbType.Currency

    .Size = 50

    .Value = IIf(IsDBNull(ShippingCost), Nothing, ShippingCost)

    End With

    cmdUpdate.Parameters.Add(paraShippingCost)

    With paraShippedTo

    .ParameterName = "ShippedTo"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(ShippedTo = "", " ", ShippedTo)

    End With

    cmdUpdate.Parameters.Add(paraShippedTo)

    With paraShippedToAddress

    .ParameterName = "ShippedToAddress"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(ShippedToAddress = "", " ", ShippedToAddress)

    End With

    cmdUpdate.Parameters.Add(paraShippedToAddress)

    With paraShippedToCity

    .ParameterName = "ShippedToCity"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(ShippedToCity = "", " ", ShippedToCity)

    End With

    cmdUpdate.Parameters.Add(paraShippedToCity)

    With paraShippedToPostalCode

    .ParameterName = "ShippedToPostalCode"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(ShippedToPostalCode = "", " ", ShippedToPostalCode)

    End With

    cmdUpdate.Parameters.Add(paraShippedToPostalCode)

    With paraGST

    .ParameterName = "GST"

    .OleDbType = OleDbType.Currency

    .Size = 50

    .Value = IIf(IsDBNull(GST), Nothing, GST)

    End With

    cmdUpdate.Parameters.Add(paraGST)

    With paraNet

    .ParameterName = "Net"

    .OleDbType = OleDbType.Currency

    .Size = 50

    .Value = IIf(IsDBNull(Net), Nothing, Net)

    End With

    cmdUpdate.Parameters.Add(paraNet)

    With paraGross

    .ParameterName = "Gross"

    .OleDbType = OleDbType.Currency

    .Size = 50

    .Value = IIf(IsDBNull(Gross), Nothing, Gross)

    End With

    cmdUpdate.Parameters.Add(paraGross)

    With paraContact

    .ParameterName = "Contact"

    .OleDbType = OleDbType.VarChar

    .Size = 100

    .Value = IIf(Contact = "", " ", Contact)

    End With

    cmdUpdate.Parameters.Add(paraContact)

    With paraStatus

    .ParameterName = "Status"

    .OleDbType = OleDbType.VarChar

    .Size = 20

    .Value = IIf(Status = "", " ", Status)

    End With

    cmdUpdate.Parameters.Add(paraStatus)

    With paraLastUpdatedOn

    .ParameterName = "LastUpdatedOn"

    .OleDbType = OleDbType.Date

    .Size = 40

    .Value = IIf(LastUpdatedOn = Nothing, Nothing, LastUpdatedOn)

    End With

    cmdUpdate.Parameters.Add(paraLastUpdatedOn)

    cmdUpdate.ExecuteNonQuery()

    ' Release the Resources

    cmdUpdate.Dispose()

    cmdUpdate = Nothing

    cnJetDB.Close()

    cnJetDB.Dispose()

    cnJetDB = Nothing

    End Sub

    Wednesday, August 1, 2007 4:30 AM

All replies

  • Your syntax for your SELECT is questionable... I created a similar UPDATE statement and got the same result as you.  The easiest way to fix your code is to prefix all your parameters with an "@" symbol.  So your SQL would look like this:

     

    Code Snippet
    UPDATE Table1 SET Table1.Field1 = @Field1, Table1.Field2 = @Field2 WHERE Table1.ID = @ID

     

     

    The way you have it you are actually setting the fields equal to themselves.

    After you change your SQL, make sure you put an "@" symbol in your OleDbParameter names as well.

    Wednesday, August 1, 2007 3:20 PM