Results 1 to 13 of 13

Thread: [RESOLVED] updating the fields in a Access Database from textbox value

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Resolved [RESOLVED] updating the fields in a Access Database from textbox value

    I have a Access DB "STG" I have fields "KKS Number" and "Type"

    I have a userform that shows KKS Number and Type, I would like the user to be able to change the "Type" textbox on my form "STG2" when they press a push button

    The KKS Number is read ONLY, I cannot seem to work out how I look for the KKS number in my table and then change the type with what has been entered by the user.

    Is this possible if so any tips would be greatly appreciated.

    Thanks Izzy

  2. #2
    New Member
    Join Date
    Mar 2018
    Posts
    7

    Re: updating the fields in a Access Database from textbox value

    Hi, if got your question correctly you want to be able to look up field "KKS Number" and then change field type with a button.

    there a two main ways to go about this:

    the first one is using typed datasets and binding source which is kind of the automatic way.

    the second method is the manual way in which you create sql commands, connection objects,datareaders and table adapters.

    you would need to specify which method you are using to further help you.

    also doesnt the STG table has an ID field or primary key? this is a must to uniquely identify records on a table.

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: updating the fields in a Access Database from textbox value

    You’re right yes, I would be better using the ID, never crossed my mind as I otherwise ignore this in the program.

    Definitely have to be the second option as I would not want everyone to be able to update the table and would create a password setup to only allow a select few to make updates

    Thanks in advance

    Izzy

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: updating the fields in a Access Database from textbox value

    I've come up with this so far, don't seem to be able to get it to update my access database though any ideas where I am going wrong?

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ds As New DataSet
            Dim da As OleDb.OleDbDataAdapter
            Dim sql As String
            Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
            Dim conn As New OleDb.OleDbConnection(connString)
            conn.Open()
            sql = "SELECT * FROM STG WHERE ID like '" & Label15.Text & "%'" 'LABEL 15 CONTAINS DATABASE ID
            da = New OleDbDataAdapter(sql, conn)
                ds = New DataSet
                da.Fill(ds, "update")
            'conn.Close()
    
    
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            ds.Tables("update").Rows(0).Item(1) = KKSTextbox.Text 'DATA I WISH TO STORE IN MY DATABASE
            ds.Tables("update").Rows(0).Item(2) = TypeTextbox.Text
                ds.Tables("update").Rows(0).Item(3) = RangeTextBox.Text
                ds.Tables("update").Rows(0).Item(4) = CompDescTextbox.Text
                ds.Tables("update").Rows(0).Item(5) = LRTextBox.Text
                ds.Tables("update").Rows(0).Item(6) = FuncLocTextbox.Text
                ds.Tables("update").Rows(0).Item(7) = LocTextbox.Text
                ds.Tables("update").Rows(0).Item(8) = MANTextBox.Text
                ds.Tables("update").Rows(0).Item(9) = PTNOTextBox.Text
                ds.Tables("update").Rows(0).Item(10) = SUPPTextBox.Text
                ds.Tables("update").Rows(0).Item(11) = DetailTextBox.Text
                ds.Tables("update").Rows(0).Item(12) = PIDTextBox.Text
                ds.Tables("update").Rows(0).Item(13) = CONNTextBox.Text
                ds.Tables("update").Rows(0).Item(14) = BLOCKTextBox.Text
                ds.Tables("update").Rows(0).Item(15) = OMTextBox.Text
                ds.Tables("update").Rows(0).Item(16) = AL1TextBox.Text
                ds.Tables("update").Rows(0).Item(17) = AL2TextBox.Text
                ds.Tables("update").Rows(0).Item(18) = AL3TextBox.Text
                ds.Tables("update").Rows(0).Item(19) = AL4TextBox.Text
                ds.Tables("update").Rows(0).Item(20) = AL5TextBox.Text
                ds.Tables("update").Rows(0).Item(21) = AL6TextBox.Text
                ds.Tables("update").Rows(0).Item(22) = AL7TextBox.Text
                ds.Tables("update").Rows(0).Item(23) = AL8TextBox.Text
                ds.Tables("update").Rows(0).Item(24) = AL9TextBox.Text
                ds.Tables("update").Rows(0).Item(25) = AL10TextBox.Text
                ds.Tables("update").Rows(0).Item(26) = EFF1TextBox.Text
                ds.Tables("update").Rows(0).Item(27) = EFF2TextBox.Text
                ds.Tables("update").Rows(0).Item(28) = EFF3TextBox.Text
                ds.Tables("update").Rows(0).Item(29) = EFF4TextBox.Text
                ds.Tables("update").Rows(0).Item(30) = EFF5TextBox.Text
                ds.Tables("update").Rows(0).Item(31) = EFF6TextBox.Text
                ds.Tables("update").Rows(0).Item(32) = EFF7TextBox.Text
                ds.Tables("update").Rows(0).Item(33) = EFF8TextBox.Text
                ds.Tables("update").Rows(0).Item(34) = EFF9TextBox.Text
                ds.Tables("update").Rows(0).Item(35) = EFF10TextBox.Text
                ds.Tables("update").Rows(0).Item(36) = SET1TextBox.Text
                ds.Tables("update").Rows(0).Item(37) = SET2TextBox.Text
                ds.Tables("update").Rows(0).Item(38) = SET3TextBox.Text
                ds.Tables("update").Rows(0).Item(39) = SET4TextBox.Text
                ds.Tables("update").Rows(0).Item(40) = SET5TextBox.Text
                ds.Tables("update").Rows(0).Item(41) = SET6TextBox.Text
                ds.Tables("update").Rows(0).Item(42) = SET7TextBox.Text
                ds.Tables("update").Rows(0).Item(43) = SET8TextBox.Text
                ds.Tables("update").Rows(0).Item(44) = SET9TextBox.Text
            ds.Tables("update").Rows(0).Item(45) = SET10TextBox.Text
            da.Update(ds)
            'da.Dispose()
            conn.Close()
    
        End Sub

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    7

    Re: updating the fields in a Access Database from textbox value

    ok at a glance first wrong thing i see is that you are referring to your ID as a non unique value (like), this would retrieve any record where the ID are similar or contain similar information, defeating the purpose of retrieving only the record you wanted row(0) so for example if the recordset brings 3 or 4 records , your code is hardcoded to update row(0) but row(0) might not be the record you want because there are multiple records so which record is getting updated is unknown, ID's are unique identifiers so the appropriate thing would be ID=value so you can retrieve one and only one record and edit and update accordingly.

    if your code is not crashing it means that is in fact updating just not the record you intended, please revise and let me know if that solves your problem or not to further assist you.

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: updating the fields in a Access Database from textbox value

    thanks for the reply,

    I have revised the code as follows I got it to write first time now I get UPDATE Syntax

    not sure what ive done hahaha

    Code:
    Dim cmd As OleDbCommand
            Dim str As String
    
            str = "UPDATE [STG] SET [KKS Number]='" & KKSTextbox.Text & "', [Type]='" & TypeTextbox.Text & "', & WHERE [ID]='" & Label15.Text 
            cmd = New OleDbCommand(str, conn)
    
            cmd.ExecuteNonQuery()
            conn.Close()

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    7

    Re: updating the fields in a Access Database from textbox value

    then you need to debug your sql string a good way to do that is to put a breakpoint on the code right after your string is built so you can analyze the entire string and even copy it and test it out in a query in access, i cannot assess that just from looking at your code since table name, field names ,etc matters when doing any db operation.

    but remember you updated the update command to reflect the ID=Value but you also have to do it in the select, insert and delete commands as well.

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: updating the fields in a Access Database from textbox value

    The question is, what data type is "ID". My guess is it's an AutoNumber field, which makes it an Integer. So the syntax would be "WHERE [ID]=" & Label15.Text

    If ID is a String data type then the syntax would be "WHERE [ID]='" & Label15.Text & "'"

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    7

    Re: updating the fields in a Access Database from textbox value

    that is correct ID should be set to autonumber which automatically sets it as an integer. so yes the value goes without quotes. like that "WHERE [ID]=" & Label15.Text

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Re: updating the fields in a Access Database from textbox value

    Thanks for everyone’s input,

    Nailed it.

    I had a comma in just before WHERE it shouldn’t be there! I knew it was something daft

    Code:
     str = "UPDATE [STG] SET [KKS Number]='" & KKSTextbox.Text & "', [Type]='" & TypeTextbox.Text & "' & WHERE [ID]='" & Label15.Text
    Thanks again for your help its much appreciated

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: [RESOLVED] updating the fields in a Access Database from textbox value

    that is correct ID should be set to autonumber
    I don't really agree with this.

    An ID field can be any data type, I've had many clients that want Alpha characters in the record ID, mostly for ease of searching. Also AutoNumber fields can cause some concurrency issues when adding/editing new records if you don't know to retrieve the last generated autonumber. I personally try to avoid them but that just me. Lots of people use them.

  12. #12
    New Member
    Join Date
    Mar 2018
    Posts
    7

    Re: [RESOLVED] updating the fields in a Access Database from textbox value

    Quote Originally Posted by wes4dbt View Post
    I don't really agree with this.

    An ID field can be any data type, I've had many clients that want Alpha characters in the record ID, mostly for ease of searching. Also AutoNumber fields can cause some concurrency issues when adding/editing new records if you don't know to retrieve the last generated autonumber. I personally try to avoid them but that just me. Lots of people use them.
    dear wes4dbt,

    by standard and definition the IDENTITY COLUMN is either an int, tiny int or small int in fact if you want it to be primary key and auto increment you will have to use any of those 3 data types otherwise won't let you, now that being said, you can have your column ID which is the one that is uniquely going to identify the row, and have a second column with a name say "ref" or "recref" or any meaningful name and store any datatype and the combination of characters accordingly to have another identifier one which you control but even for relationships it is more simpler with numeric only column, in all the years that i have programming databases i have never seen a concurrency problem with an autogenerated IDENTITY Column/Primary Key as it is in place specifically to aid with that problem and providing a mechanism of uniquely and unequivocally refer to a row.

    also if you have an autonumber primary key which is not the same as any other field with simply autonumber type, you will not need to guess the next number because it is autogenerated and if you have child tables with proper relationships the value is seeded accordingly, most of the time database problems come down to poorly design schemas and lack of proper relationships in place.

    but hey remember that every objective has many paths to get to it everyone is free to implement whatever they feel more comfortable with and works best for them at the end you want results and if you get them then is a valid approach.

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: [RESOLVED] updating the fields in a Access Database from textbox value

    threadw,

    You miss understand. I know what an Identity column is (Access Tables don't even have a field type of "Identity Column") but all we know here is their is a field with the name "ID". It could be any Access data type. I believe you haven't had concurrency issues but that's because of how you handle your Add/Edit/Deletes not because it can't happen. I've seen this concurrence issue hundreds of times on this forum and have had the issue myself. If you don't believe me then just do some searching on this forum. If your really interested I could probably create a simple example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width