CODEWITHSUNDEEP
sqlarraysvb.netloopsrows
Juan Ge
Juan Ge

Reputation: 13

SQL VB: Store query rows in DataArray but it saves only first row

i am trying to save the result of a query in a Data Array, Columns name does it fine However ONLY THE FIRST row is copied in ALL the positions os the data array for the rows. Any idea?

Dim alarm

alarm = HMIRuntime.Tags("search").Read



    strSQL = "SELECT IdAlarma, Texto FROM dbo.Alarmas WHERE (Texto) LIKE '%" & CStr(alarm) & "%'"



'   Create The Connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.CursorLocation = 3
objConnection.Open
'   Attach The Record Set To The Connection
Set oRs = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")
objCommand.CommandType = 1
Set objCommand.ActiveConnection = objConnection

    HMIRuntime.Trace("CONEXION OK")


    objCommand.CommandText = strSQL
    Set oRs = objCommand.Execute

ColumnCount = oRs.Fields.Count 'The number of columns you want to display

RowCount = oRs.RecordCount 'The number of rows to display

'Resize the Array to hold the SQL Data Redim DataArray(ColumnCount - 1 ,RowCount)

Dim X,Y 
'Loop Used to get the field names
For X = 0 To ColumnCount - 1

            DataArray(X,0) = oRs.Fields(x).Name

Next



'Loop To Get all row the values
For Y = 1 To RowCount -1 


       'HERE IT SHOULD STORE THE DIFFERENT ROWS IN THE ARRAY
       'BUT ONLY THE FIRST ROW IS SAVED IN EVERY Y POSITION OF THE ARRAY???


            DataArray(0, Y) = oRs.Fields("IdAlarma").Value
            DataArray(1, Y) = oRs.Fields("Texto").Value



Next

Upvotes: 1

Views: 58

Answers (1)

Chicken
Chicken

Reputation: 428

In the last loop with increasing your 'Y's, it is itterating through the data array, but it isn't going through the records. You're basically calling for line 1 every time.

I don't know too much about ADOOB connections, however, in SQL you would use datareader, which would loop through each row.

After doing some googling, I think you need to add in ors.MoveNext() If you add this into the 'Y' loop like this

For Y = 1 To RowCount -1 
   'HERE IT SHOULD STORE THE DIFFERENT ROWS IN THE ARRAY
   'BUT ONLY THE FIRST ROW IS SAVED IN EVERY Y POSITION OF THE ARRAY???
        DataArray(0, Y) = oRs.Fields("IdAlarma").Value
        DataArray(1, Y) = oRs.Fields("Texto").Value
        ors.MoveNext()
Next

This should move to the next record in the oRs recordset, whilst also moving the data array.

Just quick questing but are you connecting to SQL or some other database? If you're using SQL, there are some nice easy tricks to use, and can make things alot easier for you! :)

Hth Chicken

Upvotes: 1

Related Questions