VB6 More than 10 parameters for stored procedure

Question

I have problem in vb6 when I'm trying to insert parameters that are more than 10, 9 below are fine.

When I try from '@1' to '@9' its fine, the output will be strsql = '1st value'...'9th value', but when I with 10 parameter it outputs strsql = '@1'...'@10'

Set rsOR = New ADODB.Recordset
    strSql = SQLParams("DB..sp_Insert '@1','@2','@3','@4','@5','@6','@7','@8','@9','@10'", cbPayor.Text, "COLLECTION", txtORCol.Text, dtCol.Value, UserID, CollectionType, txtAmountCol.Text, "PHP", dtColCash.Value, txtCheque.Text)
    clsSession.Execute strSql, rsOR

No error using

'@1'...'@9'
FROM: strSql = SQLParams("DB..sp_Insert '@1',...,'@9'", cbPayor.Text, "COLLECTION")

OUTPUTS: strsql "DB..sp_Insert 'text here',.... ,'COLLECTION'

Error using

'@1'...'@10'
FROM: strSql = SQLParams("DB..sp_Insert '@1',...,'@10'", cbPayor.Text, "COLLECTION")

OUTPUTS: strsql "DB..sp_Insert '@1'...'@10'

So you have a function, `SQLParams`, that [concatenates parameters into the query](https://stackoverflow.com/q/332365/11683). Apparently that function does not support more that 9 parameters. Yet another reason to not use that function. — GSerg, Jul 25 '19 at 08:28
Hmmm, maybe. I really don't know because I am a new programmer and most of the things are not endorsed. — Alphatrix, Jul 25 '19 at 08:42
By the way thank you for your response, I found out that I should use letters, by clicking the definition it has some documentation/comment on it. — Alphatrix, Jul 25 '19 at 08:45
@Alphatrix Generally, it is expected that new employees will ask questions to become familiar and productive in a new environment. Please try asking your coworkers, your manager, your seniors, etc. — SMor, Jul 25 '19 at 12:11

score 0 · Answer 1 · answered Jul 25 '19 at 10:33

After a bit of googling I found this (there should be no 9 field limit):

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
& " NEEDS_ID = @NEEDS_ID, " & vbNewLine _
& " OBJ_ID = @OBJ_ID, " & vbNewLine _
& " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
& " TIME21_ID = @TIME21_ID, " & vbNewLine _
& " WHERE ID = @WHEREID"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("@WHEREID", adInteger, adParamInput, 18, 456)
    .Execute
End With

VB6 More than 10 parameters for stored procedure

1 Answers1