David Megnin
asked on
Read items from SQL Table and populate a table of TextBoxes
I have a Purchase Requisition Form with 10 item rows.
When the form is submitted the general information, who submitted it, Department, etc. goes into the [Requisitions] table in MSSQL and the, up to 10, items go into the [Items] table, related on 'ReqID' which is the PK of the [Requisitions] table and FK of the [Items] table.
So, I have two tables of data that make up the requistions and their items.
I need to pull the data back into the form in case someone wants to re-print the form.
I've got a DataReader set up that pulls the base information from the Requistions table and puts it in the form. The Items are a bit trickier since there are multiple Item records for each "ReqID".
I'll post the "FetchRecord()" Sub, which works fine, but only grabs from the Requisitions table and I'll post the ADO.Net data Insert Sub that puts the data into the Items table so you can see what's got to come back out.
Fetch from [Requisitions] table and re-populate the form (but without the Items):
This is the input Sub that puts the Items from the form into the database. I need to reverse the procedure and pull the records back out and put them on the form again:
If someone could help me with this, I sure would appreciate it.
When the form is submitted the general information, who submitted it, Department, etc. goes into the [Requisitions] table in MSSQL and the, up to 10, items go into the [Items] table, related on 'ReqID' which is the PK of the [Requisitions] table and FK of the [Items] table.
So, I have two tables of data that make up the requistions and their items.
I need to pull the data back into the form in case someone wants to re-print the form.
I've got a DataReader set up that pulls the base information from the Requistions table and puts it in the form. The Items are a bit trickier since there are multiple Item records for each "ReqID".
I'll post the "FetchRecord()" Sub, which works fine, but only grabs from the Requisitions table and I'll post the ADO.Net data Insert Sub that puts the data into the Items table so you can see what's got to come back out.
Fetch from [Requisitions] table and re-populate the form (but without the Items):
Protected Sub FetchRecord()
Dim DBConn As New SqlConnection("connection string")
Dim DBCmd As New SqlCommand
Try
DBConn.Open()
DBCmd = New SqlCommand("SELECT DateIssued, DeliverTo, TotalDollars, ReasonForRequest, ISNULL(Remarks,'') AS Remarks, ISNULL(Department,'') AS Department, VendorDate, VendorInfoID, ISNULL(VendorPONO,'') AS VendorPONO, VendorFundingCode, StateContract, StateContractNo, StateContractEndDate, SubmittedBy, DateSubmitted FROM Requisitions WHERE ReqID='" & Session("ReqID") & "'", DBConn) 'Check quotes!
Dim dr As SqlDataReader = DBCmd.ExecuteReader
dr.Read()
lblPurchaseRequisitionNumber.Text = Session("ReqID")
txtDateIssued.Text = dr("DateIssued")
txtDeliverTo.Text = dr("DeliverTo")
txtTOTAL.Text = dr("TotalDollars")
txtReasonForRequest.Text = dr("ReasonForRequest")
txtRemarks.Text = dr("Remarks")
txtDateOfPO.Text = dr("VendorDate")
txtVendorID.Text = dr("VendorInfoID")
txtPONO.Text = dr("VendorPONO")
txtFundingCodeInfo.Text = dr("VendorFundingCode")
If Not dr("StateContract") = String.Empty Then
radStateContract.SelectedValue = dr("StateContract")
Else
radStateContract.SelectedIndex = -1
End If
txtStateContractNo.Text = dr("StateContractNo")
If Not ((dr("StateContractEndDate")) Is DBNull.Value) Then
txtStateContractEndDate.Text = Date.Parse(dr("StateContractEndDate"))
Else
txtStateContractEndDate.Text = String.Empty
End If
Catch ex As Exception
txtDescription_1.Text = ex.Message
End Try
DBCmd.Dispose()
DBConn.Close()
DBConn = Nothing
End Sub
This is the input Sub that puts the Items from the form into the database. I need to reverse the procedure and pull the records back out and put them on the form again:
Protected Sub SendItemsToDatabase()
Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
Dim DBCmd As New SqlCommand
Dim sql As String = ""
Dim arr As New ArrayList
For i As Integer = 1 To 10
Dim oTxt_Total As TextBox = CType(Me.form1.FindControl("txtTotal_" & i), TextBox)
If oTxt_Total IsNot Nothing Then
If oTxt_Total.Text <> "0.00" AndAlso oTxt_Total.Text <> "" Then
arr.Add(i)
sql &= " INSERT INTO Items (ItemNumber, ReqID, Quantity, Description, UnitPrice, SubTotal) VALUES (" & i & ", @varReqID, @txtQTY_" & i & ", @txtDescription_" & i & ", @txtUNIT_" & i & ", @txtTotal_" & i & ")"
End If
End If
Next
DBConn.Open()
Try
If sql <> "" Then
DBCmd = New SqlCommand(sql, DBConn)
DBCmd.Parameters.Add("@varReqID", SqlDbType.VarChar).Value = vReqID 'Same ReqID as parent order. (Foreign Key in SQL table)
For Each i As Integer In arr
Dim oTxt_QTY As TextBox = CType(Me.form1.FindControl("txtQTY_" & i), TextBox)
DBCmd.Parameters.Add("@txtQTY_" & i, SqlDbType.VarChar).Value = oTxt_QTY.Text
Dim oTxt_Desc As TextBox = CType(Me.form1.FindControl("txtDescription_" & i), TextBox)
DBCmd.Parameters.Add("@txtDescription_" & i, SqlDbType.VarChar).Value = oTxt_Desc.Text
Dim oTxt_Unit As TextBox = CType(Me.form1.FindControl("txtUNIT_" & i), TextBox)
DBCmd.Parameters.Add("@txtUNIT_" & i, SqlDbType.VarChar).Value = oTxt_Unit.Text
Dim oTxt_Total As TextBox = CType(Me.form1.FindControl("txtTotal_" & i), TextBox)
DBCmd.Parameters.Add("@txtTotal_" & i, SqlDbType.VarChar).Value = oTxt_Total.Text
Next
End If
DBCmd.ExecuteNonQuery()
Catch exp As Exception
Response.Write(exp.Message)
End Try
DBCmd.Dispose()
DBConn.Close()
DBConn = Nothing
End Sub
If someone could help me with this, I sure would appreciate it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh, nice. I'll give that a try. I was about to start working on another priority job, but that looks simle enough to go ahead and knock it out. ...
ASKER
To add that to my existing "FetchRecord()" code do I just put it after the existing End Try, DBConn.Close, etc. ? This is more complicated than I'm used to working with. THanks.
ASKER
Hmmm, I see, no. It looks like it will have to be "merged" with the existing FetchRecord() sub.
Maybe use the existing DBConn connection, create a second "Sql" (Sql2) variable for the new query, a second DataReader (dr2) and do everything within the existing Try / Catch? Does that sound right?
Maybe use the existing DBConn connection, create a second "Sql" (Sql2) variable for the new query, a second DataReader (dr2) and do everything within the existing Try / Catch? Does that sound right?
ASKER
Here's what I've done. It pulls the Requisition fields in fine still and it pulls the first and the last item regardless of how many items there are in the table. One item, it puts one on the form; two, it puts two on the form; three it puts the first and last on the form; five it puts the first and last on the form.
Protected Sub FetchRecord()
Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
Dim DBCmd As New SqlCommand 'For Requisition
Dim Sql As String = "SELECT DateIssued, DeliverTo, TotalDollars, ReasonForRequest, ISNULL(Remarks,'') AS Remarks, ISNULL(Department,'') AS Department, VendorDate, VendorInfoID, ISNULL(VendorPONO,'') AS VendorPONO, VendorFundingCode, StateContract, StateContractNo, StateContractEndDate, SubmittedBy, DateSubmitted FROM Requisitions WHERE ReqID='" & Session("ReqID") & "'"
Dim iDBCmd As New SqlCommand 'For Items
Dim iSql As String = "Select ItemNumber, ReqID, Quantity, Description, UnitPrice, SubTotal from Items where ReqID = " + Session("ReqID").ToString() 'For Items
'Grab Requisition fields
Try
DBConn.Open()
DBCmd = New SqlCommand(Sql, DBConn)
Dim dr As SqlDataReader = DBCmd.ExecuteReader
dr.Read()
lblPurchaseRequisitionNumber.Text = Session("ReqID")
txtDateIssued.Text = dr("DateIssued")
txtDeliverTo.Text = dr("DeliverTo")
txtTOTAL.Text = dr("TotalDollars")
txtReasonForRequest.Text = dr("ReasonForRequest")
txtRemarks.Text = dr("Remarks")
txtDateOfPO.Text = dr("VendorDate")
txtVendorID.Text = dr("VendorInfoID")
txtPONO.Text = dr("VendorPONO")
txtFundingCodeInfo.Text = dr("VendorFundingCode")
If Not dr("StateContract") = String.Empty Then
radStateContract.SelectedValue = dr("StateContract")
Else
radStateContract.SelectedIndex = -1
End If
txtStateContractNo.Text = dr("StateContractNo")
If Not ((dr("StateContractEndDate")) Is DBNull.Value) Then
txtStateContractEndDate.Text = Date.Parse(dr("StateContractEndDate"))
Else
txtStateContractEndDate.Text = String.Empty
End If
dr.Close()
'Grab the Items:
iDBCmd = New SqlCommand(iSql, DBConn)
Dim idr As SqlDataReader = iDBCmd.ExecuteReader
Dim i As Integer = 1
While idr.Read()
Dim oTxt_QTY As TextBox = CType(Me.form1.FindControl("txtQTY_" & i), TextBox)
oTxt_QTY.Text = idr("Quantity")
'repeat for other fields.
Dim oTxt_Desc As TextBox = CType(Me.form1.FindControl("txtDescription_" & i), TextBox)
oTxt_Desc.Text = idr("Description")
Dim oTxt_Unit As TextBox = CType(Me.form1.FindControl("txtUNIT_" & i), TextBox)
oTxt_Unit.Text = idr("UnitPrice")
Dim oTxt_Total As TextBox = CType(Me.form1.FindControl("txtTotal_" & i), TextBox)
oTxt_Total.Text = idr("SubTotal")
i = 1 + 1
End While
idr.Close()
Catch ex As Exception
txtDescription_1.Text = ex.Message
End Try
DBCmd.Dispose()
DBConn.Close()
DBConn = Nothing
End Sub
ASKER
Oh, I found the problem there...
it should be
i = i +1
not
i = 1 + 1 ;-)
it should be
i = i +1
not
i = 1 + 1 ;-)
Looks like you have found a solution but I would use a DataGridView in this situation.
ASKER
That might be possible, but I can't imagine how I would be able to customize the DataGridView (GridView?) for the input form which also has to be printed and routed for signatures.
I agree it would make data in and out easiers, but I'd never get it to look like we want it and the subtotal calculations I'm sure are possible, but It would take me forever to figure out how to do it. ;-)
5-12-2011-7-30-37-AM.png
I agree it would make data in and out easiers, but I'd never get it to look like we want it and the subtotal calculations I'm sure are possible, but It would take me forever to figure out how to do it. ;-)
5-12-2011-7-30-37-AM.png
ASKER
Thanks a lot. This saved me another day of work. I told my boss that it was complicated and I'd have to continue working on it after this other project was done, but I was able to finish it last night with your help. Thanks a bunch!! :-)
sql = " Select ItemNumber, ReqID, Quantity, Description, UnitPrice, SubTotal from Items where ReqID" = Session("ReqID")
DBCmd = New SqlCommand(sql, DBConn)
DBConn.Open
dim i as integer = 1
dim dr As DataReader = DBCmd.ExecuteReader()
while dr.Read()
Dim oTxt_QTY As TextBox = CType(Me.form1.FindControl
oTxt_QTY.Text = dr("Quantity")
'repeat for other fields.
i = 1+1
end while
conn.close()