problem in inserting records in second table
Hello friends,
I am having two tables in a database where in first table where i am having four fields number, subject1, subject2, subject3. I am having another table which is having only two fields number, total marks. In vb my code to accomplish this follows:-
dim conn as new adodb.connection
dim rec1 as new adodb.recordset
dim cmd as new adodb.command
private sub form_load()
conn.connection string= " syntax for runtime connection is mentioned here"
conn.open
rec1.open "select number, sum(subject1 + subject2 +subject3) from table1 group
by number", conn, adopendynamic, adlockoptimistic"
rec1.movefirst
end sub
private sub command_click()
while (rec1.EOF = True)
cmd. commandtype = adcmdtext
cmd.commandtext = "Insert into table2(number, total marks) values("&("rec1.fields(0).value")&, &("rec1.fields(1).value")&")"
cmd.execute
rec1.movenext
wend
I think there is some error in line where Insert query has been issued but i never used an embedded insert command. I don't know why data is not inserted into second table....
any help will be appreciated.
thank you,
# 1 Re: problem in inserting records in second table
try this...
private sub command_click()
cmd.ActiveConnection = conn
while (rec1.EOF = True)
cmd. commandtype = adcmdtext
cmd.commandtext = "Insert into table2(number, total marks) values("&("rec1.fields(0).value")&, &("rec1.fields(1).value")&")"
cmd.execute
rec1.movenext
wend
Shuja
# 5 Re: problem in inserting records in second table
Of sorry, i just didn't see it...
try this one..
While (rec1.EOF = False)
cmd.commandtype = adcmdtext
cmd.commandtext = "Insert into table2(number, total marks) Values(" & rec1.fields(0).Value & "," & rec1.fields(1).Value & ")"
cmd.execute
rec1.movenext
Wend
check i have changed the looping condition.. your looping condition was wrong...
if this doesn't colve the problem, then you should post some more code, so that we are able to c what exactly is happening...
Shuja
# 6 Re: problem in inserting records in second table
hi shuja,
I have modified the code with rec1.
rec1.open "insert into table2(number, totalmarks) select table1.number, count(3 fileds) from table1 group by table1.number", conn, ........remaining syntax.
i included this in formload and deleted the command button option.
it is working.
thanks for the support,
varsha
# 7 Re: problem in inserting records in second table
one better way of doing this is to write the query which will append the records from one table in to another..
Actually when you open a recordset and then loop through it, it is somewhat time consuming...
but when you use a query directly, it is faster and needs less code...
below is the Example
Private Sub form_load()
conn.connection string= " syntax for runtime connection is mentioned here"
conn.open
conn.execute "INSERT INTO Table2 ( [Number], [Total] ) SELECT Table1.Number, Sum(Subject1 +Subject2 + Subject3) FROM Table1 Group By Table1.Number"
End Sub
this query will insert all your records in to the other table...
this is much more efficient than other solutions...
PS: When you post something, please be as specific as possible, because this helps others in solving the problem quickly...
Shuja--:wave:
