Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

DataGridView populate

shersshers Posts: 7Member
Hi,

I'm working on VB.NET Express and SQL Server Express. I have a DataGridView control in the form, with 8 columns, of which one column, which has default values, is hidden and one column is already filled with default values. I want to populate the DataGridView with records that exists in the database by selecting the record, based on the conditions in the SQL statement. And the record should be displayed in order of the hidden column values, without disturbing the default value column that is not hidden. How can I accomplish this? I have done some code, but it is not working. Here it is:

[CODE] Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Me.DialogResult = System.Windows.Forms.DialogResult.OK
'bolOpen = True

Dim strProj As String
strProj = LV.SelectedItems(0).Text
Dim strItem(2) As String
strItem = strProj.Split("-")

With EntryFrm
.txtRFS.Text = strItem(0)
.txtPName.Text = strItem(1)
End With

Dim strCon As String = My.Settings.ConnectString
Dim sqlConn As New SqlConnection(strCon)
Dim Cmd As SqlCommand

Dim DeptID As Integer

DeptID = cboDept.SelectedIndex.ToString + 1

Cmd = New SqlCommand("SELECT SDATE, EDATE, TLHRS, PHRS, THRS, COMP FROM PDETAILS WHERE RFS = " & strItem(0) & _
" AND DEPTID = " & DeptID & " ORDER BY PHASEID", sqlConn)

Dim DA As SqlDataAdapter
Dim DS As New DataSet

DA = New SqlDataAdapter(Cmd)
DS = New DataSet

DA.Fill(DS, "PDetails")

EntryFrm.DGV.DataSource = DS
EntryFrm.DGV.DataMember = "PDetails"

Me.Close()

End Sub[/CODE]
«1

