Need help with ADO Command.Parameters 
Author Message
 Need help with ADO Command.Parameters

Hi!
I am trying to use the Command.Parameters object.
I have a very large SQL Insert statement and i wish to use the Command Parameters and not the RS!Field option.
My code looks like:

Public Const ADD_SUBSCRIBER_QUERY = "INSERT INTO SUBSCRIBERS(" & _
                                    "SUBSCRIBER_ID,CREATED_DATE,CREATED_BY)" & _
                                    "VALUES('?','?','?')"
SubDataArr(0) = "1"
SubDataArr(1) = "01/01/02"
SubDataArr(2) = "Me"
Dim MyCommand As New ADODB.Command
Dim param As ADODB.Parameter
Dim retRS As ADODB.Recordset
Dim fieldIndex As Integer
MyCommand.ActiveConnection = ConnObjDB2
MyCommand.CommandType = adCmdText
MyCommand.CommandText = ADD_SUBSCRIBER_QUERY
'Run over all subscriber's data
For fieldIndex = 0 To UBound(SubDataArr)
    Set param = MyCommand.CreateParameter(, adVarChar, adParamInputOutput, 50)
    param.value = SubDataArr(fieldIndex)
    MyCommand.Parameters.Append param
    Set param = Nothing
Next fieldIndex
Set retRS = MyCommand.Execute

It is not working properly,
Any help?



Sat, 07 May 2005 22:18:23 GMT  
 Need help with ADO Command.Parameters

Hi,

Do you get some sort of error message? What is it?

--
Val Mazur
Microsoft MVP


  Hi!
  I am trying to use the Command.Parameters object.
  I have a very large SQL Insert statement and i wish to use the Command Parameters and not the RS!Field option.
  My code looks like:

  Public Const ADD_SUBSCRIBER_QUERY = "INSERT INTO SUBSCRIBERS(" & _
                                      "SUBSCRIBER_ID,CREATED_DATE,CREATED_BY)" & _
                                      "VALUES('?','?','?')"
  SubDataArr(0) = "1"
  SubDataArr(1) = "01/01/02"
  SubDataArr(2) = "Me"
  Dim MyCommand As New ADODB.Command
  Dim param As ADODB.Parameter
  Dim retRS As ADODB.Recordset
  Dim fieldIndex As Integer
  MyCommand.ActiveConnection = ConnObjDB2
  MyCommand.CommandType = adCmdText
  MyCommand.CommandText = ADD_SUBSCRIBER_QUERY
  'Run over all subscriber's data
  For fieldIndex = 0 To UBound(SubDataArr)
      Set param = MyCommand.CreateParameter(, adVarChar, adParamInputOutput, 50)
      param.value = SubDataArr(fieldIndex)
      MyCommand.Parameters.Append param
      Set param = Nothing
  Next fieldIndex
  Set retRS = MyCommand.Execute

  It is not working properly,
  Any help?



Sat, 07 May 2005 22:26:34 GMT  
 Need help with ADO Command.Parameters

I don't get any error message.
I tried it with less complicated code:
    Dim query As String
    query = "select * from subscriber where subscriberid =?"
    Dim MyCommand As New ADODB.Command
    Dim param As New ADODB.Parameter
    Dim retRS As ADODB.Recordset
    Dim fieldIndex As Integer
    MyCommand.ActiveConnection = GetConn("userName", "Password", "DB", "Server")
    MyCommand.CommandType = adCmdText
    MyCommand.CommandText = query
    Set param = MyCommand.CreateParameter(, adVarChar, , 10, 10)
    MyCommand.Parameters.Append param
    Set retRS = MyCommand.Execute

