Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

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):
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

Open in new window


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

Open in new window


If someone could help me with this, I sure would appreciate it.
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
 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("txtQTY_" & i), TextBox)
   oTxt_QTY.Text = dr("Quantity")
   'repeat for other fields.
   i = 1+1
  end while
  conn.close()
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Megnin

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.  ...
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.
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?
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

Open in new window

Oh, I found the problem there...

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.
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
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!!  :-)