Problem with RDO

I have a problem with RDO and SQL Server.  When I execute this:
---
strCommande = "exec AddPatientName " & Chr(34) & "Roger" & Chr(34) & "," & Chr(34) & "Vaillant" & Chr(34) & "," & Chr(34) & "1236 Boulevard St-Joseph De Lapocante" & Chr(34)
---
I receive this error from MSRDO20.DLL: "The identifier that starts with '1' is too long. Maximum length is 30."

This is my fonction:
---
Public Function mgvstrExecuterSQL(ByRef strErreur As String, strCommande As String) As Variant
On Error GoTo Erreur:

    Const METHODE = "mgvstrExecuteSQL"
    Dim qy As rdoQuery
    Dim rs As rdoResultset
    Dim intColonne As Integer
    Dim intRow As Integer
    Dim vstrTableau() As Variant

    strErreur = ""

    'Validation ouverture session
    If mConnection Is Nothing Then
        mgvstrExecuterSQL = Nothing
        strErreur = pgstrRetournerErreurSpecial(MODULE, METHODE, "Connection manquante au serveur SQL.")
        Exit Function
    End If
   
    'Vérification de la commande SQL
    If strCommande = "" Then
        mgvstrExecuterSQL = Nothing
        strErreur = pgstrRetournerErreurSpecial(MODULE, METHODE, "Commande SQL invalide.")
        Exit Function
    End If
   
    'Lancement de la commande SQL
    Set qy = New rdoQuery
    qy.SQL = strCommande
    'MsgBox strCommande
    Set qy.ActiveConnection = mConnection
   
    qy.RowsetSize = 1
   
    'Résultat
    Set rs = qy.OpenResultset()
    If rs.rdoColumns.Count > 0 Then
        ReDim Preserve vstrTableau(rs.rdoColumns.Count - 1, 0) As Variant
       
        'Nom des champs
        For intColonne = 0 To rs.rdoColumns.Count - 1
            vstrTableau(intColonne, 0) = Trim(rs.rdoColumns(intColonne).Name)
        Next
       
        'Valeur des champs
        intRow = 0
        With rs
            Do Until rs.EOF
                intRow = intRow + 1
                ReDim Preserve vstrTableau(rs.rdoColumns.Count - 1, intRow) As Variant
                For intColonne = 0 To rs.rdoColumns.Count - 1
                    vstrTableau(intColonne, intRow) = Trim(rs.rdoColumns(intColonne).Value)
                    If IsNull(vstrTableau(intColonne, intRow)) Then
                        vstrTableau(intColonne, intRow) = ""
                    End If
                Next
                rs.MoveNext
            Loop
        End With
    End If

    Set rs = Nothing
    Set qy = Nothing
    mgvstrExecuterSQL = vstrTableau

Exit Function
Erreur:
    Set rs = Nothing
    Set qy = Nothing
    strErreur = pgstrRetournerErreur(MODULE, METHODE)
End Function
---

Can I put the maximum length more than 30?
andreouelletAsked:
Who is Participating?
 
cognitionCommented:
The alternative to using Replace(sString, "'", "''") for all queries, is to use the

rs.addnew
rs!field1 = "L'Orient"
rs.update

as this will accept single quotes in the data.


0
 
CS011999Commented:
This is in French. Anyway here goes :
I think your function is fine. Problem could be your stored procedure AddPatientName. Can you look into your SP & take a look at the arguments that you have used? Try to increase the size of your arguments & see if this problem occurs again. Let me know if this is not it.
0
 
andreouelletAuthor Commented:
My stored proc is ok. Each parameter of my SP is varchar(80). My problem happend on this line:
--> Set rs = qy.OpenResultset()

Why the maximum of each parameter in RDO is 30?
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.

 
vspeterCommented:
I've faced with the same problem. You've reached RDO limitation, maximum of 30 parameters (Whether that is TRUE or NOT).

Cheers,
0
 
andreouelletAuthor Commented:
The problem it's not the maximum of parameters but the maximum of caracters of one parameter.  This is the error message:

"The identifier that starts with '1' is too long. Maximum length is 30"

My SP have only 3 parameters. Each parameter have a max. length of 80 caracters.
0
 
vspeterCommented:
Okie, Second try

Can you try to replace Chr(34) with ' (single quote). I know that Chr(34) = " (double quote).

strCommande = "exec AddPatientName 'Roger', 'Vaillant', '1236 Boulevard St-Joseph De Lapocante'"

Cheers
 
0
 
andreouelletAuthor Commented:
Sorry, it dont work.
0
 
andreouelletAuthor Commented:
After a second test, it work. But if I have a string with a single quote like "1236 Boulevard St-Joseph De L'Orient", it don't work.  It's the raison because I use a double quote.
0
 
cbdCommented:
Can you use the Replace Function (from VBScript now in VB6) ?
Replace (monaddresse,"'","''")
so final output is
strCommande = "exec AddPatientName 'Roger', 'Vaillant', '1236 Boulevard St-Joseph De L''Orient'"

bonne journée
Cathryn
0
 
andreouelletAuthor Commented:
Thanks anyone for your answers. The answer of cdb is working very well.  Thanks...
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.