none
Update command updating multiple rows in dataset RRS feed

  • Question

  • Hi,

    I am using following command to update my dataset:

    UpdateCmd.CommandText = "update sl_employee set EMP_NAME=@name, EMP_TYPE=@type, GENDER=@gender, DOB=@dob, F_NAME=@fname, M_NAME=@mname, MOB_NO=@mobno, EC_NO=@ecno, P_ADDR=@paddr, P_CITY=" & cmbPCity.SelectedValue & ", P_STATE=" & cmbPState.SelectedValue & ", P_PIN=@ppin, C_ADDR=@caddr, C_CITY=" & cmbCCity.SelectedValue & ", C_STATE=" & cmbCState.SelectedValue & ", C_PIN=@cpin, [MODBY]=" & frmMain.UserID & ", [MODON]=getdate() where emp_id=@original_emp_id"

    UpdateCmd.Connection = conSMS.sqlConn

    UpdateCmd.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {

    New System.Data.SqlClient.SqlParameter("@original_emp_id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "EMP_ID", System.Data.DataRowVersion.Original, Nothing),

    New System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 0, "EMP_NAME"), 

    New System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.VarChar, 0, "EMP_TYPE"),

    New System.Data.SqlClient.SqlParameter("@gender", System.Data.SqlDbType.VarChar, 0, "GENDER"), 

    New System.Data.SqlClient.SqlParameter("@dob", System.Data.SqlDbType.Date, 0, "DOB"),

    New System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar, 0, "F_NAME"),

    New System.Data.SqlClient.SqlParameter("@mname", System.Data.SqlDbType.VarChar, 0, "M_NAME"),

    New System.Data.SqlClient.SqlParameter("@mobno", System.Data.SqlDbType.VarChar, 0, "MOB_NO"),

    New System.Data.SqlClient.SqlParameter("@ecno", System.Data.SqlDbType.VarChar, 0, "EC_NO"),

    New System.Data.SqlClient.SqlParameter("@paddr", System.Data.SqlDbType.VarChar, 0, "P_ADDR"),

    New System.Data.SqlClient.SqlParameter("@ppin", System.Data.SqlDbType.VarChar, 0, "P_PIN"),

    New System.Data.SqlClient.SqlParameter("@caddr", System.Data.SqlDbType.VarChar, 0, "C_ADDR"),

    New System.Data.SqlClient.SqlParameter("@cpin", System.Data.SqlDbType.VarChar, 0, "C_PIN")

    })

    adpEmployee.UpdateCommand = UpdateCmd

    Private Function SaveRecord() As Boolean
                    If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                        Try
                            BMB.EndCurrentEdit()
                            Create_Commands()
                            adpEmployee.Update(dsEmployee.Tables(0))
                            dsEmployee.Tables(0).AcceptChanges()

                            MsgBox("Record Saved Successfully!", MsgBoxStyle.Information, "Edit Record")
                            Return True
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
                            Return False
                        End Try
                    Else
                        Return False
                    End If
    End Function

    But, somehow the command is updating more than the intended row, sometimes it updates the current row+first row(all the columns of the row are updated with the values of the current row) and sometimes all the records of the dataset are updated.

    Please help! and revert if anything else is required from my side to analyze the problem.


    Shishir

    Saturday, August 4, 2018 6:35 AM

