Is a control array useful for INSERT into a table?

frresh · April 15th, 2007, 05:46 PM

I'm trying to capture data in my table. I'm not sure if I am going about capturing this data properly.I was hoping a control array might be a possible answer.At this point i'm not sure.When I aatempt to execute the app by clicking the 'ADD' button. I get 'Expected Array' at the Execute method line.(last line of code below)VB6 expects sSql to be an array of some type.I hoped the execute method would take into account that the data was being captured in a control array.I am aware I could simply use a stored proc to accomplish this task but I am experimenting. Should this work?And if so,how? Thank-you in advance for any insight.

---------------------------------------------------------------------
Option Explicit
Public rstAlianza As ADODB.Recordset
Private Sub Form_Load()
'Adding a row to my table should NOT require a recordset object since I am
'simply INSERTNG data to the tabe directly.
Dim cnn1 As ADODB.Connection
Dim connStr As String
'open ADO connection / DSN is data source name
connStr = "DSN=Alianza;UID = sa;PWD=pwd;"
Set cnn1 = New ADODB.Connection

'set rs=command.execu
End Sub

Private Sub Command1_Click()
Dim cnn1 As ADODB.Connection
Dim connStr As String
Dim sSql As String
Dim lRecordsAffected As Long
Dim i As Integer
cnn1.Open connStr
    'Construct SQL statement
    sSql = "INSERT INTO Authors(au_id,au_lname," _
        & "au_fname,phone,address,city,state,zip,contrac t)" _
    & "VALUES ('" _
    & Text1(0).Text & "','" _
    & Text1(1).Text & "','" _
    & Text1(2).Text & "','" _
    & Text1(3).Text & "','" _
    & Text1(4).Text & "','" _
    & Text1(5).Text & "')'"
cnn1.Execute sSql(lRecordsAffected, adCmdText) 'USING EMBEDDED SQL
End Sub

BrianWren · April 16th, 2007, 01:50 PM

Code:

Option Explicit

Public rstAlianza As ADODB.Recordset

Private Sub Form_Load()
    ' Adding a row to my table should NOT require a 
    ' recordset object since I am simply INSERTNG 
    ' data to the tabe directly.

    Dim cnn1    As ADODB.Connection
    Dim connStr As String

    ' Open ADO connection / DSN is data source name
    connStr = "DSN=Alianza;UID = sa;PWD=pwd;"
    Set cnn1 = New ADODB.Connection

    ' Set rs = command.execu

End Sub

Private Sub Command1_Click()

    Dim cnn1             As ADODB.Connection
    Dim connStr          As String
    Dim sSql             As String
    Dim lRecordsAffected As Long
    Dim i                As Integer

    cnn1.Open connStr

    ' Construct SQL statement
    sSql = "INSERT INTO Authors(au_id,au_lname, au_fname, " & _
                               "phone, address, city, state, " & _
                               "zip, contract)" & _
           "VALUES ('" & Text1(0).Text & "', " & _
                   "'" & Text1(1).Text & "', " & _
                   "'" & Text1(2).Text & "', " & _
                   "'" & Text1(3).Text & "', " & _
                   "'" & Text1(4).Text & "', " & _
                   "'" & Text1(5).Text & "') " & _
                   "'"                      ' the bold brown part is extraneous

    cnn1.Execute sSql(lRecordsAffected, adCmdText) ' Using embedded SQL
    ' the sSql(..., ...) syntax is being interpreted as a string
    ' array (  StrName(dimension_1, dimension_2) )

End Sub

frresh · April 16th, 2007, 09:33 PM

How can I correct this misinterpretation?

BrianWren · April 17th, 2007, 01:41 PM

Follow each argument with a comma.

Code:

    cnn1.Execute sSql, lRecordsAffected, adCmdText