Array...
I having problem get all data from each SQL statement using. i able to get the data but when i insert into table, is only give me the last data and not all. FYI, i need to get different data from 11 criterias as below :-
SQLStr = "Select customerid from rptroda_dailycount where in_date >= '" & DateFrom & "' And in_date <= '" & DateTo & "' and hit_rate = 1"
Set Rs = DBConn.Execute(SQLStr)
If Rs.RecordCount <> 0 And Not Rs.BOF Then
intCount1 = Rs(0)
end if
Do While Not Rs.EOF
intCount1 = Rs(0)
Rs.MoveNext
Loop
(from the SQL statement, hit_rate will ended by >= 11. That's mean there will be 1,2,3...10, >11 for the hit_rate)
after get all the data from different criteria. it will insert all the data into a table as below:-
SQLStr = "INSERT INTO RPTRODA_COUNT VALUES('" & intCount1 & "','" & intCount2 & _
"" & "', '" & intCount3 & "', '" & intCount4 & "','" & intCount5 & "','" & intCount6 & "','" & intCount7 & "" & "', '" & intCount8 & "','" & intCount9 & "','" & intCount10 & "','" & intCount11 & "','" & date_from & "','" & date_to & "')"
DBConn.Execute (SQLStr)
DBConn.Execute ("Commit")
The result is i able to insert data but is only given last data. The data is more than 1...
Regards,
Wilson Chai
[1519 byte] By [
cyseng10] at [2007-11-15 16:15:48]
# 1 Re: Array...
Hello :
If understand you correctly. You may want to use Adodc1.Recordset.AddNew.
In my program. I use addNew to help copy data from Access table to Oracle.
Private Sub Command1_Click()
On Error Resume Next
Do Until Adodc2.Recordset.EOF
If Adodc2.Recordset.BOF Then
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
Else
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
End If
Loop
If Adodc2.Recordset.EOF Then
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0).Value = Adodc2.Recordset.Fields(0).Value
Adodc1.Recordset.Fields(1).Value = Adodc2.Recordset.Fields(1).Value
Adodc1.Recordset.Fields(2).Value = Adodc2.Recordset.Fields(2).Value
Adodc2.Recordset.MoveNext
End If
End Sub
I hope this may help.
Email me if that does not work
# 2 Re: Array...
No, i not using the adodc but i need to grap the data from the 11 criteria and insert into another table. actually, i generate a report, so after insert the data i want into the table. the report will print out according from the table.
now, i having problem when insert into a table , is only give me the last data and not all data gather from each criteria. how do i to that?
Regards,
Wilson Chai
# 3 Re: Array...
Looking at your code you are never assigning values to intCount2-intCount11. You are just overwriting what is in intCount1 every time.
Use an array and have the code do the following.
dim intCount1()
dim i as long
i=0
Do While Not Rs.EOF
redim Preserve intCount1(i)
intCount1(i) = Rs(0)
i=i+1
Rs.MoveNext
Loop
Then use intcount1(0)/intcount1(1) etc to save the data back
TH1 at 2007-11-10 1:01:22 >
# 4 Re: Array...
Hi, you've labelled your posting 'Array..' and that's exactly what you need, why not try something like this:
dim aiCount(1 to 11) as integer
SQLStr = "Select customerid, hitrate from rptroda_dailycount where in_date >= '" & DateFrom & "' And in_date <= '" & DateTo & "' and hit_rate <= 11"
set Rs = DBConn.Execute(SQLStr)
while not (rs.eof or rs.bof)
aiCount(rs(hitrate).value)=val("0" & rs(0).value)
rs.movenext
wend
SQLStr = "INSERT INTO RPTRODA_COUNT VALUES('" & ai(1) & "','" & aiCount(2) & _
"" & "', '" & aiCount(3) & "', '" & aiCount(4) & "','" & aiCount(5) & "','" & aiCount(6) & "','" & aiCount(7) & "" & "', '" & aiCount(8) & "','" & aiCount(9) & "','" & aiCount(10) & "','" & aiCount(11) & "','" & date_from & "','" & date_to & "')"
DBConn.Execute (SQLStr)
If that's what you're trying to get at?
[This assumes that you only expect a single customerid per hitrate]
HTH
# 5 Re: Array...
from your code, do i need to assign for each SQL statement or only on the insert data part.
Regards,
Wilson Chai
# 6 Re: Array...
from your code, it will get data based on the hit_rate = 1, 2 , 3...>=11.
the data will insert into from the count you given (1 to 11). FYI, there will a lot of data for each diffrent hit_rate.
Regards,
Wilson Chai