All replies

  • Examining the code there is no reason why it would update more than one row given emp_id is unique. With that said, how the code is used would be where to look at why this happens. 

    To determine exactly what is transpiring, set up a breakpoint (this should always be a first step if code appears correct but the result is unexpected behavior as in this case) to the first line of code which leads to calling the code above, step through the code until the process is completed. During the debug session once 

    adpEmployee.Update(dsEmployee.Tables(0))

    has executed, use whatever tool you have to view the table to see the change(s), best tool is SSMS (SQL-Server Management Studio), how many rows were affected? I would assume one.

    Important: Also, you should not be calling 

    dsEmployee.Tables(0).AcceptChanges()

    Take it out, I've never since version 1 of Visual Studio needed to use AcceptChanges when making changes to a backend database.

    Lastly, I have say, make sure you are targeting the right database/table.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, August 4, 2018 10:00 AM
    Moderator
  • Thanks for the reply. As suggested by you, I removed the statement 'dsEmployee.Tables(0).AcceptChanges()' from my code. Also, I used SSMS to determine how many rows were affected after the statement 'adpEmployee.Update(dsEmployee.Tables(0))' was executed (I have a MODON column in my table which is updated by current date time whenever a row is updated) and I noticed that all the rows of the table were updated as the MODON column have the same date time value for every row of the table.

    Please suggest if I should send you the complete code to identify the problem.


    Shishir

    Sunday, August 5, 2018 4:47 AM
  • Whenever a developer has issues with a command be it SELECT, UPDATE or DELETE I recommend using the language extension below.

    In your case call it before after Create_Commands() as shown in the second code block. The entire statement with values will be echo'd to the IDE output window, examine it, do you see an issue, if so fix it, if not run it in SSMS and if it works as expected then no need to go any farther as there is an issue either with code not shown that causes the current issue or it's a bug in .NET code which means you need to report it as a bug. Personally I don't write code in the fashion you have, instead I recommend code similar to the third code block which updates one row at a time.

    Imports System.Data.SqlClient
    Namespace Classes
        Public Class DataOperations
            Inherits BaseSqlServerConnections
            Public Sub New()
                DefaultCatalog = "NorthWind"
            End Sub
    
            ''' <summary>
            ''' Update Customer by primary key, in this example we use
            ''' a class but you could also use a DataRow instead
            ''' </summary>
            ''' <param name="pCustomer"></param>
            ''' <returns></returns>
            ''' <remarks>
            ''' No preparing of parameters done, it this method was to
            ''' be called in say a for-next we would have the parameters
            ''' done in a separate method, return here and used, might
            ''' gain a few milliseconds so no big deal.
            ''' </remarks>
            Public Function UpdateCustomer(pCustomer As Customers, pShowCommand As Boolean = False) As Boolean
                Dim rowsAffected As Integer = 0
    
                Dim statement As String =
                        <SQL>
                            UPDATE dbo.Customers
                               SET CompanyName = @CompanyName
                                  ,ContactName = @ContactName
                                  ,ContactTitle = @ContactTitle
                             WHERE CustomerIdentifier = @CustomerId
                        </SQL>.Value
    
                Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New SqlCommand With {.Connection = cn, .CommandText = statement}
                        cmd.Parameters.AddWithValue("@CompanyName", pCustomer.CompanyName)
                        cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName)
                        cmd.Parameters.AddWithValue("@ContactTitle", pCustomer.ContactTitle)
    
                        If pShowCommand Then
                            '
                            ' ActualCommandTextByNames will write the UPDATE 
                            ' statement including values for each parameter to
                            ' the IDE output window, copy/paste, run in SSMS.
                            '
                            Console.WriteLine(cmd.ActualCommandTextByNames())
                            Console.WriteLine("Put break point here")
                        End If
    
                        Try
                            cn.Open()
                            rowsAffected = cmd.ExecuteNonQuery()
                        Catch ex As Exception
                            mHasException = True
                            mLastException = ex
                        End Try
    
                    End Using
                End Using
    
                Return rowsAffected = 1
    
            End Function
        End Class
    End Namespace

    Example

    Console.WriteLine(UpdateCmd.ActualCommandTextByNames())

    Example of updating one row at a time (read comments)

    Imports System.Data.SqlClient
    Namespace Classes
        Public Class DataOperations
            Inherits BaseSqlServerConnections
            Public Sub New()
                DefaultCatalog = "NorthWind"
            End Sub
    
            ''' <summary>
            ''' Update Customer by primary key, in this example we use
            ''' a class but you could also use a DataRow instead
            ''' </summary>
            ''' <param name="pCustomer"></param>
            ''' <returns></returns>
            ''' <remarks>
            ''' No preparing of parameters done, it this method was to
            ''' be called in say a for-next we would have the parameters
            ''' done in a separate method, return here and used, might
            ''' gain a few milliseconds so no big deal.
            ''' </remarks>
            Public Function UpdateCustomer(pCustomer As Customers, Optional pShowCommand As Boolean = False) As Boolean
                Dim rowsAffected As Integer = 0
    
                Dim statement As String =
                        <SQL>
                            UPDATE dbo.Customers
                               SET CompanyName = @CompanyName
                                  ,ContactName = @ContactName
                                  ,ContactTitle = @ContactTitle
                             WHERE CustomerIdentifier = @CustomerId
                        </SQL>.Value
    
                Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New SqlCommand With {.Connection = cn, .CommandText = statement}
                        cmd.Parameters.AddWithValue("@CompanyName", pCustomer.CompanyName)
                        cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName)
                        cmd.Parameters.AddWithValue("@ContactTitle", pCustomer.ContactTitle)
    
                        If pShowCommand Then
                            '
                            ' ActualCommandTextByNames will write the UPDATE 
                            ' statement including values for each parameter to
                            ' the IDE output window, copy/paste, run in SSMS.
                            '
                            Console.WriteLine(cmd.ActualCommandTextByNames())
                            Console.WriteLine("Put break point here")
                        End If
    
                        Try
                            cn.Open()
                            rowsAffected = cmd.ExecuteNonQuery()
                        Catch ex As Exception
                            mHasException = True
                            mLastException = ex
                        End Try
    
                    End Using
                End Using
    
                Return rowsAffected = 1
    
            End Function
        End Class
    End Namespace

    .

    Namespace Classes
        Public Class BaseExceptionProperties
    
            Protected mHasException As Boolean
            ''' <summary>
            ''' Indicate the last operation thrown an exception or not
            ''' </summary>
            ''' <returns></returns>
            Public ReadOnly Property HasException() As Boolean
                Get
                    Return mHasException
                End Get
            End Property
            Protected mLastException As Exception
            ''' <summary>
            ''' Provides access to the last exception thrown
            ''' </summary>
            ''' <returns></returns>
            Public ReadOnly Property LastException() As Exception
                Get
                    Return mLastException
                End Get
            End Property
            ''' <summary>
            ''' If you don't need the entire exception as in LastException this 
            ''' provides just the text of the exception
            ''' </summary>
            ''' <returns></returns>
            Public ReadOnly Property LastExceptionMessage As String
                Get
                    Return mLastException.Message
                End Get
            End Property
            ''' <summary>
            ''' Indicate for return of a function if there was an exception thrown or not.
            ''' </summary>
            ''' <returns></returns>
            Public ReadOnly Property IsSuccessFul As Boolean
                Get
                    Return Not mHasException
                End Get
            End Property
        End Class
    End Namespace

    .

    Namespace Classes
        Public Class BaseSqlServerConnections
            Inherits BaseExceptionProperties
    
            ''' <summary>
            ''' This points to your database server
            ''' </summary>
            Protected DatabaseServer As String = "KARENS-PC"
            ''' <summary>
            ''' Name of database containing required tables
            ''' </summary>
            Protected DefaultCatalog As String = ""
            Public ReadOnly Property ConnectionString As String
                Get
                    Return $"Data Source={DatabaseServer};" &
                           $"Initial Catalog={DefaultCatalog};Integrated Security=True"
                End Get
            End Property
    
        End Class
    End Namespace


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, August 5, 2018 10:48 AM
    Moderator
  • Hi,

    I am getting following error while calling the function ActualCommandTextByNames():

    "object reference not set to an instance of an object"

    The error is thrown while executing following line in CommandPeeker.vb:

    Dim EmptyParameterNames = (From T In sender.Parameters.Cast(Of IDataParameter)() Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault


    Shishir

    Sunday, August 5, 2018 5:09 PM
  • That is because ActualCommandTextByNames only works when each field in the UPDATE command has a parameter 

    So the following causes the issue

    P_CITY=" & cmbPCity.SelectedValue & ", 
    P_STATE=" & cmbPState.SelectedValue & ", 
    P_PIN=@ppin, 
    C_ADDR=@caddr, 
    C_CITY=" & cmbCCity.SelectedValue & ", 
    C_STATE=" & cmbCState.SelectedValue & ", 
    C_PIN=@cpin, 
    [MODBY]=" & frmMain.UserID & ", 
    [MODON]=getdate() where emp_id=@original_emp_id"

    While these don't

    EMP_NAME=@name, 
    EMP_TYPE=@type, 
    GENDER=@gender, 
    DOB=@dob, 
    F_NAME=@fname, 
    M_NAME=@mname, 
    MOB_NO=@mobno, 
    EC_NO=@ecno, 
    P_ADDR=@paddr

    The rule of thumb is to always uses parameters for each replacement in a query for both values and for the WHERE condition.

    So for you as coded ActualCommandTextByNames will not work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, August 5, 2018 5:35 PM
    Moderator
  • I changed my update command to have parameters for each field. The statement now reads:

    update sl_employee set EMP_NAME=@name, EMP_TYPE=@type, GENDER=@gender, DOB=@dob, F_NAME=@fname, M_NAME=@mname, MOB_NO=@mobno, EC_NO=@ecno, P_ADDR=@paddr, P_CITY=@pcity, P_STATE=@pstate, P_PIN=@ppin, C_ADDR=@caddr, C_CITY=@ccity, C_STATE=@cstate, C_PIN=@cpin, MODBY=@modby, MODON=@modon where emp_id=@original_emp_id

    and added 2 extra parameters as:

    UpdateCmd.Parameters.AddWithValue("@modby", frmMain.UserID)
    UpdateCmd.Parameters.AddWithValue("@modon", Now)

    But still I am getting the same error :(

    Please help.

    Also, I have noticed that when I jump directly from first record to the last one and edit the last record then the first and last records are updated and if I traverse through each record and then edit my last record then all those records are updated.


    Shishir


    Monday, August 6, 2018 5:54 AM
  • Hi,

    Do you solve the problem?

    I checked your code and didn't feel any problems, would it be a problem with Create_Commands()?

    Please provide more code.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 13, 2018 8:07 AM
  • Hi,

    Thanks a lot for attending to my issue, the problem isn't solved yet.

    I am attaching the complete code for my form here. Please check and help me to identify the problem.

    Imports System.Data.SqlClient
    Imports Shishir.Utilities
    
    Public Class frmEmployee_Bak
        Public EditMode As Boolean = False
        Friend WithEvents cmsImage As New ContextMenuStrip
        Friend WithEvents tsmBrowse As New ToolStripMenuItem
        Friend WithEvents tsmDelete As New ToolStripMenuItem
        Dim recPosition As Integer
        Dim ImageFile As String = ""
        Dim State, City As String
        Dim WithEvents BMB As BindingManagerBase
        Dim conSMS As New DBConnection
        Dim Schema As String = frmMain.DB
        Dim adpEmployee As New SqlDataAdapter("select * from employee_vw", conSMS.sqlConn)
        Dim dsEmployee As New DataSet
        Dim ttCommon As New ToolTip
    
        Private Sub frmEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            SetToolTips()
            Create_Context_Menu()
            Get_State_City()
            Fill_Combo()
            BindFields()
            Create_Commands()
    
            If EditMode = False Then
                BMB.Position = 0
                Refresh_Navigation()
                Panel1.Select()
            End If
        End Sub
    
        Private Sub SetToolTips()
            ttCommon.SetToolTip(btnFirst, "First Record")
            ttCommon.SetToolTip(btnPrev, "Previous Record")
            ttCommon.SetToolTip(btnNext, "Next Record")
            ttCommon.SetToolTip(btnLast, "Last Record")
            ttCommon.SetToolTip(btnFind, "Find Record")
            ttCommon.SetToolTip(btnNew, "Add New Record")
            ttCommon.SetToolTip(btnEdit, "Edit Record")
            ttCommon.SetToolTip(btnDelete, "Delete Record")
        End Sub
    
        Private Sub Create_Context_Menu()
            tsmBrowse.Text = "Browse Image..."
            tsmDelete.Text = "Delete Image"
            cmsImage.Items.Add(tsmBrowse)
            cmsImage.Items.Add(tsmDelete)
        End Sub
    
        Private Sub Get_State_City()
            Dim cmdState As New SqlCommand("select state from sl_branch", conSMS.sqlConn)
            Dim cmdCity As New SqlCommand("select city from sl_branch", conSMS.sqlConn)
    
            If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                Try
                    State = cmdState.ExecuteScalar
                    City = cmdCity.ExecuteScalar
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical, "Get City and State")
                End Try
            End If
        End Sub
    
        Private Sub BindFields()
            If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                Try
                    dsEmployee.Clear()
                    adpEmployee.Fill(dsEmployee, "EMP")
    
                    dsEmployee.Tables(0).PrimaryKey = New DataColumn() {dsEmployee.Tables(0).Columns("emp_id")}
                    dsEmployee.Tables(0).Columns("emp_id").AutoIncrement = True
    
                    txtID.DataBindings.Clear()
                    txtID.DataBindings.Add("Text", dsEmployee, "EMP.emp_id", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtName.DataBindings.Clear()
                    txtName.DataBindings.Add("Text", dsEmployee, "EMP.emp_name", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtGender.DataBindings.Clear()
                    txtGender.DataBindings.Add("Text", dsEmployee, "EMP.gender", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtDOB.DataBindings.Clear()
                    txtDOB.DataBindings.Add("Text", dsEmployee, "EMP.dob", True, DataSourceUpdateMode.OnPropertyChanged, "", "dd-MMM-yyyy")
    
                    txtMStatus.DataBindings.Clear()
                    txtMStatus.DataBindings.Add("Text", dsEmployee, "EMP.m_status", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtFName.DataBindings.Clear()
                    txtFName.DataBindings.Add("Text", dsEmployee, "EMP.f_name", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtMName.DataBindings.Clear()
                    txtMName.DataBindings.Add("Text", dsEmployee, "EMP.m_name", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtMobile.DataBindings.Clear()
                    txtMobile.DataBindings.Add("Text", dsEmployee, "EMP.mob_no", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtECNO.DataBindings.Clear()
                    txtECNO.DataBindings.Add("Text", dsEmployee, "EMP.ec_no", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtNationality.DataBindings.Clear()
                    txtNationality.DataBindings.Add("Text", dsEmployee, "EMP.nationality", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPAddr.DataBindings.Clear()
                    txtPAddr.DataBindings.Add("Text", dsEmployee, "EMP.p_addr", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPCity.DataBindings.Clear()
                    txtPCity.DataBindings.Add("Text", dsEmployee, "EMP.p_city", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPState.DataBindings.Clear()
                    txtPState.DataBindings.Add("Text", dsEmployee, "EMP.p_state", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPPin.DataBindings.Clear()
                    txtPPin.DataBindings.Add("Text", dsEmployee, "EMP.p_pin", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtCAddr.DataBindings.Clear()
                    txtCAddr.DataBindings.Add("Text", dsEmployee, "EMP.c_addr", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtCCity.DataBindings.Clear()
                    txtCCity.DataBindings.Add("Text", dsEmployee, "EMP.c_city", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtCState.DataBindings.Clear()
                    txtCState.DataBindings.Add("Text", dsEmployee, "EMP.c_state", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtCPin.DataBindings.Clear()
                    txtCPin.DataBindings.Add("Text", dsEmployee, "EMP.c_pin", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtEQual.DataBindings.Clear()
                    txtEQual.DataBindings.Add("Text", dsEmployee, "EMP.edu_qual", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPEmp.DataBindings.Clear()
                    txtPEmp.DataBindings.Add("Text", dsEmployee, "EMP.prev_empl", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtPDesig.DataBindings.Clear()
                    txtPDesig.DataBindings.Add("Text", dsEmployee, "EMP.prev_desig", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtWFrom.DataBindings.Clear()
                    txtWFrom.DataBindings.Add("Text", dsEmployee, "EMP.w_from", True, DataSourceUpdateMode.OnPropertyChanged, "", "dd-MMM-yyyy")
    
                    txtWTo.DataBindings.Clear()
                    txtWTo.DataBindings.Add("Text", dsEmployee, "EMP.w_to", True, DataSourceUpdateMode.OnPropertyChanged, "", "dd-MMM-yyyy")
    
                    txtECat.DataBindings.Clear()
                    txtECat.DataBindings.Add("Text", dsEmployee, "EMP.dept", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtDesig.DataBindings.Clear()
                    txtDesig.DataBindings.Add("Text", dsEmployee, "EMP.desig", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtDOJ.DataBindings.Clear()
                    txtDOJ.DataBindings.Add("Text", dsEmployee, "EMP.doj", True, DataSourceUpdateMode.OnPropertyChanged, "", "dd-MMM-yyyy")
    
                    txtEType.DataBindings.Clear()
                    txtEType.DataBindings.Add("Text", dsEmployee, "EMP.emp_type", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    cbClass4.DataBindings.Clear()
                    cbClass4.DataBindings.Add("Checked", dsEmployee, "EMP.class_iv", True, DataSourceUpdateMode.OnPropertyChanged, 0)
    
                    txtUAN.DataBindings.Clear()
                    txtUAN.DataBindings.Add("Text", dsEmployee, "EMP.uan_no", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtDOR.DataBindings.Clear()
                    txtDOR.DataBindings.Add("Text", dsEmployee, "EMP.dor", True, DataSourceUpdateMode.OnPropertyChanged, "", "dd-MMM-yyyy")
    
                    txtRoR.DataBindings.Clear()
                    txtRoR.DataBindings.Add("Text", dsEmployee, "EMP.ror", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtSpouse.DataBindings.Clear()
                    txtSpouse.DataBindings.Add("Text", dsEmployee, "EMP.spouse_name", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtSAge.DataBindings.Clear()
                    txtSAge.DataBindings.Add("Text", dsEmployee, "EMP.spouse_age", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtNominee.DataBindings.Clear()
                    txtNominee.DataBindings.Add("Text", dsEmployee, "EMP.nominee", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtNRel.DataBindings.Clear()
                    txtNRel.DataBindings.Add("Text", dsEmployee, "EMP.nom_rel", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtNAge.DataBindings.Clear()
                    txtNAge.DataBindings.Add("Text", dsEmployee, "EMP.nom_age", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    txtRemarks.DataBindings.Clear()
                    txtRemarks.DataBindings.Add("Text", dsEmployee, "EMP.remarks", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    pbPhoto.DataBindings.Clear()
                    pbPhoto.DataBindings.Add("Image", dsEmployee, "EMP.photo", True, DataSourceUpdateMode.OnPropertyChanged)
    
                    BMB = BindingContext(dsEmployee, "EMP")
                    If BMB.Count = 0 Then AddRecord()
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
                    Me.Close()
                End Try
            End If
        End Sub
    
        Private Sub Fill_Combo()
            Dim adpPCity As New SqlDataAdapter("select city_id, city_name from sa_city order by 2", conSMS.sqlConn)
            Dim dsPCity As New DataSet
            Dim adpCCity As New SqlDataAdapter("select city_id, city_name from sa_city order by 2", conSMS.sqlConn)
            Dim dsCCity As New DataSet
            Dim adpPState As New SqlDataAdapter("select state_id, state_name from sa_state order by 2", conSMS.sqlConn)
            Dim dsPState As New DataSet
            Dim adpCState As New SqlDataAdapter("select state_id, state_name from sa_state order by 2", conSMS.sqlConn)
            Dim dsCState As New DataSet
    
            If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                dsPCity.Clear()
                adpPCity.Fill(dsPCity, "pcity")
                cmbPCity.DataSource = dsPCity
                cmbPCity.DisplayMember = "pcity.city_name"
                cmbPCity.ValueMember = "pcity.city_id"
    
                dsCCity.Clear()
                adpCCity.Fill(dsCCity, "ccity")
                cmbCCity.DataSource = dsCCity
                cmbCCity.DisplayMember = "ccity.city_name"
                cmbCCity.ValueMember = "ccity.city_id"
    
                dsPState.Clear()
                adpPState.Fill(dsPState, "pstate")
                cmbPState.DataSource = dsPState
                cmbPState.DisplayMember = "pstate.state_name"
                cmbPState.ValueMember = "pstate.state_id"
    
                dsCState.Clear()
                adpCState.Fill(dsCState, "cstate")
                cmbCState.DataSource = dsCState
                cmbCState.DisplayMember = "cstate.state_name"
                cmbCState.ValueMember = "cstate.state_id"
            End If
    
            cmbGender.SelectedIndex = 0
            cmbMStatus.SelectedIndex = 0
            cmbNationality.SelectedIndex = 0
            cmbEType.SelectedIndex = 0
            cmbECat.SelectedIndex = 0
            cmbPCity.Text = City
            cmbPState.Text = State
            cmbCCity.Text = City
            cmbCState.Text = State
        End Sub
    
        Private Sub Create_Commands()
            Dim InsertCmd As New SqlCommand
            Dim UpdateCmd As New SqlCommand
            Dim DeleteCmd As New SqlCommand
    
            InsertCmd.CommandText = "insert into sl_employee(EMP_NAME,EMP_TYPE,GENDER,DOB,F_NAME,M_NAME,MOB_NO,EC_NO,P_ADDR,P_CITY,P_STATE,P_PIN,C_ADDR,C_CITY,C_STATE,C_PIN,M_STATUS,NATIONALITY,UAN_NO,EDU_QUAL,PREV_EMPL,PREV_DESIG,W_FROM,W_TO,DEPT,DESIG,CLASS_IV,DOJ,DOR,ROR,NOMINEE,NOM_AGE,NOM_REL,SPOUSE_NAME,SPOUSE_AGE,REMARKS,MODBY,MODON) " & _
                                    "values(@name,@type,@gender,@dob,@fname,@mname,@mobno,@ecno,@paddr," & cmbPCity.SelectedValue & "," & cmbPState.SelectedValue & ",@ppin,@caddr," & cmbCCity.SelectedValue & "," & cmbCState.SelectedValue & ",@cpin,@mstat,@nation,@uan,@equal,@pemp,@pdesig,@wf,@wt,@dept,@desig,@civ,@doj,@dor,@ror,@nom,@nage,@nrel,@sname,@sage,@rem," & frmMain.UserID & ",getdate())"
            InsertCmd.Connection = conSMS.sqlConn
            InsertCmd.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {
                                          New System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 0, "EMP_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.VarChar, 0, "EMP_TYPE"),
                                          New System.Data.SqlClient.SqlParameter("@gender", System.Data.SqlDbType.VarChar, 0, "GENDER"),
                                          New System.Data.SqlClient.SqlParameter("@dob", System.Data.SqlDbType.Date, 0, "DOB"),
                                          New System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar, 0, "F_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@mname", System.Data.SqlDbType.VarChar, 0, "M_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@mobno", System.Data.SqlDbType.VarChar, 0, "MOB_NO"),
                                          New System.Data.SqlClient.SqlParameter("@ecno", System.Data.SqlDbType.VarChar, 0, "EC_NO"),
                                          New System.Data.SqlClient.SqlParameter("@paddr", System.Data.SqlDbType.VarChar, 0, "P_ADDR"),
                                          New System.Data.SqlClient.SqlParameter("@ppin", System.Data.SqlDbType.VarChar, 0, "P_PIN"),
                                          New System.Data.SqlClient.SqlParameter("@caddr", System.Data.SqlDbType.VarChar, 0, "C_ADDR"),
                                          New System.Data.SqlClient.SqlParameter("@cpin", System.Data.SqlDbType.VarChar, 0, "C_PIN"),
                                          New System.Data.SqlClient.SqlParameter("@mstat", System.Data.SqlDbType.VarChar, 0, "M_STATUS"),
                                          New System.Data.SqlClient.SqlParameter("@nation", System.Data.SqlDbType.VarChar, 0, "NATIONALITY"),
                                          New System.Data.SqlClient.SqlParameter("@uan", System.Data.SqlDbType.VarChar, 0, "UAN_NO"),
                                          New System.Data.SqlClient.SqlParameter("@equal", System.Data.SqlDbType.VarChar, 0, "EDU_QUAL"),
                                          New System.Data.SqlClient.SqlParameter("@pemp", System.Data.SqlDbType.VarChar, 0, "PREV_EMPL"),
                                          New System.Data.SqlClient.SqlParameter("@pdesig", System.Data.SqlDbType.VarChar, 0, "PREV_DESIG"),
                                          New System.Data.SqlClient.SqlParameter("@wf", System.Data.SqlDbType.Date, 0, "W_FROM"),
                                          New System.Data.SqlClient.SqlParameter("@wt", System.Data.SqlDbType.Date, 0, "W_TO"),
                                          New System.Data.SqlClient.SqlParameter("@dept", System.Data.SqlDbType.VarChar, 0, "DEPT"),
                                          New System.Data.SqlClient.SqlParameter("@desig", System.Data.SqlDbType.VarChar, 0, "DESIG"),
                                          New System.Data.SqlClient.SqlParameter("@civ", System.Data.SqlDbType.Bit, 1, "CLASS_IV"),
                                          New System.Data.SqlClient.SqlParameter("@doj", System.Data.SqlDbType.Date, 0, "DOJ"),
                                          New System.Data.SqlClient.SqlParameter("@dor", System.Data.SqlDbType.Date, 0, "DOR"),
                                          New System.Data.SqlClient.SqlParameter("@ror", System.Data.SqlDbType.VarChar, 0, "ROR"),
                                          New System.Data.SqlClient.SqlParameter("@nom", System.Data.SqlDbType.VarChar, 0, "NOMINEE"),
                                          New System.Data.SqlClient.SqlParameter("@nage", System.Data.SqlDbType.Int, 0, "NOM_AGE"),
                                          New System.Data.SqlClient.SqlParameter("@nrel", System.Data.SqlDbType.VarChar, 0, "NOM_REL"),
                                          New System.Data.SqlClient.SqlParameter("@sname", System.Data.SqlDbType.VarChar, 0, "SPOUSE_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@sage", System.Data.SqlDbType.Int, 0, "SPOUSE_AGE"),
                                          New System.Data.SqlClient.SqlParameter("@rem", System.Data.SqlDbType.VarChar, 0, "REMARKS")
                                      })
            adpEmployee.InsertCommand = InsertCmd
    
            UpdateCmd.CommandText = "update sl_employee set EMP_NAME=@name, EMP_TYPE=@type, GENDER=@gender, DOB=@dob, F_NAME=@fname, M_NAME=@mname, MOB_NO=@mobno, EC_NO=@ecno, P_ADDR=@paddr, P_CITY=" & cmbPCity.SelectedValue & ", P_STATE=" & cmbPState.SelectedValue & ", P_PIN=@ppin, C_ADDR=@caddr, C_CITY=" & cmbCCity.SelectedValue & ", C_STATE=" & cmbCState.SelectedValue & ", C_PIN=@cpin, M_STATUS=@mstat, NATIONALITY=@nation, UAN_NO=@uan, EDU_QUAL=@equal, PREV_EMPL=@pemp, PREV_DESIG=@pdesig, W_FROM=@wf, W_TO=@wt, DEPT=@dept, DESIG=@desig, CLASS_IV=@civ, DOJ=@doj, DOR=@dor, ROR=@ror, NOMINEE=@nom, NOM_AGE=@nage, NOM_REL=@nrel, SPOUSE_NAME=@sname, SPOUSE_AGE=@sage, REMARKS=@rem, [MODBY]=" & frmMain.UserID & ", [MODON]=getdate() where emp_id=@original_emp_id"
            UpdateCmd.Connection = conSMS.sqlConn
            UpdateCmd.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {
                                          New System.Data.SqlClient.SqlParameter("@original_emp_id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "EMP_ID", System.Data.DataRowVersion.Original, Nothing),
                                          New System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 0, "EMP_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.VarChar, 0, "EMP_TYPE"),
                                          New System.Data.SqlClient.SqlParameter("@gender", System.Data.SqlDbType.VarChar, 0, "GENDER"),
                                          New System.Data.SqlClient.SqlParameter("@dob", System.Data.SqlDbType.Date, 0, "DOB"),
                                          New System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar, 0, "F_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@mname", System.Data.SqlDbType.VarChar, 0, "M_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@mobno", System.Data.SqlDbType.VarChar, 0, "MOB_NO"),
                                          New System.Data.SqlClient.SqlParameter("@ecno", System.Data.SqlDbType.VarChar, 0, "EC_NO"),
                                          New System.Data.SqlClient.SqlParameter("@paddr", System.Data.SqlDbType.VarChar, 0, "P_ADDR"),
                                          New System.Data.SqlClient.SqlParameter("@ppin", System.Data.SqlDbType.VarChar, 0, "P_PIN"),
                                          New System.Data.SqlClient.SqlParameter("@caddr", System.Data.SqlDbType.VarChar, 0, "C_ADDR"),
                                          New System.Data.SqlClient.SqlParameter("@cpin", System.Data.SqlDbType.VarChar, 0, "C_PIN"),
                                          New System.Data.SqlClient.SqlParameter("@mstat", System.Data.SqlDbType.VarChar, 0, "M_STATUS"),
                                          New System.Data.SqlClient.SqlParameter("@nation", System.Data.SqlDbType.VarChar, 0, "NATIONALITY"),
                                          New System.Data.SqlClient.SqlParameter("@uan", System.Data.SqlDbType.VarChar, 0, "UAN_NO"),
                                          New System.Data.SqlClient.SqlParameter("@equal", System.Data.SqlDbType.VarChar, 0, "EDU_QUAL"),
                                          New System.Data.SqlClient.SqlParameter("@pemp", System.Data.SqlDbType.VarChar, 0, "PREV_EMPL"),
                                          New System.Data.SqlClient.SqlParameter("@pdesig", System.Data.SqlDbType.VarChar, 0, "PREV_DESIG"),
                                          New System.Data.SqlClient.SqlParameter("@wf", System.Data.SqlDbType.Date, 0, "W_FROM"),
                                          New System.Data.SqlClient.SqlParameter("@wt", System.Data.SqlDbType.Date, 0, "W_TO"),
                                          New System.Data.SqlClient.SqlParameter("@dept", System.Data.SqlDbType.VarChar, 0, "DEPT"),
                                          New System.Data.SqlClient.SqlParameter("@desig", System.Data.SqlDbType.VarChar, 0, "DESIG"),
                                          New System.Data.SqlClient.SqlParameter("@civ", System.Data.SqlDbType.Bit, 0, "CLASS_IV"),
                                          New System.Data.SqlClient.SqlParameter("@doj", System.Data.SqlDbType.Date, 0, "DOJ"),
                                          New System.Data.SqlClient.SqlParameter("@dor", System.Data.SqlDbType.Date, 0, "DOR"),
                                          New System.Data.SqlClient.SqlParameter("@ror", System.Data.SqlDbType.VarChar, 0, "ROR"),
                                          New System.Data.SqlClient.SqlParameter("@nom", System.Data.SqlDbType.VarChar, 0, "NOMINEE"),
                                          New System.Data.SqlClient.SqlParameter("@nage", System.Data.SqlDbType.Int, 0, "NOM_AGE"),
                                          New System.Data.SqlClient.SqlParameter("@nrel", System.Data.SqlDbType.VarChar, 0, "NOM_REL"),
                                          New System.Data.SqlClient.SqlParameter("@sname", System.Data.SqlDbType.VarChar, 0, "SPOUSE_NAME"),
                                          New System.Data.SqlClient.SqlParameter("@sage", System.Data.SqlDbType.Int, 0, "SPOUSE_AGE"),
                                          New System.Data.SqlClient.SqlParameter("@rem", System.Data.SqlDbType.VarChar, 0, "REMARKS")
                                      })
    
            adpEmployee.UpdateCommand = UpdateCmd
    
            DeleteCmd.CommandText = "update sl_employee set dor=getdate(), ror='Record Deleted', [MODBY]=" & frmMain.UserID & ", [MODON]=getdate() where emp_id=@original_emp_id"
            DeleteCmd.Connection = conSMS.sqlConn
            DeleteCmd.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {
                                          New System.Data.SqlClient.SqlParameter("@original_emp_id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "EMP_ID", System.Data.DataRowVersion.Original, Nothing)
                                              })
    
            adpEmployee.DeleteCommand = DeleteCmd
        End Sub
    
        Private Sub Refresh_Navigation()
            btnFirst.Enabled = Not (BMB.Position = 0 Or EditMode)
            btnLast.Enabled = Not (BMB.Position = BMB.Count - 1 Or EditMode)
            btnNext.Enabled = Not (BMB.Position = BMB.Count - 1 Or EditMode)
            btnPrev.Enabled = Not (BMB.Position = 0 Or EditMode)
            btnFind.Enabled = Not (BMB.Count = 0 Or EditMode)
            btnDelete.Enabled = Not (BMB.Count = 0 Or EditMode)
            btnEdit.Enabled = Not (EditMode And BMB.Count = 0)
    
            If EditMode Then
                pbPhoto.Cursor = Cursors.Hand
                ttCommon.SetToolTip(pbPhoto, "Click to Add/Edit Image")
            Else
                ttCommon.SetToolTip(pbPhoto, "")
                pbPhoto.Cursor = Cursors.Default
            End If
    
            lblRPos.Text = "Record " & BMB.Position + 1 & " of " & BMB.Count
        End Sub
    
        Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
            BMB.Position = 0
            Refresh_Navigation()
        End Sub
    
        Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
            BMB.Position -= 1
            Refresh_Navigation()
        End Sub
    
        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
            BMB.Position += 1
            Refresh_Navigation()
        End Sub
    
        Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
            BMB.Position = BMB.Count
            Refresh_Navigation()
        End Sub
    
        Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
    
        End Sub
    
        Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
            If btnNew.Image.Tag = "Save" Then
                BMB.EndCurrentEdit()
                Update_Image()
    
                If SaveRecord() Then
                    btnNew.Image = My.Resources._new
                    btnNew.Image.Tag = "NewRec"
                    ttCommon.SetToolTip(btnNew, "Add New Record")
    
                    btnEdit.Image = My.Resources.edit
                    btnEdit.Image.Tag = "EditRec"
                    ttCommon.SetToolTip(btnEdit, "Edit Record")
    
                    EditMode = False
                    Change_Edit_Mode(EditMode)
                    Refresh_Navigation()
                End If
            Else
                AddRecord()
            End If
        End Sub
    
        Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
            If btnEdit.Image.Tag = "RevertAdd" Or btnEdit.Image.Tag = "RevertEdit" Then
                btnNew.Image = My.Resources._new
                btnNew.Image.Tag = "NewRec"
                ttCommon.SetToolTip(btnNew, "Add New Recrd")
                btnEdit.Image = My.Resources.edit
                btnEdit.Image.Tag = "EditRec"
                ttCommon.SetToolTip(btnEdit, "Edit Record")
    
                BMB.CancelCurrentEdit()
                recPosition = BMB.Position
                BindFields()
                If recPosition >= 0 Then BMB.Position = recPosition Else Me.Close()
                EditMode = False
                Change_Edit_Mode(EditMode)
                Refresh_Navigation()
            Else
                EditRecord()
            End If
        End Sub
    
        Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
            Dim dlgDelete As DialogResult
    
            If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                Try
                    dlgDelete = MsgBox("Are you sure you want to delete this record ?", vbQuestion + vbYesNo, "Delete Record")
    
                    If dlgDelete = DialogResult.Yes Then
                        BMB.EndCurrentEdit()
                        adpEmployee.Update(dsEmployee.Tables(0))
                        dsEmployee.Tables(0).AcceptChanges()
    
                        MsgBox("Record Deleted Successfully!", MsgBoxStyle.Information, "Delete Record")
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical, "Delete Record")
                End Try
            End If
        End Sub
    
        Private Sub pbPhoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles pbPhoto.Click
            If EditMode Then cmsImage.Show(MousePosition) : tsmDelete.Enabled = Not IsNothing(pbPhoto.Image)
        End Sub
    
        Private Sub Browse_Image(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmBrowse.Click
            Dim OFD As New OpenFileDialog
            Dim oStream As IO.Stream
    
            OFD.Title = "Select an Image"
            OFD.Filter = "Image Files (*.jpg;*.gif;*.bmp;*.png)|*.jpg;*.gif;*.bmp;*.png"
            OFD.FilterIndex = 0
            OFD.RestoreDirectory = True
    
            If OFD.ShowDialog() = Windows.Forms.DialogResult.OK Then
                oStream = OFD.OpenFile()
                ImageFile = OFD.FileName
    
                If Not (oStream Is Nothing) Then
                    Try
                        pbPhoto.Image = Image.FromStream(oStream)
                    Catch exc As Exception
                        MsgBox("Errr loading image — " & exc.Message, MsgBoxStyle.Critical, "Select Image")
                    Finally
                        oStream.Flush()
                        oStream.Close()
                    End Try
                End If
            End If
        End Sub
    
        Private Sub Delete_Image(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmDelete.Click
            Dim cmdDelete As New SqlCommand("update company set c_logo=null where c_id=" & txtID.Text, conSMS.sqlConn)
            Dim dlgResponse As New DialogResult
    
            dlgResponse = MsgBox("Are you sure you want to delete this image?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Delete Image")
    
            If dlgResponse = Windows.Forms.DialogResult.Yes Then
                If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                    Try
                        cmdDelete.ExecuteNonQuery()
                        pbPhoto.Image = Nothing
                    Catch ex As Exception
                        MsgBox("Error deleting image. Error is: " & ex.Message, MsgBoxStyle.Critical, "Delete Image")
                    End Try
                End If
            End If
        End Sub
    
        Private Sub Update_Image()
            If Not (ImageFile Is Nothing Or ImageFile = "") Then
                Dim cmdUImage As New SqlClient.SqlCommand
                Dim valIMG As Byte() = My.Computer.FileSystem.ReadAllBytes(ImageFile)
    
                cmdUImage.Connection = conSMS.sqlConn
                If conSMS.sqlConn.State = 0 Then conSMS.sqlConn.Open()
    
                cmdUImage.CommandType = CommandType.StoredProcedure
                cmdUImage.CommandText = "UPDATE_IMAGE_SP"
                cmdUImage.Parameters.AddWithValue("@Table", "employee")
                cmdUImage.Parameters.AddWithValue("@ID", txtID.Text)
                cmdUImage.Parameters.AddWithValue("@IMGVAL", valIMG)
                Try
                    cmdUImage.ExecuteScalar()
                    recPosition = BMB.Position
                    BindFields()
                    BMB.Position = recPosition
                Catch ex As Exception
                    MsgBox("Error while updating image — " & ex.Message, MsgBoxStyle.Critical, "Save Image")
                Finally
                    ImageFile = Nothing
                End Try
            End If
        End Sub
    
        Private Sub AddRecord()
            btnNew.Image = My.Resources.Save
            btnNew.Image.Tag = "Save"
            ttCommon.SetToolTip(btnNew, "Save")
            btnEdit.Image = My.Resources.Revert
            btnEdit.Image.Tag = "RevertAdd"
            ttCommon.SetToolTip(btnEdit, "Revert")
    
            EditMode = True
            Change_Edit_Mode(EditMode)
            BMB.AddNew()
            Set_Combo_Values()
            Refresh_Navigation()
            txtName.Select()
        End Sub
    
        Private Sub EditRecord()
            btnNew.Image = My.Resources.Save
            btnNew.Image.Tag = "Save"
            ttCommon.SetToolTip(btnNew, "Save")
            btnEdit.Image = My.Resources.Revert
            btnEdit.Image.Tag = "RevertEdit"
            ttCommon.SetToolTip(btnEdit, "Revert")
    
            EditMode = True
            Change_Edit_Mode(EditMode)
            Set_Combo_Values()
            Refresh_Navigation()
        End Sub
    
        Private Function SaveRecord() As Boolean
            If btnEdit.Image.Tag = "RevertAdd" Then
                If Not String.IsNullOrEmpty(Trim(txtName.Text)) Then
                    If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                        Try
                            BMB.EndCurrentEdit()
                            Create_Commands()
                            adpEmployee.Update(dsEmployee.Tables(0))
                            dsEmployee.Tables(0).AcceptChanges()
    
                            BindFields()
                            BMB.Position = BMB.Count
    
                            MsgBox("Employee details added successfully!", MsgBoxStyle.Information, "Add New Employee")
                            Return True
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Critical, "Add New Employee")
                            Return False
                        End Try
                    Else
                        Return False
                    End If
                Else
                    MsgBox("Employee Name can not be Blank!", MsgBoxStyle.Information, "Add New Employee")
                    Return False
                End If
            Else
                If Not String.IsNullOrEmpty(Trim(txtName.Text)) Then
                    If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
                        Try
                            BMB.EndCurrentEdit()
                            Create_Commands()
                            adpEmployee.Update(dsEmployee.Tables(0))
    
                            recPosition = BMB.Position
                            BindFields()
                            BMB.Position = recPosition
    
                            MsgBox("Record Saved Successfully!", MsgBoxStyle.Information, "Edit Record")
                            Return True
                        Catch ex As Exception
                            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
                            Return False
                        End Try
                    Else
                        Return False
                    End If
                Else
                    MsgBox("Employee Name can not be Blank!", MsgBoxStyle.Information, "Edit Record")
                    Return False
                End If
            End If
        End Function
    
        Private Sub Change_Edit_Mode(ByVal Edit_Mode As Boolean)
            For Each i In Me.TabControl1.TabPages(0).Controls
                If Strings.Left(i.name, 3) = "txt" Then
                    i.ReadOnly = Not Edit_Mode
                ElseIf Strings.Left(i.name, 3) = "cmb" Or Strings.Left(i.name, 3) = "dtp" Then
                    i.Visible = Edit_Mode
                End If
            Next
    
            For Each i In Me.TabControl1.TabPages(1).Controls
                If Strings.Left(i.name, 3) = "txt" Then
                    i.ReadOnly = Not Edit_Mode
                ElseIf Strings.Left(i.name, 3) = "cmb" Or Strings.Left(i.name, 3) = "dtp" Then
                    i.Visible = Edit_Mode
                End If
    
                If Strings.Left(i.name, 3) = "Gro" Then
                    For Each j In i.Controls
                        If Strings.Left(j.name, 3) = "txt" Or Strings.Left(j.name, 2) = "cb" Then
                            If Strings.Left(j.name, 2) = "cb" Then
                                j.enabled = Edit_Mode
                            Else
                                j.ReadOnly = Not Edit_Mode
                            End If
                        ElseIf Strings.Left(j.name, 3) = "cmb" Or Strings.Left(j.name, 3) = "dtp" Then
                            j.Visible = Edit_Mode
                        End If
                    Next
                End If
            Next
    
            txtID.ReadOnly = True
        End Sub
    
        Private Sub Set_Combo_Values()
            If btnEdit.Image.Tag = "RevertAdd" Then
                Me.TabControl1.SelectedTab = TabControl1.TabPages(1)
    
                txtGender.Text = cmbGender.Text
                txtMStatus.Text = cmbMStatus.Text
                txtNationality.Text = cmbNationality.Text
                txtEType.Text = cmbEType.Text
                txtECat.Text = cmbECat.Text
    
                txtDOB.Text = dtpDOB.Value.ToShortDateString
                txtDOJ.Text = dtpDOJ.Value.ToShortDateString
                cbClass4.Checked = True
                cbClass4.Checked = False
    
                Me.TabControl1.SelectedTab = TabControl1.TabPages(0)
            Else
                cmbGender.Text = dsEmployee.Tables(0).Rows(BMB.Position)("gender", DataRowVersion.Original).ToString()
                cmbMStatus.Text = dsEmployee.Tables(0).Rows(BMB.Position)("m_status", DataRowVersion.Original).ToString()
                cmbNationality.Text = dsEmployee.Tables(0).Rows(BMB.Position)("nationality", DataRowVersion.Original).ToString()
                cmbEType.Text = dsEmployee.Tables(0).Rows(BMB.Position)("emp_type", DataRowVersion.Original).ToString()
                cmbECat.Text = dsEmployee.Tables(0).Rows(BMB.Position)("dept", DataRowVersion.Original).ToString()
                cmbPCity.Text = dsEmployee.Tables(0).Rows(BMB.Position)("p_city", DataRowVersion.Original).ToString()
                cmbPState.Text = dsEmployee.Tables(0).Rows(BMB.Position)("p_state", DataRowVersion.Original).ToString()
                cmbCCity.Text = dsEmployee.Tables(0).Rows(BMB.Position)("c_city", DataRowVersion.Original).ToString()
                cmbCState.Text = dsEmployee.Tables(0).Rows(BMB.Position)("c_state", DataRowVersion.Original).ToString()
    
                dtpDOB.Text = dsEmployee.Tables(0).Rows(BMB.Position)("dob", DataRowVersion.Original)
                dtpDOJ.Text = dsEmployee.Tables(0).Rows(BMB.Position)("doj", DataRowVersion.Original)
                If Not IsDBNull(dsEmployee.Tables(0).Rows(BMB.Position)("dor", DataRowVersion.Original)) Then dtpDOR.Text = dsEmployee.Tables(0).Rows(BMB.Position)("dor", DataRowVersion.Original)
                If Not IsDBNull(dsEmployee.Tables(0).Rows(BMB.Position)("w_from", DataRowVersion.Original)) Then dtpWFrom.Text = dsEmployee.Tables(0).Rows(BMB.Position)("w_from", DataRowVersion.Original)
                If Not IsDBNull(dsEmployee.Tables(0).Rows(BMB.Position)("w_to", DataRowVersion.Original)) Then dtpWTo.Text = dsEmployee.Tables(0).Rows(BMB.Position)("w_to", DataRowVersion.Original)
            End If
        End Sub
    
        Private Sub Combo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbGender.SelectedIndexChanged, cmbMStatus.SelectedIndexChanged, cmbNationality.SelectedIndexChanged
            If EditMode = True Then Me.TabControl1.TabPages(0).Controls("txt" & Strings.Mid(sender.name, 4)).Text = sender.Text
        End Sub
    
        Private Sub cmbEType_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbEType.SelectedIndexChanged
            If EditMode = True Then txtEType.Text = sender.text
        End Sub
    
        Private Sub cmbECat_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbECat.SelectedIndexChanged
            If EditMode = True Then txtECat.Text = sender.text
        End Sub
    
        Private Sub dtpDOB_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpDOB.ValueChanged
            If EditMode Then txtDOB.Text = dtpDOB.Value.ToShortDateString
        End Sub
    
        Private Sub dtpWFrom_Validated(sender As Object, e As System.EventArgs) Handles dtpWFrom.Validated
            If dtpWFrom.Checked = False Then txtWFrom.Text = ""
        End Sub
    
        Private Sub dtpWF_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpWFrom.ValueChanged
            If EditMode Then txtWFrom.Text = dtpWFrom.Value.ToShortDateString
        End Sub
    
        Private Sub dtpWTo_Validated(sender As Object, e As System.EventArgs) Handles dtpWTo.Validated
            If dtpWTo.Checked = False Then txtWTo.Text = ""
        End Sub
    
        Private Sub dtpWT_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpWTo.ValueChanged
            If EditMode Then txtWTo.Text = dtpWTo.Value.ToShortDateString
        End Sub
    
        Private Sub dtpDOJ_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpDOJ.ValueChanged
            If EditMode Then txtDOJ.Text = dtpDOJ.Value.ToShortDateString
        End Sub
    
        Private Sub dtpDOR_Validated(sender As Object, e As System.EventArgs) Handles dtpDOR.Validated
            If dtpDOR.Checked = False Then txtDOR.Text = ""
        End Sub
    
        Private Sub dtpDOR_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpDOR.ValueChanged
            If EditMode And dtpDOR.Checked Then txtDOR.Text = dtpDOR.Value.ToShortDateString
        End Sub
    
    	Private Sub adpEmployee_RowUpdated(sender As Object, e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles adpEmployee.RowUpdated
            Dim changesDataSet As New DataSet
    
            changesDataSet = dsEmployee.GetChanges(DataRowState.Modified)
            PrintValues(changesDataSet, "Subset values")
        End Sub
    
        Private Sub PrintValues(dataSet As DataSet, label As String)
            Console.WriteLine(label + ControlChars.Cr)
            Dim table As DataTable
            For Each table In dataSet.Tables
                Console.WriteLine("TableName: " + table.TableName)
                Dim row As DataRow
                For Each row In table.Rows
                    Dim column As DataColumn
                    For Each column In table.Columns
                        Console.Write(ControlChars.Tab & " " _
                           & row(column).ToString())
                    Next column
                    Console.WriteLine()
                Next row
            Next table
        End Sub
    
        Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
            Me.Close()
        End Sub
    
    End Class


    Shishir

    Tuesday, August 14, 2018 6:51 AM
  • Hi,

    Your code still reports an error:"object reference not set to an instance of an object"?

    But I don't seem to find the code in the code you provided:

    Dim EmptyParameterNames = (From T In sender.Parameters.Cast(Of IDataParameter)() Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 15, 2018 8:55 AM
  • Hi,

    The code you mentioned is a part of another code, provided by Karen, to display the parameterized query along with the values of its parameters. The complete code provided by Karen is:

    Public Module CommandPeeker
        ''' <summary>
        ''' Used to display parameter values for named parameters
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <returns>
        ''' SQL statement with values from parameters
        ''' </returns>
        ''' <remarks>
        ''' Assumes each parameter has a parameter name beginning with @
        ''' and if not throws an exception. Also checks for empty parameter
        ''' names.
        ''' </remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function ActualCommandTextByNames(ByVal sender As IDbCommand) As String
            Dim sb As New System.Text.StringBuilder(sender.CommandText)
            Dim EmptyParameterNames = (From T In sender.Parameters.Cast(Of IDataParameter)() Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault
    
            If EmptyParameterNames IsNot Nothing Then
                Return sender.CommandText
            End If
    
            For Each p As IDataParameter In sender.Parameters
    
                Select Case p.DbType
                    Case DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.Date, DbType.DateTime, DbType.DateTime2, DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Time, DbType.Xml
                        If p.ParameterName(0) = "@" Then
                            If p.Value Is Nothing Then
                                Throw New Exception("no value given for parameter '" & p.ParameterName & "'")
                            End If
                            sb = sb.Replace(p.ParameterName, String.Format("'{0}'", p.Value.ToString.Replace("'", "''")))
                        Else
                            sb = sb.Replace(String.Concat("@", p.ParameterName), String.Format("'{0}'", p.Value.ToString.Replace("'", "''")))
                        End If
                    Case Else
                        sb = sb.Replace(p.ParameterName, p.Value.ToString)
                End Select
            Next
    
            Return sb.ToString
        End Function
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="Token">Alternate parameter start token</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <Runtime.CompilerServices.Extension()> _
        Public Function ActualCommandTextByNames(ByVal sender As IDbCommand, ByVal Token As String) As String
            Dim sb As New System.Text.StringBuilder(sender.CommandText)
            Dim EmptyParameterNames = (From T In sender.Parameters.Cast(Of IDataParameter)() Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault
    
            If EmptyParameterNames IsNot Nothing Then
                Return sender.CommandText
            End If
    
            For Each p As IDataParameter In sender.Parameters
    
                Select Case p.DbType
                    Case DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.Date, DbType.DateTime, DbType.DateTime2, DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Time, DbType.Xml
                        If p.ParameterName(0) = Token Then
                            If p.Value Is Nothing Then
                                Throw New Exception("no value given for parameter '" & p.ParameterName & "'")
                            End If
                            sb = sb.Replace(p.ParameterName, String.Format("'{0}'", p.Value.ToString.Replace("'", "''")))
                        Else
                            sb = sb.Replace(String.Concat(Token, p.ParameterName), String.Format("'{0}'", p.Value.ToString.Replace("'", "''")))
                        End If
                    Case Else
                        sb = sb.Replace(p.ParameterName, p.Value.ToString)
                End Select
            Next
            Return sb.ToString
        End Function
        ''' <summary>
        ''' Used to write SQL statement as written and also with passed values
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="FileName"></param>
        ''' <param name="Cheezie"></param>
        ''' <remarks></remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Sub ActualCommandTextToFile(ByVal sender As IDbCommand, ByVal FileName As String, ByVal Cheezie As Boolean)
            Dim sb As New System.Text.StringBuilder
            sb.AppendLine("SQL from code w/o values")
            sb.AppendLine(sender.CommandText.TrimEmbeddedQueryText(True))
            sb.AppendLine("SQL from code with values ready for your SQL editor to test")
            sb.AppendLine(sender.ActualCommandTextByNames.TrimEmbeddedQueryText(Cheezie))
            IO.File.WriteAllText(FileName, sb.ToString)
        End Sub
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Sub ActualCommandTextToFile(ByVal sender As IDbCommand, ByVal FileName As String)
            Dim SQL As String = sender.ActualCommandTextByNames
            IO.File.WriteAllText(FileName, SQL.TrimEmbeddedQueryText(False))
        End Sub
        ''' <summary>
        ''' Utility function to flatten SQL statements
        ''' </summary>
        ''' <param name="value"></param>
        ''' <param name="Cheezie"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' Not perfect.
        ''' </remarks>
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Function TrimEmbeddedQueryText(ByVal value As String, ByVal Cheezie As Boolean) As String
            Dim Result As String = System.Text.RegularExpressions.Regex.Replace(value.TabsToSpaces(6).TrimStart, " {2,}", " ")
            Dim sb As New System.Text.StringBuilder
    
            If Result.LineCount > 0 Then
                Dim Lines As String() = Result.Split(Chr(13))
                For Each Item As String In Lines
                    If Item.Length > 0 Then
                        Dim Holder As String = ""
                        If Not Item.EndsWith(" ") Then
                            Holder = Item.Replace(Chr(13), " ").Replace(Chr(10), " ") & " "
                        Else
                            Holder = Item.Replace(Chr(13), " ").Replace(Chr(10), " ")
                        End If
                        sb.AppendLine(Holder)
                    End If
                Next
            End If
    
            If Cheezie Then
                Return sb.ToString.Replace("   ", " ")
            Else
                Return sb.ToString
            End If
    
        End Function
    End Module
    


    Shishir

    Wednesday, August 15, 2018 3:51 PM
  • Hi,

    I am sorry that I did not find the problem.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 16, 2018 2:58 AM