Access database not updating through the Textboxes in Vb.net-CodePudding

I am using Vb.Net to update my access database with some textboxes. This is the code that I have which doesn't give any error when the 'update' button is clicked in the form, but nothing happens either. For some reason the database doesn't get updated. I'm not sure what I'm doing wrong.

Dim pro As String
        Dim connstring As String
        Dim command As String
        Dim myconnection As OleDbConnection = New OleDbConnection

        pro = "Provider =Microsoft.ACE.OLEDB.12.0; data source= C:\Users\Hamza\Documents\POS system1.accdb"
        connstring = pro
        myconnection.ConnectionString = connstring
        myconnection.Open()
        Dim Str = "Update Customers SET  FirstName=?, LastName=?, Address=?, PhoneNo=?, Points=? WHERE CustID=?"
        Dim cmd = New OleDbCommand(Str, myconnection)
        cmd.Parameters.AddWithValue("@CustID", CustIDTextBox)
        cmd.Parameters.AddWithValue("@FirstName", First_NameTextBox)
        cmd.Parameters.AddWithValue("@LastName", Last_NameTextBox)
        cmd.Parameters.AddWithValue("@Address", AddressTextBox)
        cmd.Parameters.AddWithValue("@PhoneNo", Phone_noTextBox)
        cmd.Parameters.AddWithValue("@Points", PointsTextBox)
        cmd.ExecuteNonQuery()

        MsgBox("Updated!")

CodePudding user response：

You can pass the connection string directly to the constructor of the connection.

OleDb (used with Access) does not care about the names of parameters. The order that they appear in the sql string must match the order that they are added to the parameters collection. Thus, move the CustID to the end of the parameters being added because it is the last thing in the sql string.

I was very glad to see that you are using parameters. The Add method gives you a much better chance of sending the proper datatype to the database. I wasn't sure if the TextBox variables were text boxes that needed the Text property or variables you had set. I had to guess at the datatypes. Check your database for the actual types.

Connections and Commands need to be disposed. Using...End Using blocks accomplish this for us. It also closes the connection. You didn't close your connection at all.

Don't open the connection until directly before the Execute...

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Str = "Update Customers SET  FirstName=?, LastName=?, Address=?, PhoneNo=?, Points=? WHERE CustID=?"
    Dim pro = "Provider =Microsoft.ACE.OLEDB.12.0; data source= C:\Users\Hamza\Documents\POS system1.accdb"
    Using myconnection As New OleDbConnection(pro),
            cmd As New OleDbCommand(Str, myconnection)
        cmd.Parameters.Add("@FirstName", OleDbType.VarChar).Value = First_NameTextBox.Text
        cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = Last_NameTextBox.Text
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = AddressTextBox.Text
        cmd.Parameters.Add("@PhoneNo", OleDbType.VarChar).Value = Phone_noTextBox.Text
        cmd.Parameters.Add("@Points", OleDbType.Integer).Value = CInt(PointsTextBox.Text)
        cmd.Parameters.Add("@CustID", OleDbType.Integer).Value = CInt(CustIDTextBox.Text)
        myconnection.Open()
        cmd.ExecuteNonQuery()
    End Using
    MsgBox("Updated!")
End Sub

CodePudding user response：

Ok, lets try this a bit different.

First up: Lets get the connection string OUT side of the code.

Like for desktop, or anything else? You can add values like connection string to the project like this:

And really nice is you get to use the connection builder to do this.

The above setting are shoved into web.config for you automatic.

So, setup your connection in above.

Ok, now in this case, I just shove on the screen a few text boxes for a user and hotel name.

