Member Avatar for like_bilal02

Dear freinds i want to get specific data in gridview specifc cell with the help of sql query from sql table

i put the following query but it doesn't work

Sql = "select e.item_no from nitmtbl e,purchaseitem g where e.item_name=g.item_name and g.item_name='grovita sp'"
        Da = New SqlDataAdapter(Sql, Class1.Connection)
        Da.SelectCommand.ExecuteNonQuery()
        Dim ds As New DataSet
        Da.Fill(ds, "nitmtbl")
        DataGridView1.Rows(0).Cells(0).Value = ds.Tables("nitmtbl")

kindly help me

Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 2 Contributors
  • 2 Replies
  • 115 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by like_bilal02

Dani AI

Generated

: the goal is to auto-fill the item_no cell when you type an item_name into a DataGridView row. 's advice (fill a DataTable and set DataGridView.DataSource) is fine for showing query results, but it does not handle the interactive per-row lookup you want while editing.

Two immediate problems in the code you posted: calling ExecuteNonQuery for a SELECT is incorrect (DataAdapter.Fill runs the SELECT for you), and assigning a whole DataTable to a single cell will not place the item value into that cell. The runtime/compile error about dt means the DataTable variable was never instantiated before use — be sure you do Dim dt As New DataTable() if you use that route.

A cleaner approach for per-row auto-fill is to handle the grid's edit event and do a parameterized lookup (ExecuteScalar) for the single value. This avoids filling a full DataSet just to get one column:

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Const ITEM_NAME_COL As Integer = 1
    Const ITEM_NO_COL As Integer = 0

    If e.RowIndex < 0 OrElse e.ColumnIndex <> ITEM_NAME_COL Then Return

    Dim nameObj = DataGridView1.Rows(e.RowIndex).Cells(ITEM_NAME_COL).Value
    If nameObj Is Nothing Then Return
    Dim itemName = nameObj.ToString().Trim()
    If itemName = String.Empty Then Return

    Dim result = Nothing
    Using cn As New SqlConnection(Class1.Connection.ConnectionString)
        Using cmd As New SqlCommand("SELECT item_no FROM nitmtbl WHERE item_name = @name", cn)
            cmd.Parameters.AddWithValue("@name", itemName)
            cn.Open()
            result = cmd.ExecuteScalar()
        End Using
    End Using

    If result IsNot Nothing AndAlso result IsNot DBNull.Value Then
        DataGridView1.Rows(e.RowIndex).Cells(ITEM_NO_COL).Value = result.ToString()
    Else
        DataGridView1.Rows(e.RowIndex).Cells(ITEM_NO_COL).Value = DBNull.Value
    End If
End Sub

If the table is small or lookups are frequent, preload a dictionary at form load and use it during edits for much faster responses and to avoid round-trips. Always use parameterized queries (avoid string concatenation) and handle DBNull, trimming, and duplicate item_name cases.

Recommended Answers

All 2 Replies

Member Avatar for kvprajapati 
Sql = "select e.item_no from nitmtbl e,purchaseitem g where e.item_name=g.item_name and g.item_name='grovita sp'"

Da = New SqlDataAdapter(Sql, Class1.Connection)

Dim dt As New DataTable
Da.Fill(dt)
DataGridView1.DataSource=dt
Member Avatar for like_bilal02

yr this query give error in DT

error is: Variable "dt"is used before it has assigned a null value

and 1 more thing i want to explain i want that vakue come from table in a way that when i make any entry in datagrid view and when i put a item name in datagrid 2 cell the 1st cell value come automatically with the help of above query query

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.