• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Simple update statement

I am trying to create a simple update command for a query. I have a table I have imported and I have added a query to it to filter the records. I am trying to create an update statement to update my database table (access) to reflect the changes. I have no idea what I am doing here and have this which is way wrong

UPDATE    message
SET              [Check] = message_receive.checkcheckbox
0
wiswalld
Asked:
wiswalld
  • 7
  • 5
1 Solution
 
MrBassCommented:
Greetings,

Could do with a little bit more information, i.e. what's the name of the table you're updating, what's the field you want to update.

The update syntax though, which could get you started is,

UPDATE tablename
SET fieldname = 'value'
WHERE unique_fieldname = 'value'

Hope that helps.
0
 
VBRocksCommented:
The general SQL is similar to MrBass' example, but here's the rest of the code that can help you
accomplish this:

        Dim connectionString As String = "** Your connection string **"

        Dim SQL As String

        'This sample code updates the first name of an employee that has an employee id of 1
        '    in the Employees table
        'SQL = _
        '        "UPDATE Employees " & _
        '        "SET Employees.FirstName = 'Gary' " & _
        '        "WHERE Employees.ID=1;"

        'Your code will be similar to this, but you need to set the WHERE criteria to a unique field/value
        SQL = _
                "UPDATE message " & _
                "SET [Check] = '" & message_receive.checkcheckbox & "' " & _
                "WHERE FieldName=Value;"    'Replace FieldName and Value (as MrBass showed above)

        'The using statement will automatically close and dispose of the connection
        Using cn As New OleDb.OleDbConnection(connectionString)

            cn.Open()

            'Create and execute your SQL using a Command
            Dim cmd As New OleDb.OleDbCommand(SQL, cn)
            Dim i As Int16 = cmd.ExecuteNonQuery()

            'i should contain the number of rows affected
            If i > 0 Then
                MsgBox("Success")
            Else
                MsgBox("Update failed")
            End If

            cmd.Dispose()

        End Using



0
 
wiswalldAuthor Commented:
UPDATE tablename
SET fieldname = 'value'                                  
WHERE unique_fieldname = 'value'



Table name = Message
Field Name = Check
Form field name = CheckCheckBox

What I want to do is change the value from true to false.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
wiswalldAuthor Commented:
I got this:

UPDATE    message
SET              [Check] = False
Where CheckCheckBox = False


Do I need Me.CheckCheckbox or just CheckCheckBox?

Do I need all that other code?
0
 
VBRocksCommented:
If you want to change the value in the database, then you do need to use a Command, with your SQL
to accomplish that.

Either Me.CheckCheckbox or CheckCheckBox is fine.

0
 
wiswalldAuthor Commented:
OK but you are going to have to help me more ............................ PLEASE!

Database table - Message
Database Field - Check
Form Field - CheckCheckBox
0
 
wiswalldAuthor Commented:
I am trying to do this in the edit dataset designer by adding an update statement and using query builder.
0
 
wiswalldAuthor Commented:
This is my current select statement in the table adapter

SELECT     AutoNumber, [Check], [Date], [Time], BOLO
FROM         message
WHERE     ([Check] = [false])

How do I add the darn update statement. Please help.
0
 
VBRocksCommented:
Ok 2 things:  

First, did you generate an Update statement for your TableAdapter when you created it?  If you did,
then you can just use that, here's an example:

        'If your Dataset is named "DataSet1" and your table is named "Message"
        Dim ta As New DataSet1TableAdapters.MessageTableAdapter
        ta.Update(dt)

Second, did you bind your Message table to your Message Table?  if you did, then you can use the
example above, if not, then we can program an update.

0
 
VBRocksCommented:
Sorry, the second question should be:

Did you bind the [Check] field of your Message table to CheckCheckBox?  if you did, then you can use
the example above, if not, then we can program an update.

0
 
wiswalldAuthor Commented:
>>>>Did you bind the [Check] field of your Message table to CheckCheckBox?  if you did, then you can use
the example above, if not, then we can program an update.


Not sure how
0
 
VBRocksCommented:
No problem.  Is your checkbox bound to a datasource?

Here's how you can check:
Click on your checkbox, go to Properties window to view it's properties.  Expand the (DataBindings)
property at the top and look at the Checked property.  Does it say (none) or does it show something
else?  Do any of the properties show a databinding?




0
 
wiswalldAuthor Commented:
I feel really stupid. I thought the whole time I had designated a primary key in the access table. Nope. Now it works great.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now