Real plane jane like this:

    <div style="width:25%;text-align:right;padding:25px;border:solid;border-width:1px">

        <style> .tbox {width:260px;margin-left:5px;margin-bottom:15px;border-radius:8px;border-width:1px}</style>

        Hotel Name: <asp:TextBox ID="txtHotelName" runat="server" class="tbox"/>
        <br />
        First Name: <asp:TextBox ID="txtFirst" runat="server" class="tbox" />
        <br />
        Last Name:<asp:TextBox ID="txtLast" runat="server"  class="tbox"/>
        <br />
        City: <asp:TextBox ID="txtCity" runat="server"  class="tbox"/>
        <br />
        Active:<asp:CheckBox ID="ckActive" runat="server"  />
        <br />
        <br />

  </div>
        <asp:Button ID="cmdSave" runat="server" Text="Save" class="btn" />
        <asp:Button ID="cmdCancel" runat="server" Text="Cancel" style="margin-left:20px" class="btn" />

Ok, now our code to load this. I don't have a text box or source for the id, but a integer value OR a text value will work.

So, our code to load up is this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()
    End If

End Sub

Sub LoadData()

    Dim cmdSQL As OleDbCommand = New OleDbCommand()

    cmdSQL.CommandText = "SELECT * from tblhotels where ID = @ID"
    cmdSQL.Parameters.Add("@ID", OleDbType.Integer).Value = 23

    Dim rst As DataTable = MyRst(cmdSQL)
    With rst.Rows(0)
        txtHotelName.Text = .Item("HotelName")
        txtFirst.Text = .Item("FirstName")
        txtLast.Text = .Item("LastName")
        txtCity.Text = .Item("City")
        ckActive.Checked = .Item("Active")
    End With

    ViewState("rst") = rst

End Sub

Note the cute helper routine MyRst.

So, you can use that routine EVERY where. eg:

Dim cmdSQL As OleDbCommand = New OleDbCommand("select * from RoomTypes")
Dim rst as DataTable = MyRst(cmdSQL)

So, it just a handy dandy routine. (you do NOT have to use parameters if you don't need them).

Ok, so we loaded the one row into the table (and we save that row for later use into ViewState)

Ok, so now we see this:

Now, the save code. Note how we used a record set (datatable) in place of a GAZILLION parameters.

We do this for quite a few reasons.

Strong data type conversion occurs here.

Parameter order for the save does not matter. I can cut-paste, or add 5 or 15 more columns here, and it works - and order does not matter!!!

So, now the save code.

Protected Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click

    SaveData()

End Sub

Sub SaveData()

    Dim rst As DataTable = ViewState("rst")

    Using con As New OleDbConnection(My.Settings.AccessTest2)
        Using cmdSQL As New OleDbCommand("SELECT * from tblHotels WHERE ID = 0", con)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmdSQL)
            Dim daSQLU As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            con.Open()
            With rst.Rows(0)
                .Item("HotelName") = txtHotelName.Text
                .Item("FirstName") = txtFirst.Text
                .Item("LastName") = txtLast.Text
                .Item("City") = txtCity.Text
                .Item("Active") = ckActive.Checked
            End With
            da.Update(rst)
        End Using
    End Using

End Sub

NOTE: not a bug, I MOST certainly did use where ID = 0

So, the nice part is we can add more text box etc. We will have to add code to setup the text boxes, but at least the order don't matter.

Last but not least?

That helper routine, the one I use to fill datatables. I even use it for say filling out combo box (dropdown lists), or whatever.

Public Function MyRst(cmdSQL As OleDbCommand) As DataTable

    Dim rstData As New DataTable
    Using MyCon As New OleDbConnection(My.Settings.AccessTest2)
        cmdSQL.Connection = MyCon
        MyCon.Open()
        rstData.Load(cmdSQL.ExecuteReader)
    End Using

    Return rstData

End Function

So note how we used the connection string setting that we setup in the project.

And since access is sensitive to parameter order, then I adopted the above idea of using a data table. Note that this approach also works for a grid, or even adding rows. When you run that update routine? rows added, rows edits, row deleted?

They all are done for you with the ONE da.Upate(rst).

Note also, you should set your project to run as x86, and not x64, since JET ONLY can work as x32. However, the ACE data engine can be had for x64 bits.