Update in Access dbase from VB6

Hello all,

I'm trying to update a few data and i have an error.

I want to update "adress and Ville" field.

How can i fix this please?

Thanks
If (a = 6) Then
        Dim MyConn As ADODB.Connection
    Dim MyRecSet1 As New ADODB.Recordset

    Set MyConn = New ADODB.Connection
    MyConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text
    MyRecSet1.Open "SELECT * FROM [Employés] WHERE Nom='" & txtNomemploye1.Text & "'", MyConn, adOpenForwardOnly, adLockReadOnly

  
    If MyRecSet1.EOF = False Then
        MyConn.Execute ( _
            "Update [Employés] SET Adresse= '" & txtAdresse.Text & "' AND " _
            & "Ville ='" & txtVille.Text & "'" _
            & Where _
            & "Nom ='" & txtNomemploye1.Text & "' AND " _
            & "Prénom ='" & txtPrenomEmploye.Text & "'")

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
 
Den_HBRConnect With a Mentor Commented:
replace

"Update [Employés] SET Adresse= '" & txtAdresse.Text & "' AND " _
            & "Ville ='" & txtVille.Text & "'" _

with

"Update [Employés] SET Adresse= '" & txtAdresse.Text & "' ," _
            & "Ville ='" & txtVille.Text & "'" _
0
 
Den_HBRCommented:
What error do you get?
0
 
Wilder1626Author Commented:
This is the error
error.jpg
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Den_HBRCommented:
replace

& Where _

with

& " Where " _
0
 
Wilder1626Author Commented:
That is good, no more error.

But it does not change anything also.

Altually, it change "Adresse" with a"0" by default and "Ville" stay the same.
0
 
Den_HBRCommented:
and just a hint...
It will not work if there is an ' in 1 off you textboxes.
Better use

replace(txtAdresse.Text,"'","''")
replace(txtVille.Text,"'","''")
replace(txtNomemploye1.Text,"'","''")
replace(txtPrenomEmploye.Text,"'","''")
0
 
Wilder1626Author Commented:
Thanks for your help. This is great
0
 
Wilder1626Author Commented:
What do you mean by:

replace(txtAdresse.Text,"'","''")
replace(txtVille.Text,"'","''")
replace(txtNomemploye1.Text,"'","''")
replace(txtPrenomEmploye.Text,"'","''")
0
 
Den_HBRCommented:
replace a single quote with 2 single quotes.

Trust me!
Try to use "test'test" as your ville and then try to use "test''test" as your ville
0
 
Wilder1626Author Commented:
So i need to do this?

 "Update [Employés] SET Adresse= '" & txtAdresse.Text & "' , & "' ," _
0
 
Den_HBRCommented:
No.

MyConn.Execute ( _
            "Update [Employés] SET Adresse= '" & replace(txtAdresse.Text,"'","''") & "' ," _
            & "Ville ='" & replace(txtVille.Text,"'","''") & "'" _
            & Where _
            & "Nom ='" & replace(txtNomemploye1.Text,"'","''") & "' AND " _
            & "Prénom ='" & replace(txtPrenomEmploye.Text,"'","''") & "'")
0
 
Wilder1626Author Commented:
This is weird cause if i enter in the ville: St-Hubert, it is not working. But if i remove the -, it work.

Do you know why?
0
 
Den_HBRCommented:
Change your code to


Dim strSQL as String

strSQL = " Update [Employés] "
strSQL = strSQL   & " SET "
strSQL  = strSQL  &  "Adresse= '" & replace(txtAdresse.Text,"'","''")  & "'"
strSQL  = strSQL  & " , "
strSQL  = strSQL  & " Ville ='" & replace(txtVille.Text,"'","''")  & "'"
strSQL  = strSQL  &  " Where "
strSQL  = strSQL  &  " Nom ='" & replace(txtNomemploye1.Text,"'","''") & "'"
strSQL  = strSQL  &  " AND "
strSQL  = strSQL  &  " Prénom ='" & replace(txtPrenomEmploye.Text,"'","''") & "'"

MyConn.Execute (strSQL)



set a breakpoint on MyConn.Execute (strSQL) and post the value of strSQL


0
 
Wilder1626Author Commented:
Oh perfect

I like that.

Thank you so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.