Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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
    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)
        '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
        End With
    End If

    Set rs = Nothing
    Set qy = Nothing
    mgvstrExecuterSQL = vstrTableau

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

Can I put the maximum length more than 30?
1 Solution
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.
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?
I've faced with the same problem. You've reached RDO limitation, maximum of 30 parameters (Whether that is TRUE or NOT).

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.
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'"

andreouelletAuthor Commented:
Sorry, it dont work.
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.
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
The alternative to using Replace(sString, "'", "''") for all queries, is to use the

rs!field1 = "L'Orient"

as this will accept single quotes in the data.

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

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now