Member Avatar for ashley24 
Imports System.Data.SqlClient
Imports System
Imports System.Data
Imports System.Collections
Imports System.Windows.Forms
Imports System.Resources
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Net.Mail
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls


Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim counter As Int32 = 0


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        txtDate.Text = DateTime.Now.ToString("MM/dd/yyyy")

        Me.DataGridView1.EnableHeadersVisualStyles = False

        With DataGridView1.ColumnHeadersDefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter
            .BackColor = Color.Maroon
            .ForeColor = Color.WhiteSmoke
            .Font = New Font(.Font.FontFamily, .Font.Size, _
            .Font.Style Or FontStyle.Bold, GraphicsUnit.Point)
        End With

        With Me.DataGridView1
            .RowsDefaultCellStyle.BackColor = Color.Cornsilk
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
        End With

        DataGridView1.AllowUserToAddRows = False

    End Sub


    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click


        Dim labelInfo As String = ""
        Dim connectionString As String = "Provider =SQLOLEDB.1; Data Source =SERVER; Persist Security Info =True; Password=password.; User ID=userid; Initial Catalog=database;"
        Dim connection4 As New OleDbConnection(connectionString)
        connection4.Open()
        labelInfo = "select debtor.[NAMES] AS Customer, estimate.[DETAILS1] AS JobName  from estimate, DEBTOR Where ESTIMATE.[DEBTOR] = DEBTOR.[AC NO] AND estimate.[JOB NUMBER] = '" & txtJobNo.Text & "'"
        Dim sqlLabel As OleDb.OleDbCommand = New OleDb.OleDbCommand
        sqlLabel.Connection = connection4
        sqlLabel.CommandText = labelInfo
        Dim sqlReader1 As OleDb.OleDbDataReader = sqlLabel.ExecuteReader()
        While sqlReader1.Read()
            lblJobInfo.Text = sqlReader1.Item(0)
            lblJobInfo2.Text = sqlReader1.Item(1)
        End While


        Dim dt As New DataTable

        Dim tbl As DataTable = GetDataRecord(txtJobNo.Text)

        If DataGridView1.DataSource Is Nothing Then

            DataGridView1.DataSource = tbl

        Else


            Try

                If tbl.Rows.Count = 1 Then

                    DirectCast(DataGridView1.DataSource, DataTable).LoadDataRow(tbl.Rows(0).ItemArray, True)

                ElseIf tbl.Rows.Count > 1 Then


                    For Each r As DataRow In tbl.Rows

                        DirectCast(DataGridView1.DataSource, DataTable).LoadDataRow(r.ItemArray, True)

                    Next

                Else

                    'Do nothing

                End If

            Catch ex As Exception

            End Try

        End If 

    End Sub

    Private Function GetDataRecord(ByVal JobNo As String) As DataTable



        Dim jobnumber As String = ""
        jobnumber = txtJobNo.Text.Trim

        Dim connectionString As String = "Provider =SQLOLEDB.1; Data Source =SERVER; Persist Security Info =True; Password=password.; User ID=userid; Initial Catalog=database;"
        Dim connection4 As New OleDbConnection(connectionString)
        connection4.Open()
        jobnumber = "select estimate.[JOB NUMBER] AS JobNo, REPLACE(CONVERT(VARCHAR, CONVERT(MONEY,estimate.[Qty1]),1),'.00','') AS JobQty, estimate.[FINAL DELIVERY] AS DropDate from estimate where estimate.[JOB NUMBER] = '" & jobnumber & "'"

        Dim dataadapter As New OleDbDataAdapter(jobnumber, connection4)
        Dim SqlCommand4 As OleDb.OleDbCommand = New OleDb.OleDbCommand
        SqlCommand4.Connection = connection4
        SqlCommand4.CommandText = jobnumber
        Dim sqlReader4 As OleDb.OleDbDataReader = SqlCommand4.ExecuteReader()


        Dim ds As New DataSet()
        ds = New DataSet()
        dataadapter.Fill(ds, "table")
        DataGridView1.DataSource = ds
        'DataGridView1.DataBind()
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        DataGridView1.DataMember = ("table")


        Dim cmb As New DataGridViewCheckBoxColumn()
        cmb.HeaderText = "Data"
        cmb.Name = "cmb"
        DataGridView1.Columns.Add(cmb)

        Dim laser As New DataGridViewCheckBoxColumn()
        laser.HeaderText = "Laser"
        laser.Name = "laser"
        DataGridView1.Columns.Add(laser)

        Dim print As New DataGridViewCheckBoxColumn()
        print.HeaderText = "Print"
        print.Name = "print"
        DataGridView1.Columns.Add(print)

        Dim igen As New DataGridViewCheckBoxColumn()
        igen.HeaderText = "Igen"
        igen.Name = "igen"
        DataGridView1.Columns.Add(igen)

        Dim machine As New DataGridViewCheckBoxColumn()
        machine.HeaderText = "Machine"
        machine.Name = "machine"
        DataGridView1.Columns.Add(machine)

        Dim hand As New DataGridViewCheckBoxColumn()
        hand.HeaderText = "Hand"
        hand.Name = "hand"
        DataGridView1.Columns.Add(hand)

        Dim asap As New DataGridViewCheckBoxColumn()
        asap.HeaderText = "ASAP"
        asap.Name = "asap"
        DataGridView1.Columns.Add(asap)

        Dim notes As New DataGridViewTextBoxColumn()
        notes.HeaderText = "NOTES"
        notes.Name = "notes"
        DataGridView1.Columns.Add(notes)


        'Dim iRow As Integer = DataGridView1.Rows.Add



        Dim JobQty As Integer
        Dim DropDate As Date

        Dim t As New DataTable

        t.Columns.Add("JobNo", GetType(String))

        t.Columns.Add("JobQty", GetType(Integer))

        t.Columns.Add("DropDate", GetType(Date))

        Dim row As DataRow = t.NewRow

        row("JobNo") = JobNo
        counter += 1

        row("JobQty") = JobQty
        row("DropDate") = DropDate

        t.Rows.Add(row)

        Return t

        connection4.Close()


    End Function  


End Class
  • 3 Contributors
  • 4 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by M.Waqas Aslam
Member Avatar for poojavb

Please could you explain in detail....what problem are u actually facing....

Member Avatar for ashley24

user inputs a Job No in a textfield (txtJobNo), under the private function GetDataRecord , the select statement jobnumber gets the values from the database for that particular jobnumber from the textfield, and populates the first row of the datagridview. This step works very good.
Now, when the user inputs a new jobnumber in the textfield to get the results from the database, the second entry replaces the first one in the datagridview.
I want the datagridview to hold all records everytime the user inputs a new job number, not just the most recent one.

Member Avatar for poojavb

ur query tells that the select query shud display the results according to job number...so what ever will be present in ur text box only that part will be shown....

modify ur select query to show all the records...remove the jobnumber criteria if u want all the records....

"select estimate.[JOB NUMBER] AS JobNo, REPLACE(CONVERT(VARCHAR, CONVERT(MONEY,estimate.[Qty1]),1),'.00','') AS JobQty, estimate.[FINAL DELIVERY] AS DropDate from estimate where estimate.[JOB NUMBER] = '" & jobnumber & "'"

Member Avatar for M.Waqas Aslam

i think there is nothing wrong with the query , as there is not that much time to read your full code , but i think you are using data table or data set , , the best way is to add row to your datagrid manualy using sqldatareader , this will not disturb your grid and simply add new rows to your grid.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.