andreouellet
asked on
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 = pgstrRetournerErreurSpecia l(MODULE, METHODE, "Connection manquante au serveur SQL.")
Exit Function
End If
'Vérification de la commande SQL
If strCommande = "" Then
mgvstrExecuterSQL = Nothing
strErreur = pgstrRetournerErreurSpecia l(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(intColo nne).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(intColo nne).Value )
If IsNull(vstrTableau(intColo nne, 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(MODUL E, METHODE)
End Function
---
Can I put the maximum length more than 30?
---
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 = pgstrRetournerErreurSpecia
Exit Function
End If
'Vérification de la commande SQL
If strCommande = "" Then
mgvstrExecuterSQL = Nothing
strErreur = pgstrRetournerErreurSpecia
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.
'Nom des champs
For intColonne = 0 To rs.rdoColumns.Count - 1
vstrTableau(intColonne, 0) = Trim(rs.rdoColumns(intColo
Next
'Valeur des champs
intRow = 0
With rs
Do Until rs.EOF
intRow = intRow + 1
ReDim Preserve vstrTableau(rs.rdoColumns.
For intColonne = 0 To rs.rdoColumns.Count - 1
vstrTableau(intColonne, intRow) = Trim(rs.rdoColumns(intColo
If IsNull(vstrTableau(intColo
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(MODUL
End Function
---
Can I put the maximum length more than 30?
ASKER
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?
--> 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).
Cheers,
Cheers,
ASKER
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.
"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'"
Cheers
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
ASKER
Sorry, it dont work.
ASKER
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
Cathryn
Replace (monaddresse,"'","''")
so final output is
strCommande = "exec AddPatientName 'Roger', 'Vaillant', '1236 Boulevard St-Joseph De L''Orient'"
bonne journée
Cathryn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks anyone for your answers. The answer of cdb is working very well. Thanks...
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.