Member Avatar for saravanarajan

Hi Everyone,
I have a datagrid, columns name location code,account code,account name(read only),
if i give the account no to check the acc. no in database and also automatically give the
account name in another cell(i.e. account name cell)

  • 2 Contributors
  • 2 Replies
  • 94 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by saravanarajan

Dani AI

Generated

: goal is to have the account name filled automatically when an account code is entered in the grid. pointed you in the right direction by reacting to cell changes, but rebinding the whole grid on every edit is fragile and slow. A better pattern is: (1) detect when the account-code cell edit finishes, (2) do a single, parameterized lookup for that code, (3) write the returned name into the account-name cell (keep that cell ReadOnly), and (4) cache results to avoid repeated DB hits.

For a Windows Forms DataGridView the flow looks like this (example uses a safe, single-row lookup and an in-memory cache):

' form-level
Private accountCache As New Dictionary(Of String, String)(StringComparer.OrdinalIgnoreCase)
Private connectionString As String = "your connection string here"

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Const accountCodeCol As Integer = 1
    Const accountNameCol As Integer = 2

    If e.ColumnIndex <> accountCodeCol Then Return

    Dim raw = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value
    Dim code = If(raw IsNot Nothing, raw.ToString().Trim(), String.Empty)
    If code = String.Empty Then
        DataGridView1.Rows(e.RowIndex).Cells(accountNameCol).Value = String.Empty
        Return
    End If

    Dim name As String = Nothing
    If Not accountCache.TryGetValue(code, name) Then
        Using cn As New System.Data.SqlClient.SqlConnection(connectionString)
            Using cmd As New System.Data.SqlClient.SqlCommand("SELECT AccountName FROM Accounts WHERE AccountCode = @code", cn)
                cmd.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 50).Value = code
                cn.Open()
                Dim obj = cmd.ExecuteScalar()
                If obj IsNot Nothing Then name = obj.ToString()
            End Using
        End Using
        If Not String.IsNullOrEmpty(name) Then accountCache(code) = name
    End If

    DataGridView1.Rows(e.RowIndex).Cells(accountNameCol).Value = If(String.IsNullOrEmpty(name), "Not found", name)
End Sub

For ASP.NET GridView (WebForms) use a TemplateField with a textbox for the code and a label for the name, call a server WebMethod or small API from JavaScript on blur/change, and update the label client-side to avoid full postbacks.

Troubleshooting/cautions: verify column indexes, trim inputs, handle null results, log DB exceptions, mark the name column ReadOnly so users cannot overwrite it, and always use parameterized queries to avoid injection. If you have many lookups, preload a small lookup table or keep a short-lived cache to reduce DB load.

Recommended Answers

All 2 Replies

Member Avatar for manoshailu

' while form load bind the data in datagird control
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection = New SqlConnection("server=shailu;uid=sa;pwd=;database=pubs")
Dim sadapt As SqlDataAdapter = New SqlDataAdapter("select * from account", con)
Dim ds As DataSet = New DataSet
sadapt.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
End Sub

'Bind the data in datagrid control by accountcode from the user through textbox

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As SqlConnection = New SqlConnection("server=shailu;uid=sa;pwd=;database=pubs")
Dim sadapt As SqlDataAdapter = New SqlDataAdapter("select * from account where accountcode='" & TextBox1.Text & "'", con)
Dim ds As DataSet = New DataSet
sadapt.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
End Sub

'While change values in a cell(account code) required data will bind
Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged
Dim con As SqlConnection = New SqlConnection("server=shailu;uid=sa;pwd=;database=pubs")
Dim sadapt As SqlDataAdapter = New SqlDataAdapter("select * from account where accountcode like '%" & DataGrid1.Item(DataGrid1.CurrentRowIndex, 0) & "%'", con)
Dim ds As DataSet = New DataSet
sadapt.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
End Sub

Best Regards,
shailu:)

Member Avatar for saravanarajan

thank you very much for your help

regds,
saravnarajan

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.