Hi,
I have an editable gridview that is bound to a database table tblDevExpressInvoiceItem. The columns of the gridview are LineID, InvoiceNo,ItemID, Quantity, UnitPrice and LineTotal. The editing control of ItemID is a RepositoryItemLookupEdit. The RepositoryItemLookupEdit is bound to the table tblDevExpressItem having ItemID as valueMember and ItemName as DisplayMember.
The idea is when the user selects an item from the RepositoryItemLookupEdit, the UnitPrice of that item will be obtained from
tblDevExpressItem and set as the Cellvalue of UnitPrice column of the current row. And this should occur as soon as the edit value of the
RepositoryItemLookupEdit is changed. The code for performing this action is at the end of the post.
When I'm performing this action on an existing row everything goes fine. But when this is done on a new row for the first time, nothing is happening. The reason seems to be that the new row is not yet been initialized. Writing the code in gridView_InitNewRow event didn't prove to be fruitful. How can I solve this?
Another question is, in a new row while pressing Enter, unless the focus reaches the last column no NewItemRow is created below in the gridview.
But I want it to appear as soon as data is modified in the current row. Is there any property by which I can change this?
Please help. Regards.
Private strSql As String
Private da As SqlDataAdapter
Private dtbl As DataTable
Private cnn As SqlConnection
Private Sub OpenConnection()
Dim strConn As String = "Persist Security Info=False;User ID=sa;Password=;Initial Catalog=TestDB;Data Source=MyServer"
If cnn Is Nothing Then
cnn = New SqlConnection(strConn)
cnn.Open()
End If
End Sub
Private Sub CloseConnection()
If Not cnn Is Nothing Then
If cnn.State = ConnectionState.Open Then
cnn.Close()
cnn = Nothing
End If
End If
End Sub
Private Function CreateDataAdapter(ByVal strQuery As String) As SqlDataAdapter
Call OpenConnection()
Dim da As New SqlDataAdapter(strQuery, cnn)
Call CloseConnection()
Return da
End Function
Private Function CreateDataSet(ByVal strQuery As String) As DataSet
Dim ds As New DataSet
Dim da As SqlDataAdapter = CreateDataAdapter(strQuery)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Fill(ds, "dtbl")
Return ds
End Function
Private Function CreateCommand(ByVal strQuery As String) As SqlCommand
OpenConnection()
Dim cmd As New SqlCommand(strQuery, cnn)
Return cmd
End Function
Private Function CreateDataReader(ByVal strQuery As String) As SqlDataReader
Dim cmd As SqlCommand = CreateCommand(strQuery)
Dim drd As SqlDataReader = cmd.ExecuteReader()
Return drd
End Function
Private Sub BindRepositoryItemLookupEdit(ByVal objRepositoryItemLookUpEdit As DevExpress.XtraEditors.Repository.RepositoryItemLookUpEdit, ByVal strQuery As String, ByVal strValueMember As String, ByVal strDisplayMember As String)
Dim ds As DataSet = New DataSet
ds = CreateDataSet(strQuery)
Dim dv As DataView = ds.Tables("dtbl").DefaultView
objRepositoryItemLookUpEdit.DataSource = Nothing
objRepositoryItemLookUpEdit.DataSource = dv
If Not IsNothing(strValueMember) Then objRepositoryItemLookUpEdit.ValueMember = strValueMember
objRepositoryItemLookUpEdit.DisplayMember = strDisplayMember
End Sub
Private Sub BindGridControl(ByVal strQuery As String, ByRef da As SqlDataAdapter, ByRef dtbl As DataTable, ByRef bs As BindingSource, ByRef grd As DevExpress.XtraGrid.GridControl)
da = CreateDataAdapter(strQuery)
Dim cb As New SqlCommandBuilder(da)
dtbl.Clear()
dtbl.Locale = System.Globalization.CultureInfo.InvariantCulture
da.Fill(dtbl)
bs.Clear()
bs.DataSource = dtbl
grd.DataSource = Nothing
grd.DataSource = bs
End Sub
Private Sub frmInvoice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
PopulateGridControl()
End Sub
Private Sub PopulateGridControl()
strSql = "Select ItemID,Item From tblDevExpressItem"
BindRepositoryItemLookupEdit(RepositoryItemLookUpEdit1, strSql, "ItemID", "Item")
strSql = Nothing
If dtbl Is Nothing Then dtbl = New DataTable
Dim bs = New BindingSource
strSql = "Select LineID,InvoiceNo,ItemID,Quantity,UnitPrice,LineTotal From tblDevExpressInvoiceItem Where InvoiceNo=" & Val(txtInvoiceNo.Text)
BindGridControl(strSql, da, dtbl, bs, grdItems)
strSql = Nothing
End Sub
Private Sub RepositoryItemLookUpEdit1_EditValueChanging(ByVal sender As Object, ByVal e As DevExpress.XtraEditors.Controls.ChangingEventArgs) Handles RepositoryItemLookUpEdit1.EditValueChanging
GetItemPrice(e.NewValue)
End Sub
Private Sub GetItemPrice(ByVal intItemID As Integer)
Dim drd As SqlDataReader = Nothing
Try
strSql = "Select Price From tblDevExpressItem Where ItemID=" & intItemID
drd = CreateDataReader(strSql)
If drd.Read Then
If IsDBNull(drd(0)) Then
gvwItems.SetFocusedRowCellValue(UnitPrice, 0)
gvwItems.SetFocusedRowCellValue(LineTotal, 0)
Else
gvwItems.SetFocusedRowCellValue(UnitPrice, drd(0))
gvwItems.SetFocusedRowCellValue(LineTotal, (Val(gvwItems.GetFocusedRowCellDisplayText(Quantity)) * drd(0)))
End If
End If
drd.Close()
CloseConnection()
strSql = Nothing
Catch ex As Exception
CloseConnection()
strSql = Nothing
End Try
End Sub
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
da.Update(dtbl)
End Sub