Comments

  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    : Hi,
    :
    : I'm working on VB.NET Express and SQL Server Express. I have a
    : DataGridView control in the form, with 8 columns, of which one
    : column, which has default values, is hidden and one column is
    : already filled with default values. I want to populate the
    : DataGridView with records that exists in the database by selecting
    : the record, based on the conditions in the SQL statement. And the
    : record should be displayed in order of the hidden column values,
    : without disturbing the default value column that is not hidden. How
    : can I accomplish this? I have done some code, but it is not working.
    : Here it is:
    :
    : [CODE] Private Sub OK_Button_Click(ByVal sender As System.Object,
    : ByVal e As System.EventArgs) Handles OK_Button.Click
    : Me.DialogResult = System.Windows.Forms.DialogResult.OK
    : 'bolOpen = True
    :
    : Dim strProj As String
    : strProj = LV.SelectedItems(0).Text
    : Dim strItem(2) As String
    : strItem = strProj.Split("-")
    :
    : With EntryFrm
    : .txtRFS.Text = strItem(0)
    : .txtPName.Text = strItem(1)
    : End With
    :
    : Dim strCon As String = My.Settings.ConnectString
    : Dim sqlConn As New SqlConnection(strCon)
    : Dim Cmd As SqlCommand
    :
    : Dim DeptID As Integer
    :
    : DeptID = cboDept.SelectedIndex.ToString + 1
    :
    : Cmd = New SqlCommand("SELECT SDATE, EDATE, TLHRS, PHRS,
    : THRS, COMP FROM PDETAILS WHERE RFS = " & strItem(0) & _
    : " AND DEPTID = " & DeptID & " ORDER BY PHASEID", sqlConn)
    :
    : Dim DA As SqlDataAdapter
    : Dim DS As New DataSet
    :
    : DA = New SqlDataAdapter(Cmd)
    : DS = New DataSet
    :
    : DA.Fill(DS, "PDetails")
    :
    : EntryFrm.DGV.DataSource = DS
    : EntryFrm.DGV.DataMember = "PDetails"
    :
    : Me.Close()
    :
    : End Sub[/CODE]
    :

    If you want to preserve values, the easiest would be to add the values manually rather than use the DataSource and DataMember property.
    I don't know about the sort - maybe you can call a Sort() function specifying a column, but else I think you can create your own sorter class.

    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • shersshers Posts: 7Member
    : If you want to preserve values, the easiest would be to add the
    : values manually rather than use the DataSource and DataMember
    : property.
    : I don't know about the sort - maybe you can call a Sort() function
    : specifying a column, but else I think you can create your own sorter
    : class.
    :
    : Best Regards,
    : Richard
    :
    : The way I see it... Well, it's all pretty blurry

    Thanks for the reply. I tried a lot of search, but couldn't find how to go about it. Could you please help me, on how that can be done?

    Thanks
  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    : : If you want to preserve values, the easiest would be to add the
    : : values manually rather than use the DataSource and DataMember
    : : property.
    : : I don't know about the sort - maybe you can call a Sort() function
    : : specifying a column, but else I think you can create your own sorter
    : : class.
    : :
    : : Best Regards,
    : : Richard
    : :
    : : The way I see it... Well, it's all pretty blurry
    :
    : Thanks for the reply. I tried a lot of search, but couldn't find how
    : to go about it. Could you please help me, on how that can be done?
    :
    : Thanks
    :

    Just set the RowCount and Column count to appropriate values and use Items(x,y) to address each cell.

    So basically, what you'll do is loop through the found records and at each entry to the grid view.


    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • shersshers Posts: 7Member

    : Just set the RowCount and Column count to appropriate values and use
    : Items(x,y) to address each cell.
    :
    : So basically, what you'll do is loop through the found records and
    : at each entry to the grid view.
    :
    :
    : Best Regards,
    : Richard
    :
    : The way I see it... Well, it's all pretty blurry

    I would like to know if it is possible to enter the data from the DataSet row by row. If not, how do I loop through the DataSet? Could you please give me an example?

    Thanks
  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    From MSDN Library:

    The following code example demonstrates how to initialize a simple data-bound DataGridView. It also demonstrates how to set the DataSource property. To run this example, paste the following code into a form that contains a DataGridView named dataGridView1, replace the value of the connectionString variable specified in the code with a string that is valid for the system the example will run on, and call the InitializeDataGridView method from the form's constructor or Load event handler.

    [code]
    Private Sub InitializeDataGridView()
    Try
    ' Set up the DataGridView.
    With Me.dataGridView1
    ' Automatically generate the DataGridView columns.
    .AutoGenerateColumns = True

    ' Set up the data source.
    bindingSource1.DataSource = GetData("Select * From Products")
    .DataSource = bindingSource1

    ' Automatically resize the visible rows.
    .AutoSizeRowsMode = _
    DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders

    ' Set the DataGridView control's border.
    .BorderStyle = BorderStyle.Fixed3D

    ' Put the cells in edit mode when user enters them.
    .EditMode = DataGridViewEditMode.EditOnEnter
    End With
    Catch ex As SqlException
    MessageBox.Show("To run this sample replace " _
    & "connection.ConnectionString with a valid connection string" _
    & " to a Northwind database accessible to your system.", _
    "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    System.Threading.Thread.CurrentThread.Abort()
    End Try
    End Sub

    Private Shared Function GetData(ByVal sqlCommand As String) _
    As DataTable

    Dim connectionString As String = _
    "Integrated Security=SSPI;Persist Security Info=False;" _
    & "Initial Catalog=Northwind;Data Source=localhost"

    Dim northwindConnection As SqlConnection = _
    New SqlConnection(connectionString)

    Dim command As New SqlCommand(sqlCommand, northwindConnection)
    Dim adapter As SqlDataAdapter = New SqlDataAdapter()
    adapter.SelectCommand = command

    Dim table As New DataTable
    table.Locale = System.Globalization.CultureInfo.InvariantCulture
    adapter.Fill(table)

    Return table

    End Function
    [/code]
    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • gangrejagangreja Posts: 9Member
    hi, im new in vb.net development and i`d like some help if someone could. i took the above code (DataGridView populate Posted by BitByBit_Thor on 2 Oct 2007 at 12:18 AM ) and change it into this:

    Imports System.Windows.Forms.TextBox
    Imports System.Windows.Forms.ComboBox
    Imports System.Data
    Imports System.Data.SqlClient.SqlCommand




    Public Class Form1


    Public Sub InitializeDataGridView()
    Try
    ' Set up the DataGridView.
    With Me.DataGridView1
    ' Automatically generate the DataGridView columns.
    .AutoGenerateColumns = True

    ' Set up the data source.
    BindingSource1.DataSource = GetData("Select * From student Where studentID = " & Me.TextBox1.Text)
    .DataSource = BindingSource1

    ' Automatically resize the visible rows.
    .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders

    ' Set the DataGridView control's border.
    .BorderStyle = BorderStyle.Fixed3D

    ' Put the cells in edit mode when user enters them.
    .EditMode = DataGridViewEditMode.EditOnEnter
    End With
    Catch ex As SqlClient.SqlException
    MessageBox.Show("To run this sample replace " _
    & "connection.ConnectionString with a valid connection string" _
    & " to a TEI database accessible to your system.", _
    "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    System.Threading.Thread.CurrentThread.Abort()
    End Try
    End Sub

    Public Shared Function GetData(ByVal sqlCommand As String) _
    As DataTable

    Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=False;" & "Initial Catalog= TEI;Data Source=localhost"

    Dim TEIConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionString)

    Dim command As New SqlClient.SqlCommand(sqlCommand, TEIConnection)

    Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()
    adapter.SelectCommand = command

    Dim table As New DataTable
    table.Locale = System.Globalization.CultureInfo.InvariantCulture
    adapter.Fill(table)

    Return table

    End Function


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    InitializeDataGridView()
    End Sub

    End Class



    with the sql query ("Select * From student Where studentID = " & Me.TextBox1.Text) the code works. if i change it into this ("Select * From student Where name = " & Me.TextBox1.Text) it doesnt work.

    please if anyone have an idea how to make it work please tell
  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    Hey,

    If I remember SQL right, then you forgot to add single quotation marks around the search text. With my initial example, the value was an Integer, thus no quotation marks were needed.
    Changing the code to something like ...where name='" & TextBox1.Text & "'" should do the trick.
    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • BitByBit_ThorBitByBit_Thor Posts: 2,444Member
    Oops... double post
  • gangrejagangreja Posts: 9Member
    thank you very very very very very much it works!!!!
  • gangrejagangreja Posts: 9Member
    one more question i forgot.
    can you suggest me a book or a site for creating that kind of sql
«1
Sign In or Register to comment.