How to Update my Access Database from textboxes

Question

i created a screen where i can edit the details in the database. but as soon as i click on the update button, it says no value given for one or more required parameters. i have attached my code....

Update BUtton...

Private Sub SimpleButton5_Click(sender As Object, e As EventArgs) Handles SimpleButton5.Click

    Try

        Access.AddParam("@UId", TextBox1.Text)
        Access.AddParam("@ImagePic", PictureBox1.Image)
        Access.AddParam("@Barcod", TextBox2.Text)
        Access.AddParam("@BrandName", TextBox3.Text)
        Access.AddParam("@StockName", TextBox4.Text)
        Access.AddParam("@Category", TextBox5.Text)
        Access.AddParam("@SubCat", TextBox6.Text)
        Access.AddParam("@Subcat2", TextBox7.Text)
        Access.AddParam("@Discrip", TextBox8.Text)
        Access.AddParam("@StockLvl", TextBox9.Text)
        Access.AddParam("@CustomAmount", TextBox10.Text)
        Access.AddParam("@CostPrice", TextBox11.Text)
        Access.AddParam("@Markup", TextBox12.Text)
        Access.AddParam("@TaxAmount", TextBox13.Text)
        Access.AddParam("@SellingPrice", TextBox14.Text)
        Access.AddParam("@BeforTax", TextBox15.Text)
        Access.AddParam("@AfterTax", TextBox16.Text)
        Access.AddParam("@TaxPer", TextBox17.Text)
        Access.AddParam("@MarkupPer", TextBox18.Text)
        Access.AddParam("@LastDate", TextBox19.Text)
        Access.AddParam("@LastUser", TextBox20.Text)

        Access.ExecQuery("UPDATE Inventory " &
                         "SET [Image]=PictureBox1.image, BarCode=Textbox2.text, " &
                         "BrandName=@BrandName, StockName=@StockName, Category=@Category, SubCategory=@SubCat, " &
                         "SubCategory2=@SubCat2, Description=@Discrip, StockLevels=@StockLvl, CustomAmount=@Customamount, " &
                         "CostPrice=@CostPrice, MarkupAmount=@Markup, SellingPrice=@SellingPrice, ProfirBefore=@BeforeTax, " &
                         "ProfitAfter=@AfterTax, TaxAmount=@TaxAmount, taxPer=@TaxPer, MarkupPer=@MarkupPer, LastDateupdated=@LAstDate, " &
                         "UpserUpdated=@LastUser WHERE ID=@UId")

        If NoErrors(True) = False Then Exit Sub

        RefreshData()

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally

    End Try

End Sub

My Access.ExecQuery --- (Class...)

Imports System.Data.OleDb

Public Class DBControl
    Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;")
    Private DBCmd As OleDbCommand
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable
    Public Params As New List(Of OleDbParameter)
    Public RecordCount As Integer
    Public Exception As String

    Public Sub ExecQuery(Query As String)
        RecordCount = 0
        Exception = ""

        Try
            DBCon.Open()
            DBCmd = New OleDbCommand(Query, DBCon)

            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            Params.Clear()

            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)
        Catch ex As Exception
            Exception = ex.Message
        End Try

        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    ' INCLUDE QUERY & COMMAND PARAMETERS
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)
        Params.Add(NewParam)
    End Sub
End Class

I have played around with this for 2 days now, but somewhere i am missing something or overlooking something

thx

Jaco

Answer 1

I created a simple class for your Inventory object so I could avoid passing all of Properties as parameters. I can just pass the Inventory object to the UpdateDatabase method.

Public Class Inventory
    Public Property Picture As Byte()
    Public Property BarCode As String
    Public Property BrandName As String
    Public Property StockName As String
    Public Property Category As String
    Public Property SubCategory As String
    Public Property SubCategory2 As String
    Public Property Description As String
    Public Property StockLevels As Integer
    Public Property CustomAmount As Decimal
    Public Property CostPrice As Decimal
    Public Property MarkupAmount As Decimal
    Public Property SellingPrice As Decimal
    Public Property ProfitBefore As Decimal
    Public Property ProfitAfter As Decimal
    Public Property TaxAmount As Decimal
    Public Property taxPer As Decimal
    Public Property MarkupPer As Decimal
    Public Property LastDateupdated As Date
    Public Property UpserUpdated As String
    Public Property ID As Integer
End Class

To get your picture box image in the proper format for storage I have a small function that takes an Image as a parameter and returns a Byte array.

'This Function requires Imports System.Drawing
Private Function GetByteArrayFromImage(img As Image) As Byte()
    Dim convert As New ImageConverter
    Dim arr = DirectCast(convert.ConvertTo(img, GetType(Byte())), Byte())
    Return arr
End Function

Each property of the Inventory object is set. You can see that giving your controls meaningful names would help here. CInt, CDec, and CDate text boxes need to be validated before this code is reached.

