insert an array into mysql using vb.net vs vba

Question

I have retrieve all data from the internet into a 2 dimension array, I know how to use vba recordset and by filter and update using loop. Below is part of the code in vba.

the difficult problem is here:

Using cmd As New MySqlCommand("INSERT INTO `calls` (`CID`, `ctype`) VALUES (@CID, @ctype)", cnn)

This make me could not use any loop through the array and update accordingly.

cmd.Parameters.Add ('@CID').value = arrValue(i,j)

I hope this could be done in kind of below:

for x = 0 to ubound(arrValue,0)
      for y = 0 to ubound(arrValue,1)
          .fields(arrHeader(y) = arrValue(x,y)
      next y
   next x

say i, the n-th to be updated, j = the value of the header

extract of vba:

    With rs
            'Worksheets(strWsName).Activate
            'iLastRow = Worksheets(strWsName).Cells(65535, 1).End(xlUp).row              'column B, column 2
            For i = 0 To UBound(arrValue, 1)
                    Debug.Print "Updating " & i + 1 & " of " & UBound(arrValue, 1) + 1 & " news ..." ' of " & strCodeAB & " ..."
                    'Start looping the news row
                    strNewsID = arrValue(i, 1) 'Worksheets(strWsName).Range(ColRefNewsID & i).Value
                    If strNewsID = "" Then
                        Debug.Print i - 1 & " news is updated to database"
                        i = UBound(arrValue, 1)
                    Else
                        strFilter = "fID='" & strNewsID & "'"
                        rs.Filter = strFilter
                        If rs.EOF Then
                            .AddNew 'create a new record
                            'add values to each field in the record
                            For j = 0 To UBound(arrTitle_ALL)
                                '20140814
                                strFieldValue = .Fields(arrAccessField(j))
                                strNewValue = arrValue(i, j)
                                If IsNull(strFieldValue) And strNewValue = "" Then
                                Else
                                    .Fields(arrAccessField(j)) = arrValue(i, j) 'Worksheets(strWsName).Cells(i, j + 1).Value
                                End If
                            Next j
                            On Error Resume Next                                        '***************
                            .Update 'stores the new record
                            On Error GoTo 0
                            iCounterNewsAdded = iCounterNewsAdded + 1
                            glcounterNewsAdded = iCounterNewsAdded
                        Else

It seems that the below post similar to my request but I don't know how to do so. [reference]passing an Array as a Parameter to be used in a SQL Query using the "IN" Command

Steve · Accepted Answer · 2014-08-20 22:47:49Z

0

The post you mention (Using the IN() command) works in your WHERE clause but it is not valid for values. MySQL has no way to pass an array so you need to loop through your array and run multiple INSERT statements:

for y = 0 to ubound(arrValue,1)
    cmd.Parameters.AddWithValue(@CID,arrValue(0,y))
    cmd.Parameters.AddWithValue(@ctype,arrValue(1,y))
    cmd.ExecuteNonQuery
next y

answered Aug 20, 2014 at 22:47

Steve

5,5553 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trader Over a year ago

Is it possible to use the data set as if vba like .fields('field name') = arrvalue(x,y) as I could do so in vba with MySQL.

Steve Over a year ago

MySQL does not offer a way to pass a dataset/datatable or an array. Also, what your asking for makes no sense. You want X number of values in a single record/column. Perhaps you want a comma separated string. BTW, I have passed comma separated strings to SP's and then parsed them in the SP and created multiple records but there is no other way.

Trader Over a year ago

Just for clarification, X is no of news -> arrValue(X,1-13), means the x-th news with 14 values for 14 fields. That one is working fine in my vba to mysql currently, I see why it makes no sense provided the code is working fine. Besides, for insert into, do you mean i could turn my array of each no. of news into a comma separated string so that I could do the following like: strAllVAlues = "'ID1','Equity'" ("INSERT INTO calls (CID, ctype) VALUES (strAllValues)", cnn) Thanks for your contribution.

Collectives™ on Stack Overflow

insert an array into mysql using vb.net vs vba

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related