Problem in GridView RepositoryItemLookupEdit EditValueChanged event (DevExpress Version 2010).

See more:

.NET

DevExpress

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