Htykuut

I am trying to search itemcode by typing in textbox and i want to get specific columns in same row from SQL database. I have made columns with headers in datagridview and i want that searched data in that specific datagridview columns. I have written code but it's not working. I do not know how to do this. I am new and trying to learn vb. Please give some suggestion.

This is my code:

Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click



    Using cn As New SqlConnection("server= PANKAJSQLEXPRESS; database = pankaj billing software; integrated security=true")

        Using cmd2 As New SqlCommand("select itemcode As 'Item Code', item,qty As Quantity, weight as Weight from stockdata Where itemcode = @itemcode;", cn)

            cmd2.Parameters.AddWithValue("@itemcode", TextBox1.Text)

            cn.Open()

            Dim dr As SqlDataReader = cmd2.ExecuteReader()

            dt.Load(dr)

            DataGridView1.DataSource = dt







            For Each row As DataGridViewRow In DataGridView1.Rows





                cmd2.Parameters.Add("@item", SqlDbType.VarChar)

                cmd2.Parameters.Add("@qty", SqlDbType.VarChar)

                cmd2.Parameters.Add("@weight", SqlDbType.VarChar)





                With cmd2



                    row.Cells(1).Value = .Parameters("@item").Value

                    row.Cells(2).Value = .Parameters("@qty").Value

                    row.Cells(2).Value = .Parameters("@weight").Value

                End With

                cmd2.ExecuteNonQuery()

            Next

        End Using

    End Using

End Sub

I used a TextBox located outside the DataGridView to enter the item code to search for. I added a Button to do the search and retrieve the data to a DataReader.

The DataReader then loads the DataTable which is declared as a form level (class level variable). We want to use the same DataTable every time we search so the items will be added to the grid. With the Load method if the DataTable already contains rows, the incoming data from the data source is merged with the existing rows. The DataTable is then bound to the DataGridView. Each time the user enters an item code in the TextBox and clicks the Search Button a new row will be added to the grid.

To make nicer looking Column headers use as alias in your Select statement. The As clause following the databse column name is the alias and will show up in the DataGridView as a header. If you have a space in your alias it must be enclosed in single quotes.

Private dt As DataTable



Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    dt = New DataTable()

End Sub



Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

    Using cn As New SqlConnection("server= PANKAJSQLEXPRESS; database = pankaj billing software; integrated security=true")

        Using cmd2 As New SqlCommand("select itemcode As 'Item Code', item,qty As Quantity, weight as Weight from stockdata Where itemcode = @itemcode;", cn)

            cmd2.Parameters.AddWithValue("@itemcode", txtItemCode.Text)

            cn.Open()

            Dim dr As SqlDataReader = cmd2.ExecuteReader()

            dt.Load(dr)

            DataGridView1.DataSource = dt

        End Using

    End Using

End Sub

P.S. Very glad to see the use of parameters and Using blocks! :-)

How about this?

'"C:your_pathNorthwind.mdb"

Imports System

Imports System.Data

Imports System.Data.OleDb

Imports System.Windows.Forms



Public Class Form1

    Inherits System.Windows.Forms.Form



    Private bindingSource1 As New BindingSource()

    Private dataAdapter As New OleDbDataAdapter()



    <STAThreadAttribute()> _

    Public Shared Sub Main()

        Application.Run(New Form1())

    End Sub



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

        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathNorthwind.mdb"

        Dim selectCommand As String

        Dim connection As New OleDbConnection(connectionString)



        selectCommand = "Select * From MyExcelTable ORDER BY ID"

        Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)



        With DataGridView1

            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

        End With



        Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

        Dim table As New DataTable()



        table.Locale = System.Globalization.CultureInfo.InvariantCulture



        Me.dataAdapter.Fill(table)

        Me.bindingSource1.DataSource = table



        Dim data As New DataSet()



        data.Locale = System.Globalization.CultureInfo.InvariantCulture



        DataGridView1.DataSource = Me.bindingSource1

        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua

        Me.DataGridView1.AutoResizeColumns( _

            DataGridViewAutoSizeColumnsMode.AllCells)



    End Sub



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

        Dim table As New DataTable()



        Me.bindingSource1 = Me.DataGridView1.DataSource

        table = Me.bindingSource1.DataSource



        Me.dataAdapter.Update(table)

    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click, btnClose.Click

        Me.Close()

    End Sub



    Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged, TextBox1.Click



        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:your_pathNorthwind.mdb"

        Dim selectCommand As String

        Dim connection As New OleDbConnection(connectionString)



        'selectCommand = "Select * From MyExcelTable where Fname = '" & TextBox1.Text & "'"

        '"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearch & "%'"

        'or ending with:

        '"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearch & "'"



        selectCommand = "Select * From MyExcelTable where Fname Like '%" & TextBox1.Text & "%'"

        Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)



        With DataGridView1

            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

        End With



        Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

        Dim table As New DataTable()



        table.Locale = System.Globalization.CultureInfo.InvariantCulture



        Me.dataAdapter.Fill(table)

        Me.bindingSource1.DataSource = table



        Dim data As New DataSet()



        data.Locale = System.Globalization.CultureInfo.InvariantCulture



        DataGridView1.DataSource = Me.bindingSource1

        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua

        Me.DataGridView1.AutoResizeColumns( _

            DataGridViewAutoSizeColumnsMode.AllCells)



    End Sub

End Class