i m writing a stored procudrue to update my data that is onthertable.and i pass the parameter in my vb code,when i pass the data thatis insert only first record of data but second record insert the eroorwill come is data reader is colsed. now insted of data reade i have touse data set how can i use that and update my data is ontehrtable.?below i written my vb.net2005 code.
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
' con.Open()
' Dim ggrnid As String
' Dim acceptqty As String
' Dim itemid As String
' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox)
' ggrnid = grnid.Text
' Dim sWhere As String = grnid.Text
' If (Not String.IsNullOrEmpty(sWhere)) Then
' For Each s As String In sWhere '
' 'Dim iRowIndex As Integer = Convert.ToInt32(s)
' Dim sqldtr As SqlDataReader
' sqlcmd = New SqlCommand
' sqlcmd.Connection = con
' sqlcmd.CommandType = CommandType.Text
' sqlcmd.CommandText = "select acceptqty,itemid fromgrndetail where grnid='" & Trim(ggrnid) & "'"
' datacommand = CommandType.StoredProcedure
' 'datacommand("aaceptqtygrn", con)
' Dim cmd As New SqlCommand("aaceptqtygrn", con)
' sqldtr = sqlcmd.ExecuteReader()
' 'dataset = datacommand.
' 'sqldtr = sqlcmd.ExecuteScalar
' If sqldtr.HasRows = True Then
' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()
' End While
' 'sqldtr.Close()
' 'cmd.ExecuteNonQuery()
' 'Next sqldtr.HasRows
' End If
' Next s
' sqldtr.Close()
' con.Close()
' End If
' End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
prajapatiamit2003:
' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()
' End While
It looks like you are closing your data reader before exiting you while loop. And you probably do this because your stored proc wont execute while you have the data reader open.
You can either ececute the stored procedure ona different connection, or you can store the results of the reader in some kind of list/array/collection close the reader and then iterate through the collection to execute the sproc.
Because the datareader maintains an open connection to the database you cannot use that connection while the reader is open.
|||But how can i store in array of my data!