Solved

Problem with RDO

Posted on 1999-01-27
10
448 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:andreouellet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Expert Comment

by:CS011999
ID: 1499520
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
 

Author Comment

by:andreouellet
ID: 1499521
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
 
LVL 2

Expert Comment

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

Cheers,
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andreouellet
ID: 1499523
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
 
LVL 2

Expert Comment

by:vspeter
ID: 1499524
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
 

Author Comment

by:andreouellet
ID: 1499525
Sorry, it dont work.
0
 

Author Comment

by:andreouellet
ID: 1499526
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
 

Expert Comment

by:cbd
ID: 1499527
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
 
LVL 3

Accepted Solution

by:
cognition earned 100 total points
ID: 1499528
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
 

Author Comment

by:andreouellet
ID: 1499529
Thanks anyone for your answers. The answer of cdb is working very well.  Thanks...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question