Format for updating a field in vb.net

[cheruvannoor]

i want to get format for updating a field in vb.net.i am using sql as database.my code is given below



conn = New SqlConnection
conn.ConnectionString = "data source=localhost;initial catalog=CARGO;user id=sa;pwd="
conn.Open()

myconn = New SqlCommand
myconn.Connection = conn
query = "select * from agent"
myconn.CommandText = query
myreader = myconn.ExecuteReader()

myconn = New SqlCommand
myconn.Connection = conn
query = "update Agent set agentId = '" & Txtkey.Text & "',agentdate='" & Format(dtp.Value, "MM/dd/yyyy") & "',agentname= '" & Txtname.Text & "',agentadd1= '" & Txtadd1.Text & "',agentadd2= '" & Txtadd2.Text & "',agentadd3= '" & Txtadd3.Text & "',agentphone= '" & Txtphone.Text & "',agentfax= '" & Txtfax.Text & "',agentemail= '" & Txtemail.Text & "',agentiata= '" & Txtiata.Text & "',agentac= '" & Txtac.Text & "'"
myconn.CommandText = query
myconn.ExecuteNonQuery()
MsgBox("updated")



my error message is
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: There is already an open DataReader associated with this Connection which must be closed first."

 

[kulrom]

It is very confusing if i may ... because you are executing reader for nothing ... btw it should be closed right after you got a needed data.

i.e.

myreader = cmd.ExecuteReader()
While myreader.read
'fetch a data
End While
myreader.Close()

i assume you have done that expecting that it will be declared fot later usage but hey it is not case in ADO.NET ... before use the reader ones again first must be closed.

Cheers

 

[cheruvannoor]

i am getting the same error

 

[kulrom]

ok post the entire code of current sub (procedure)

Cheers

 

[cheruvannoor]

Format for updating a field in vb.net Reply to Thread

Is there anything wrong in the update format

Private Sub butnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butnfind.Click



conn = New SqlConnection
conn.ConnectionString = "data source=localhost;initial catalog=CARGO;user id=sa;pwd="
conn.Open()

myconn = New SqlCommand
myconn.Connection = conn
query = "select * from agent"
myconn.CommandText = query
myreader = myconn.ExecuteReader()





query = "update Agent set agentId = '" & Txtkey.Text & "',agentdate='" & Format(dtp.Value, "MM/dd/yyyy") & "',agentname= '" & Txtname.Text & "',agentadd1= '" & Txtadd1.Text & "',agentadd2= '" & Txtadd2.Text & "',agentadd3= '" & Txtadd3.Text & "',agentphone= '" & Txtphone.Text & "',agentfax= '" & Txtfax.Text & "',agentemail= '" & Txtemail.Text & "',agentiata= '" & Txtiata.Text & "',agentac= '" & Txtac.Text & "'"
myreader.Close()
myconn.CommandText = query
myconn.ExecuteNonQuery()


MsgBox("updated")

End Sub


thanks & regards
Cheruvannoor

 

[Blokz]

         myconn = New SqlCommand
        myconn.Connection = conn
        query = "select * from agent"
        myconn.CommandText = query
        myreader = myconn.ExecuteReader()

What are you doing with the result set from this?
You need to handle the result set while the reader is open before you update.
Such as:

While myreader.read
'Do something. I'm assuming you are filling in textboxes (or labels) for the following update command?
End While
myreader.Close

Now you can:

query = "update Agent set agentId = '" & Txtkey.Text & "',agentdate='" & Format(dtp.Value, "MM/dd/yyyy") & "',agentname= '" & Txtname.Text & "',agentadd1= '" & Txtadd1.Text & "',agentadd2= '" & Txtadd2.Text & "',agentadd3= '" & Txtadd3.Text & "',agentphone= '" & Txtphone.Text & "',agentfax= '" & Txtfax.Text & "',agentemail= '" & Txtemail.Text & "',agentiata= '" & Txtiata.Text & "',agentac= '" & Txtac.Text & "'"

Then:
myconn.CommandText = query
myconn.ExecuteNonQuery()

HTH

Blokz