What is wrong with my codes...cannot update/edit

[zach007]

Dear Experts,

I am going to edit what is in the database BUT it still does not work...why..??

Private Sub CommandButton2_Click()
 
    Dim con As ADODB.Connection
Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          & "c:\exercise.mdb"
 
 
 
 
  'update the data
    Dim strSQL As String
    strSQL = "UPDATE item " _
        & " SET Item_Code = " & TextBox1.Text _
        & " WHERE Item_Code = " & Trim$(cmb_item)
        
    strSQL = "UPDATE item " _
        & " SET Description = " & TextBox2.Text _
        & " WHERE Item_Code = " & Trim$(cmb_item)
    
    strSQL = "UPDATE item " _
        & " SET Qty = " & TextBox3.Text _
        & " WHERE Item_Code = " & Trim$(cmb_item)
    
    strSQL = "UPDATE item " _
        & " SET Unit_Price = " & TextBox4.Text _
        & " WHERE Item_Code = " & Trim$(cmb_item)
    
    con.Execute strSQL
    
End Sub

Please have a look and let me know what is wrong with this codes...when I click "Update" it wont happen...

Thanks a lot...

Jennifer

[Hack]

Your code should be giving you an error. I'm surprised it isn't. When dealing with text in INSERTING or UPDATING, you need to encapsulate it with single quotes. Try this

strSQL = "UPDATE item " 
 strSQL = strSQL & " SET Item_Code = '" & TextBox1.Text & "' "
 strSQL = strSQL& " WHERE Item_Code = '" & Trim$(cmb_item) & "' "

[zach007]

strSQL = strSQL

I tried and this is error....

[Hack]

I tried and this is error....

What error? I use that type of syntax all the time.

[mendhak]

What is the error.

[zach007]

Hi,

It works..!!!...Thanks a lot...!!!

[zach007]

Hi,

How about this one please....because, it could not delete data in database...here is my code:

Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
        Exit Sub 'exit the command
    Else
        If Not (rst.BOF = True Or rst.EOF = True) Then
            rst.Delete 'delete the current record
            If Not (rst.BOF = True Or rst.EOF = True) Then
                rst.MoveNext 'move next
            If rst.EOF Then rst.MoveLast
                fillfields
            End If
        End If
    End If
    
    Application.ScreenRefresh
End Sub

What is wrong with it...???

[mendhak]

I believe you'll have to refresh the datasource once you perform the delete. In cases like this though, I usually reccommend that you use the connection object's Execute() method to delete the record, and then refill the recordset.

[zach007]

How to do that..???..could you please show me the codes..???...which is it is better for me to learn....

Thanks very much...

[dee-u]

I think mendhak is leading you to something like the ff...

con.Execute "DELECT * FROM Table WHERE Field='Criteria'"
adoRecordset.Requery

[Quizton]

Try something like this it works


Private Sub cmdDelete_Click()
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "DeleteRecord") = vbNo Then
Exit Sub
Else
rs.Delete
Set rs = db.OpenRecordset("enter your datatable here", dbOpenTable)
list
txtFirstName.Text = vbNullString
txtFirstName.Enabled = True

[zach007]

Hi,

I did this code:

Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
        Exit Sub 'exit the command
    Else
        'If Not (rst.BOF = True Or rst.EOF = True) Then
            rst.Delete 'delete the current record
            'If Not (rst.BOF = True Or rst.EOF = True) Then
                'rst.MoveNext 'move next
            'If rst.EOF Then rst.MoveLast
                'fillfields
                Set rst = db.OpenRecordset("SELECT * FROM Item_Code", dbOpenTable)
                TextBox1.Text = vbNullString
                TextBox1.Text = True
                TextBox2.Text = vbNullString
                TextBox2.Text = True
                TextBox3.Text = vbNullString
                TextBox3.Text = True
                TextBox4.Text = vbNullString
                TextBox4.Text = True
            End If
        'End If
    'End If
    
    Application.ScreenRefresh
End Sub

and received error message: Compile Error: Variable not defined...dbOpenTable is in blue highlight....what kind of variable should I set for this...???..i.e. Dim dbOpentable As ?????"

[Quizton]

set your variables at top of page as a general declaration
note i used private instead of Dim you can use either
Option Explicit
Private db As Database
Private rs As DAO.Recordset
Dim sSQL As String
Private ws As Workspace

something like this

[zach007]

Hi,

I am not using DAO...but, I am using ADO connection..???

[Quizton]

Set rst = db.OpenRecordset("SELECT * FROM Item_Code", dbOpenTable)
TextBox1.Text = vbNullString
TextBox1.Text = True
TextBox2.Text = vbNullString
TextBox2.Text = True
TextBox3.Text = vbNullString

Sorry zach forgot to mention that textbox.text = vbNullString will clear you text boxes .
Also in my example the app consists of 10 text boxes and a listbox my listbox displays data & data on each row if you click on the name presented in the listbox then all the textboxes fill with data. The texboxes can be anything you would want and if one of those ten textboxes is a idno or something you would like to search for then it would do so all linked to a database.also you can save, add, edit &, delete If this is generaly what your trying to do then pm me and ill pm you the example It's in the codebank but I dont know how to link you to the page so ill just send you it