Populate Form from SQL Server

[MIkeC]

Apologies if this has already been posted.

I am coding in VB

I am trying to find the best way to populate a form from a sql Database in .net 2005

I have a company table that has > 20 columns, I will be selecting a specific row. I have populated the form with textboxes that are named so that they reflect the column names of the company table.

I want to select the row, populate the form by looping through the columns.

I do not want to use a datagrid. I will also want to update the database with any changes.

Any help will be Appreciated

[balabaster]

Apologies if this has already been posted.

I am coding in VB

I am trying to find the best way to populate a form from a sql Database in .net 2005

I have a company table that has > 20 columns, I will be selecting a specific row. I have populated the form with textboxes that are named so that they reflect the column names of the company table.

I want to select the row, populate the form by looping through the columns.

I do not want to use a datagrid. I will also want to update the database with any changes.

Any help will be Appreciated

I'm assuming you're using the SqlConnection/SqlCommand to query the database. I'm not sure if there's a way to obtain a field name from the DataReader class, and there's only two overloads for accessing a field, either by index or by name. So tying the field from your DataReader with a textbox may not be as straightforward as you would like:

Dim oFieldName() As String
'Make sure you populate this array with your fieldnames - you'll need to
'specify an array limit; you could also modify the array and set the
'values to the control indices of the textboxes to be updated.
 
Dim oDR As SqlDataReader = oCmd.ExecuteReader()
If oDR.HasRows Then
  While oDR.Read
    For i As Integer = 0 To oRdr.FieldCount - 1
      Me.Controls(FieldName(i)).Text = oRdr(i)
    Next
  End While
End If
oCmd.Dispose()

An alternative approach may be to use an SqlDataAdapter to populate a DataTable. You can then run a For-Each on the DataColumns within the table and reference the ColumnName. This won't perform quite as well as the DataReader, but will provide you with more flexibility.

Dim oCon As New SqlConnection("Driver=....")
Dim sSQL As String = "Select Fields From Table..."
Dim oDA As New SqlDataAdapter(sSQL, oCon)
Dim oDT As New DataTable
oDA.Fill(oDT)
Dim i As Integer = 0 'Row we wish to extract data from
If oDT.Rows.Count > 0 Then
  For Each oCol As DataColumn In oDT.Columns
    Me.Controls(oCol.ColumnName).Text = oDT.Rows(i).Item(oCol.Ordinal).ToString
  Next
End If

For the auto-update, you'd have to set the autopostback on each of the textboxes. I would recommend using an AJAX extender on them so that the update on the database is run in the background giving a more fluid user experience.

[MIkeC]

Thanks for the Rapid reply.

I have a couple of problems. It seems that Autopostback is an ASP feature I am coding in VB. sorry If this is wrong

The form will only display 1 row which is controlled by the Where clause. Ideally I will have a button on the form that will perform the Update code and close the form. I was looking for an update command that will commit all changes back to database

Thanks in anticipation

Mike

[balabaster]

Private sCon As String = "Server=.\SQL2005DEV;Database=Dummy;Trusted_Connection=yes;"
Private sSelect As String = "Select KeyField, Data1, Data2, Data3 From Dummy Where KeyField = 239"
Private oCon As SqlClient.SqlConnection
Private oDA As SqlClient.SqlDataAdapter
Private oQB As SqlClient.SqlCommandBuilder
Private oDT As DataTable
Private _CurrentRec As Integer = -1
 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
  oCon = New SqlConnection(sCon)
  oDA = New SqlDataAdapter(sSelect, oCon)
  oQB = New SqlCommandBuilder(oDA)
  oDT = New DataTable("Person")
  oDT.Columns.Add("KeyField", GetType(Integer))
  oDT.Columns.Add("Data1", GetType(String))
  oDT.Columns.Add("Data2", GetType(String))
  oDT.Columns.Add("Data3", GetType(String))
 
  Dim oKeys(0) As DataColumn
  oKeys(0) = oDT.Columns("KeyField")
  oDT.PrimaryKey = oKeys
 
  oDA.Fill(oDT)
 
  If oDT.Rows.Count > 0 Then
    _CurrentRec = oDT.Rows(0).Item(0)
    TextBox1.Text = oDT.Rows(0).Item(1).ToString
    TextBox2.Text = oDT.Rows(0).Item(2).ToString
    TextBox3.Text = oDT.Rows(0).Item(3).ToString
  End If
 
End Sub
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
  Dim oRow As DataRow = oDT.Rows.Find(_CurrentRec)
  oRow.Item(1) = TextBox1.Text
  oRow.Item(2) = TextBox2.Text
  oRow.Item(3) = TextBox3.Text
  oDA.Update(oDT)
 
End Sub

This isn't quite what you were looking for but it's close enough that you should be able to figure out where to go from there...