We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Update in Access dbase from VB6

Wilder1626
Wilder1626 asked
on
Medium Priority
413 Views
Last Modified: 2012-05-11
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




Comment
Watch Question

I'm not to hot on VB but could you not set it to NULL instead of ""
CERTIFIED EXPERT

Author

Commented:
Oh yes,   let me try this
CERTIFIED EXPERT

Author

Commented:
No sorry, still don't take it

Still have run time error -2147217913 Incompatible Type.

It just do that when it is NULL.

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?
CERTIFIED EXPERT

Author

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.
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?
CERTIFIED EXPERT

Author

Commented:
Sorry but i  dont think i understand: "Allow zero length string" option in the table definition help
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Author

Commented:
Oh ok, I will take a look,

Thanks for your help.

I will let you know
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Author

Commented:
ok
CERTIFIED EXPERT

Author

Commented:
Hello all,

Thanks for your help, everything is good now.

Thanks again
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.