I put the Try...Catch here so you could show a message to the user.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim img As Image = PictureBox1.Image
    Dim imgArray = GetByteArrayFromImage(img)
    Dim inv As New Inventory With
    {
        .Picture = imgArray,
        .BarCode = TextBox2.Text,
        .BrandName = TextBox3.Text,
        .StockName = TextBox4.Text,
        .Category = TextBox5.Text,
        .SubCategory = TextBox6.Text,
        .SubCategory2 = TextBox7.Text,
        .Description = TextBox8.Text,
        .StockLevels = CInt(TextBox9.Text),
        .CustomAmount = CDec(TextBox10.Text),
        .CostPrice = CDec(TextBox11.Text),
        .MarkupAmount = CDec(TextBox12.Text),
        .SellingPrice = CDec(TextBox14.Text),
        .ProfitBefore = CDec(TextBox15.Text),
        .ProfitAfter = CDec(TextBox16.Text),
        .TaxAmount = CDec(TextBox13.Text),
        .taxPer = CDec(TextBox17.Text),
        .MarkupPer = CDec(TextBox18.Text),
        .LastDateupdated = CDate(TextBox19.Text),
        .UpserUpdated = TextBox20.Text,
        .ID = CInt(TextBox1.Text)
    }
    Try
        UpdateDatabase(inv)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

In Access the order that the parameters appear in the sql statement must match the order that they are added to the parameters collection.

I thought it was odd that all your field names are capitalized except taxPer. Check your database.

Using...End Using blocks ensure that your database objects are closed and disposed even if there is an error.

The .Add method is superior to the method you were using because it includes datatypes and size. I had to guess at the types and sizes so check your database. Where I have guessed wrong, you will have to correct the .Add method, the type of the property in the Inventory class and the conversion of the text boxes .Text property. (3 places to change)

Private Sub UpdateDatabase(inv As Inventory)
    Dim sql = "UPDATE Inventory SET  
                [Image]=@Picture, 
                BarCode= @BarCode,
                BrandName=@BrandName, 
                StockName=@StockName, 
                Category=@Category, 
                SubCategory=@SubCat, 
                SubCategory2=@SubCat2, 
                Description=@Discrip, 
                StockLevels=@StockLvl, 
                CustomAmount=@Customamount,
                CostPrice=@CostPrice, 
                MarkupAmount=@Markup, 
                SellingPrice=@SellingPrice, 
                ProfirBefore=@BeforeTax, 
                ProfitAfter=@AfterTax, 
                TaxAmount=@TaxAmount, 
                taxPer=@TaxPer,      
                MarkupPer=@MarkupPer, 
                LastDateupdated=@LAstDate,
                UpserUpdated=@LastUser 
                WHERE ID=@UId"
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;"),
            cmd As New OleDbCommand(sql, cn)
        With cmd.Parameters
            .Add("@Picture", OleDbType.LongVarBinary).Value = inv.Picture
            .Add("@BarCode", OleDbType.VarChar, 100).Value = inv.BarCode
            .Add("@BrandName", OleDbType.VarChar, 100).Value = inv.BrandName
            .Add("@StockName", OleDbType.VarChar, 100).Value = inv.StockName
            .Add("@Category", OleDbType.VarChar, 100).Value = inv.Category
            .Add("@SubCat", OleDbType.VarChar, 100).Value = inv.SubCategory
            .Add("@SubCat2", OleDbType.VarChar, 100).Value = inv.SubCategory2
            .Add("@Discrip", OleDbType.VarChar, 100).Value = inv.Description
            .Add("@StockLvl", OleDbType.Integer).Value = inv.StockLevels
            .Add("@Customamount", OleDbType.Decimal).Value = inv.CustomAmount
            .Add("@CostPrice", OleDbType.Decimal).Value = inv.CostPrice
            .Add("@Markup", OleDbType.Decimal).Value = inv.MarkupAmount
            .Add("@SellingPrice", OleDbType.Decimal).Value = inv.SellingPrice
            .Add("@BeforeTax", OleDbType.Decimal).Value = inv.ProfitBefore
            .Add("@AfterTax", OleDbType.Decimal).Value = inv.ProfitAfter
            .Add("@TaxAmount", OleDbType.Decimal).Value = inv.TaxAmount
            .Add("@TaxPer", OleDbType.Decimal).Value = inv.taxPer
            .Add("@MarkupPer", OleDbType.Decimal).Value = inv.MarkupPer
            .Add("@LAstDate", OleDbType.Date).Value = inv.LastDateupdated
            .Add("@LastUser ", OleDbType.VarChar, 100).Value = inv.UpserUpdated
            .Add("@UId", OleDbType.Integer).Value = inv.ID
        End With
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Answer 2

The error message is pretty obvious. Some parameters are missing, either you forgot them or they are misspelled.

You need to double-check your code, it contains quite a few typos.

• You are defining parameter @ImagePic, but it's not used in your query.
• Same for @Barcod, you put this instead in your SQL: BarCode=Textbox2.text. Just call it @Barcode, why do you abbreviate names like that. That only creates confusion. Use proper English spelling and be consistent.
• Another typo: Access.AddParam("@BeforTax", TextBox15.Text). In your SQL: ProfirBefore=@BeforeTax. ProfirBefore is a typo too.
• Please do yourself a favor and rename the textboxes too: TextBox1 thru 20 is not good naming practice. There is good chance that you will mix up fields after doing copy-paste of your statements. Textbox20 is not intuitive at all and does not tell you what data you are handling.

I have played around with this for 2 days now, but somewhere i am missing something or overlooking something

Missing glasses perhaps :) I don't know about your development environment put I pasted your code in Notepad++ and by clicking on a keyword it highlights all occurrences of that keyword in the code. It quickly became obvious that some keywords were not being referenced anywhere.