no error but not working.
I'm using it over SQL Server
Boaz

  Hi,

  Do you get some sort of error message? What is it?

  --
  Val Mazur
  Microsoft MVP


    Hi!
    I am trying to use the Command.Parameters object.
    I have a very large SQL Insert statement and i wish to use the Command Parameters and not the RS!Field option.
    My code looks like:

    Public Const ADD_SUBSCRIBER_QUERY = "INSERT INTO SUBSCRIBERS(" & _
                                        "SUBSCRIBER_ID,CREATED_DATE,CREATED_BY)" & _
                                        "VALUES('?','?','?')"
    SubDataArr(0) = "1"
    SubDataArr(1) = "01/01/02"
    SubDataArr(2) = "Me"
    Dim MyCommand As New ADODB.Command
    Dim param As ADODB.Parameter
    Dim retRS As ADODB.Recordset
    Dim fieldIndex As Integer
    MyCommand.ActiveConnection = ConnObjDB2
    MyCommand.CommandType = adCmdText
    MyCommand.CommandText = ADD_SUBSCRIBER_QUERY
    'Run over all subscriber's data
    For fieldIndex = 0 To UBound(SubDataArr)
        Set param = MyCommand.CreateParameter(, adVarChar, adParamInputOutput, 50)
        param.value = SubDataArr(fieldIndex)
        MyCommand.Parameters.Append param
        Set param = Nothing
    Next fieldIndex
    Set retRS = MyCommand.Execute

    It is not working properly,
    Any help?



Sat, 07 May 2005 23:01:49 GMT  
 Need help with ADO Command.Parameters

Boaz,

How do you check if it is not working? Are you checking RecordCount property of recordset or some other way? Did you try to execute same query in Query Analyzer to see if it selects anything?

--
Val Mazur
Microsoft MVP


  I don't get any error message.
  I tried it with less complicated code:
      Dim query As String
      query = "select * from subscriber where subscriberid =?"
      Dim MyCommand As New ADODB.Command
      Dim param As New ADODB.Parameter
      Dim retRS As ADODB.Recordset
      Dim fieldIndex As Integer
      MyCommand.ActiveConnection = GetConn("userName", "Password", "DB", "Server")
      MyCommand.CommandType = adCmdText
      MyCommand.CommandText = query
      Set param = MyCommand.CreateParameter(, adVarChar, , 10, 10)
      MyCommand.Parameters.Append param
      Set retRS = MyCommand.Execute

  no error but not working.
  I'm using it over SQL Server
  Boaz

    Hi,

    Do you get some sort of error message? What is it?

    --
    Val Mazur
    Microsoft MVP


      Hi!
      I am trying to use the Command.Parameters object.
      I have a very large SQL Insert statement and i wish to use the Command Parameters and not the RS!Field option.
      My code looks like:

      Public Const ADD_SUBSCRIBER_QUERY = "INSERT INTO SUBSCRIBERS(" & _
                                          "SUBSCRIBER_ID,CREATED_DATE,CREATED_BY)" & _
                                          "VALUES('?','?','?')"
      SubDataArr(0) = "1"
      SubDataArr(1) = "01/01/02"
      SubDataArr(2) = "Me"
      Dim MyCommand As New ADODB.Command
      Dim param As ADODB.Parameter
      Dim retRS As ADODB.Recordset
      Dim fieldIndex As Integer
      MyCommand.ActiveConnection = ConnObjDB2
      MyCommand.CommandType = adCmdText
      MyCommand.CommandText = ADD_SUBSCRIBER_QUERY
      'Run over all subscriber's data
      For fieldIndex = 0 To UBound(SubDataArr)
          Set param = MyCommand.CreateParameter(, adVarChar, adParamInputOutput, 50)
          param.value = SubDataArr(fieldIndex)
          MyCommand.Parameters.Append param
          Set param = Nothing
      Next fieldIndex
      Set retRS = MyCommand.Execute

      It is not working properly,
      Any help?



Sat, 07 May 2005 23:10:14 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Need help with ADO Command.Parameters (Cont)

2. Need help on ADO commands and parameters

3. SQL Server ADO Command Parameters Problem - Please Help

4. command-line-parameter-help needed

5. Urgent Help Needed with ADO/Oracle Parameter Problem...

6. Urgent Help Needed ---> ADO/Oracle Parameter Passing

7. Need help with SQL Command for ADO

8. ADO Command to RecordSet Help Needed

9. multiple ADO command parameters

10. ADO command parameter syntax

11. ADO: Command with parameters

12. ADO: commands and parameters

 

 
Powered by phpBB® Forum Software