Update in Access dbase from VB6

Hello all,

I have a problem with this code.

When i apply that code, it work but when the value = "0", if i change the code to put = "", it does not work. It require a quantity, not a blank.

How can i fix this to update the access dbase with null if the quantity is "0" ?

The quantity is in txtQteInventaireSiSupprime.text.

Thanks for your help.



 Dim MyConn3 As ADODB.Connection
    Dim MyRecSet3 As New ADODB.Recordset

    Set MyConn3 = New ADODB.Connection
    MyConn3.Open "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text
    MyRecSet3.Open "SELECT * FROM [Inventaire] WHERE" _
                           & " Période = '" & txtPeriode.Text & "'" _
                           & " and Description_du_produit = '" & ComDescriptionDe.Text & "'", MyConn3, adOpenForwardOnly, adLockReadOnly

    If MyRecSet3.EOF = False Then
        If MyRecSet3.EOF = False Then
       Dim strSQL As String

strSQL = " Update [Inventaire] "
strSQL = strSQL & " SET "
strSQL = strSQL & "Quantité_distribuée = '" & Replace(txtQteInventaireSiSupprime.Text, "'", "''") & "'"
strSQL = strSQL & " Where "
strSQL = strSQL & " Période ='" & Replace(txtPeriode.Text, "'", "''") & "'"
strSQL = strSQL & " AND "
strSQL = strSQL & " Description_du_produit ='" & Replace(ComDescriptionDe.Text, "'", "''") & "'"

MyConn3.Execute (strSQL)

Open in new window




LVL 11
Wilder1626Asked:
Who is Participating?
 
IrishStudentCommented:
If I remember correctly there is an option in Access to allow zero length strings to be saved to a table, you have to set it against the field that you are trying to update. What is the datatype of the field in question.
0
 
IrishStudentCommented:
I'm not to hot on VB but could you not set it to NULL instead of ""
0
 
Wilder1626Author Commented:
Oh yes,   let me try this
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Wilder1626Author Commented:
No sorry, still don't take it

Still have run time error -2147217913 Incompatible Type.

It just do that when it is NULL.

0
 
IrishStudentCommented:
Ok lets see if I can understand what your trying to do. I think maybe I'm not understanding the question completely.

You want to update your database when the quantity is 0 to null.
What is the structure of your table and what is the requirement that would result in you setting a integer value from 0 (lets say no stock) to null.

Also if required, does setting the "Allow zero length string" option in the table definition help? It may force an empty string to be interpreted as NULL?
0
 
Wilder1626Author Commented:
Let me explain a little bit more.

This is the value determine in my textbox:txtQteInventaireSiSupprime

Form11.txtQteInventaireSiSupprime = Val(Form11.txtQteDistribue.Text) - Val(Form11.txtQtéAssignéeAjustée2.Text)

Open in new window


When it is > 0, there's no problem. But if the result in txtQteInventaireSiSupprime  = 0, then when i update the access database, i don't want to put 0 but NULL.

I have done a code saying that if txtQteInventaireSiSupprime  = 0 then
txtQteInventaireSiSupprime = NULL.

But when it update in access dbase, i have the error:  run time error -2147217913 Incompatible Type.
0
 
IrishStudentCommented:
Ok well what is the data type of the field you are trying to update is it int does setting the "Allow zero length string" option in the table definition help?
0
 
Wilder1626Author Commented:
Sorry but i  dont think i understand: "Allow zero length string" option in the table definition help
0
 
Wilder1626Author Commented:
Oh ok, I will take a look,

Thanks for your help.

I will let you know
0
 
VBClassicGuyCommented:
Make sure your field is set to "Allow NULL = Yes".
0
 
Wilder1626Author Commented:
ok
0
 
Wilder1626Author Commented:
Hello all,

Thanks for your help, everything is good now.

Thanks again
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.