Using Transaction then SQL DataAdapter

[Lee21]

Hi Everyone, I'm having a hard time on using Transaction then after that I need to use
sqldataadapter. When I tried the code below I'm having an error "ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized." Pointing to this block..

dtemp = New DataTable
sda = New SqlDataAdapter("SELECT DISTINCT EMPL_ID,YEARNUM,MONTHNUM,WKNUM FROM KREPORTS WHERE CREA_DATE = '" & DateValue(modGlobalvar.dtime) & "' AND STATE='NEW'", sqlcon)
sda.Fill(dtemp)

I already tried using different connection for the transaction and for the sqldataadapter but it doesn't work. Please help me on this one.

scmd = New SqlCommand
sqlcon.Open()
sqltrans = sqlcon.BeginTransaction(IsolationLevel.Serializable, "t1")

scmd.Connection = sqlcon
scmd.Transaction = sqltrans

While total <> dt.Rows.Count
scmd.CommandText = "INSERT INTO KREPORTS(EMPL_ID,EMPL_LNAME,EMPL_FNAME,KPIPOSTCODE,YEARNUM,MONTHNUM,WKNUM,KPIDTLCODE,TALLY,KPISOURCETYPE,STATE,CREA_BY,CREA_DATE,CREA_TIME) VALUES('" & dt.Rows(total).Item(0) & "','" & dt.Rows(total).Item(1) & "','" & dt.Rows(total).Item(2) & "','" & dt.Rows(total).Item(3) & "','" & dt.Rows(total).Item(4) & "','" & dt.Rows(total).Item(5) & "','" & dt.Rows(total).Item(6) & "','" & dt.Rows(total).Item(7) & "','" & dt.Rows(total).Item(8) & "','" & dt.Rows(total).Item(9) & "','NEW','" & modGlobalvar.name & "','" & DateValue(modGlobalvar.dtime) & "','" & TimeValue(modGlobalvar.dtime) & "')"
scmd.ExecuteNonQuery()
total += 1
End While

dtemp = New DataTable
sda = New SqlDataAdapter("SELECT DISTINCT EMPL_ID,YEARNUM,MONTHNUM,WKNUM FROM KREPORTS WHERE CREA_DATE = '" & DateValue(modGlobalvar.dtime) & "' AND STATE='NEW'", sqlcon)
sda.Fill(dtemp)

[adam_k]

I believe that you are getting the error because you are starting the transaction in the SQL command and not the connection.
I don't have .Net available right now to check and present you the code, but I would try to start the transaction with the connection and use that connection with both the scmd and sda.

If you can't make it work, drop me a private message and I'll get to a PC with VB .NET

PS: I trust you'll fill in error handling and rollback/commit later or is happening further down your code.