Solved

Concatenation using + or  &

Posted on 2007-11-30
9
473 Views
Last Modified: 2013-11-26
The concatenation going on here:
 
Me.ProductIDTable. Columns.Add( "FullProduct" , GetType(String) ,
"ProductName + '-' + Category + '-' + Measure + '-' + OzLtr + '-' +
SupplierName" )
 
Please explain why I can't use the & symbol to concatenate but can use the +. An in depth expanation would help. The fields in the database are being concatenated, but is appears to me that FullProduct is a string and I should be able to convert it to &...
0
Comment
Question by:mindserve
  • 5
  • 3
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20382591
are you aware that you have " " around the whole expression, so the + will not be taken into account at all?!
0
 
LVL 1

Author Comment

by:mindserve
ID: 20383234
But it does seem to be taken into consideration, the code does function with the + exactly as you see it above.
If it did not have the " around it, you get an error and the code won't run at all.


0
 
LVL 1

Author Comment

by:mindserve
ID: 20383270
Anyway, the question would assume that the code I have works  so again :

Please explain why I can't use the & symbol to concatenate but can use the +. An in depth expanation would help. The fields in the database are being concatenated, but is appears to me that FullProduct is a string and I should be able to convert it to &.
0
 
LVL 1

Author Comment

by:mindserve
ID: 20383306
Here is the code from the small application I have. It all works....Just need to know why I can't use the & instead of the + . I am concatenating the fields in the database, but it seems that they are a string in how its formatted in the code.
Option Strict On 'leave strict and explict turned on

Option Explicit On

Imports System.Data

Imports System.Data.OleDb
 

Public Class FrmEmployees
 
 
 

    Private formLoading As Boolean

    Private EmployeeRowPosition As Integer = 0

    Dim EmployeeIDConnection As OleDbConnection

    Dim EmployeeIDCommand As OleDbCommand

    Dim EmployeeIDAdapter As OleDbDataAdapter

    Dim EmployeeIDTable As DataTable

    Dim EmployeeIDManager As CurrencyManager
 

    Dim MyState As String

    Dim MyBookmark As Integer
 

    Dim LastNameList As DataTable

    Private LastNameManager As CurrencyManager

    Private EmployeeDeleteCommand As OleDbCommand
 
 

    '--------------------------------------------------------------------

    Private Sub employees_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

1:      If MyState = "Edit" Or MyState = "Add" Then

2:          MessageBox.Show("You must finish the current edit before stopping.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

3:          e.Cancel = True

4:      Else

5:          Try

                'save the updateEmployeeID table

6:              Dim EmployeeIDAdapterCommands As New OleDbCommandBuilder(EmployeeIDAdapter)

7:              EmployeeIDAdapter.Update(EmployeeIDTable)

8:          Catch ex As Exception

9:              MessageBox.Show(ex.Message, "Error Saving Database", MessageBoxButtons.OK, MessageBoxIcon.Error)

10:         End Try

            'close the connection

11:         EmployeeIDConnection.Close()

            'dispose of the objects

12:         EmployeeIDCommand.Dispose()

13:         EmployeeIDAdapter.Dispose()

14:         EmployeeIDTable.Dispose()

15:     End If

    End Sub
 

    Private Sub Employees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 

        'initialize objects

1:      EmployeeIDConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + Application.StartupPath + "\shx.mdb")
 

2:      Me.LastNameList = New DataTable
 

3:      Me.EmployeeIDTable = New DataTable
 
 

4:      Me.EmployeeIDCommand = New OleDbCommand("SELECT * FROM Employees")

5:      Me.EmployeeIDCommand.Connection = Me.EmployeeIDConnection

6:      Me.EmployeeIDAdapter = New OleDbDataAdapter

7:      Me.EmployeeIDAdapter.SelectCommand = Me.EmployeeIDCommand
 

8:      Dim cmdBuilder As New OleDbCommandBuilder(Me.EmployeeIDAdapter)

        'this will get the right EmployeeID value during Insert action

9:      AddHandler EmployeeIDAdapter.RowUpdated, AddressOf EmployeeIDAdapter_RowUpdated
 

        'keep this command and use it later, see Delete method

10:     Me.EmployeeDeleteCommand = cmdBuilder.GetDeleteCommand(True)

        'there are 57 paramenters, we need only one

11:     Me.EmployeeDeleteCommand.Parameters.Clear()
 

12:     Dim idParam As New OleDbParameter()

13:     idParam.ParameterName = "Original_EmployeeID"

14:     idParam.OleDbType = OleDbType.Integer

15:     idParam.SourceColumn = "EmployeeID"

16:     idParam.SourceVersion = DataRowVersion.Original 'very important, makes sure the value is the one loaded from the database

17:     Me.EmployeeDeleteCommand.Parameters.Add(idParam)

        'now change the SQL Statement to mach the new parameter collection

18:     Me.EmployeeDeleteCommand.CommandText = "DELETE FROM Employees WHERE EmployeeID=?"
 
 

19:     Me.EmployeeIDManager = DirectCast(Me.BindingContext(EmployeeIDTable), CurrencyManager)

20:     AddHandler EmployeeIDManager.PositionChanged, AddressOf EmployeeIDManager_PositionChanged
 

        'add full name column

21:     Dim colLastName As New DataGridViewTextBoxColumn

22:     colLastName.DataPropertyName = "FullName"

23:     Me.dgEmp.Columns.Add(colLastName)

24:     Me.dgEmp.AutoGenerateColumns = False 'to make sure that only FullName column will be displayed

25:     Me.dgEmp.DataSource = Me.EmployeeIDTable 'this way the grid and the controls will be bound to the same data
 
 

26:     Me.LastNameManager = DirectCast(Me.BindingContext(Me.LastNameList), CurrencyManager)

        'Load last names

27:     Me.FillLastNames()

        'add a primary key,it will load data faster

28:     Me.LastNameList.PrimaryKey = New DataColumn() {Me.LastNameList.Columns(0)}

        'AddHandler LastNameManager.PositionChanged, AddressOf LastNameList_PositionChanged

        'AddHandler LastNameManager.CurrentChanged, AddressOf LastNameList_PositionChanged

        'AddHandler LastNameManager.CurrentItemChanged, AddressOf LastNameList_PositionChanged

        'AddHandler LastNameManager.MetaDataChanged, AddressOf LastNameList_PositionChanged
 

29:     Me.FillEmployees()
 

30:     Try

            'Concatenate the first and last name of the customer.

31:         Me.EmployeeIDTable.Columns.Add("FullName", GetType(String), "LastName + ', ' + FirstName")

            'set the default value of Dependents column

            '//////////////////////////////////////////////////////////////////////

            '32:         Dim myDataView As DataView = New DataView(Me.EmployeeIDTable) 'JHG

            '33:         myDataView.Sort = "LastName ASC, FirstName ASC" 'JHG

            '34:         Me.dgEmp.DataSource = myDataView 'JHG

            EmployeeIDTable.DefaultView.Sort = "FirstName"

            'JHG the problem is that it does not bind now to the textboxes.But it does sort the grid...:)

            'It should load and bind to textboxes in alphabetical order.

            '//////////////////////////////////////////////////////////////////////////////////

35:         Me.EmployeeIDTable.Columns("Dependents").DefaultValue = 1

36:     Catch ex As Exception

37:         Console.WriteLine()

38:     End Try
 
 

        'bind controls

39:     Me.BindControls()
 
 
 

        'show initial photo

40:     Call ShowUnbound()

41:     Call SetState("View")

        'Call ShowPhoto()

42:     Me.Top = 80
 

        'txtStatus.Text = CStr(CDbl(ComboBox3.SelectedIndex.ToString) + 1) & " of " & EmployeeIDManager.Count - 0

    End Sub
 

#Region "Added 09/20/2007"
 

    ''' <summary>

    ''' Load Last Names

    ''' </summary>

    ''' <remarks></remarks>

    Public Sub FillLastNames()
 

        '"Using" statement makes sure the object is disposed even if errors occur

1:      Using adapter As New OleDbDataAdapter

2:          Using cmd As New OleDbCommand("SELECT DISTINCT LastName FROM Employees ORDER BY LastName") '"DISTINCT" keyword doesn't load duplicates

3:              cmd.Connection = Me.EmployeeIDConnection

4:              adapter.SelectCommand = cmd

                'fill last name list

5:              adapter.Fill(Me.LastNameList)

                'fill the combobox

6:              Me.ComboBox3.DataSource = Me.LastNameList

7:              Me.ComboBox3.ValueMember = "LastName"

8:              Me.ComboBox3.DisplayMember = "LastName"

                'after this, the SelectedIndexChanged event is fired, so the current data will be loaded:

                'ComboBox3_SelectedIndexChanged will call FillEmployees method
 

9:          End Using

10:     End Using
 
 

    End Sub
 

    ''' <summary>

    ''' Fills EmployeeIDTable filtered by the current value of the LastNames combobox

    ''' </summary>

    ''' <remarks></remarks>

    Public Sub FillEmployees()

1:      Me.FillEmployees(Me.ComboBox3.Text)

    End Sub
 

    Public Sub FillEmployees(ByVal searchTerm As String)

1:      Me.FillEmployees(searchTerm, "LastName", "=")

    End Sub
 

    ''' <summary>

    ''' Fills the EmployeeIDTable using a search term, field (usualy LastName or FirstName) and an operator("LIKE" or "=")

    ''' </summary>

    ''' <param name="searchTerm"></param>

    ''' <param name="field"></param>

    ''' <param name="operator"></param>

    ''' <remarks></remarks>

    Public Sub FillEmployees(ByVal searchTerm As String, ByVal field As String, ByVal [operator] As String)

1:      Try

2:          Dim cmdText As String = String.Empty

            'build the sql statement;it will also create the FullName column

3:          Select Case [operator]

                Case "LIKE"

4:                  cmdText = "SELECT * FROM Employees WHERE " & field & " LIKE '" & searchTerm & "%' ORDER BY " & field

5:              Case "="

6:                  cmdText = "SELECT * FROM Employees WHERE " & field & " = '" & searchTerm & "' ORDER BY " & field

7:                  End Select

8:          If Not String.IsNullOrEmpty(cmdText) Then

9:              Me.EmployeeIDCommand.CommandText = cmdText

                'clear current data

10:             Me.EmployeeIDTable.Clear()

11:             Me.EmployeeIDAdapter.Fill(Me.EmployeeIDTable)

12:         End If
 
 

13:         Me.ShowUnbound()
 

14:         Me.lblSearchStatus.Text = ""

15:     Catch ex As Exception

16:         Console.WriteLine(ex.ToString)

17:     End Try
 

    End Sub
 

    Private Sub btnSearchCustomerNames_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchCustomerNames.Click

1:      Control.CheckForIllegalCrossThreadCalls = False 'to avoid exceptions, this can be set only once in the constructor
 

        'more options can be added here:disable other controls, add a cancel button etc.

2:      Me.lblSearchStatus.Text = "Searching. Please wait ..."

        'create a new thread

3:      Dim t As New Threading.Thread(New Threading.ParameterizedThreadStart(AddressOf Me.FillEmployeesSafe))

        'start the thread with parameters

4:      t.Start(New Object() {Me.txtSearchTerm.Text, Me.cmbSearchField.Text, Me.cmbOperator.Text})

        'make sure the ui is not blocked

5:      Do While t.IsAlive

6:          Application.DoEvents()

7:      Loop
 

        'if you want to run this as it was before, just comment or delete the code above and uncomment the following line

        'Me.FillEmployees(Me.txtSearchTerm.Text, Me.cmbSearchField.Text, Me.cmbOperator.Text)

    End Sub
 

    ''' <summary>

    ''' The same as FillEmployees.Used when performing search in another thread

    ''' </summary>

    ''' <param name="params"></param>

    ''' <remarks></remarks>

    Private Sub FillEmployeesSafe(ByVal params As Object)

1:      Dim parameters As Object() = CType(params, [Object]())

2:      Me.FillEmployees(CStr(parameters(0)), CStr(parameters(1)), CStr(parameters(2)))

    End Sub
 

    Public Sub BindControls()

1:      txtFirstName.DataBindings.Add("Text", EmployeeIDTable, "FirstName")

2:      txtLastName.DataBindings.Add("Text", EmployeeIDTable, "LastName")

        ' txtEmployeeName.DataBindings.Add("Text", EmployeeIDTable, "EmployeeName")

3:      txtMI.DataBindings.Add("Text", EmployeeIDTable, "MI")

4:      TxtEmployeeID.DataBindings.Add("Text", EmployeeIDTable, "EmployeeID")

5:      txtTitle.DataBindings.Add("Text", EmployeeIDTable, "Title")

6:      TxtHireDate.DataBindings.Add("Text", EmployeeIDTable, "HireDate")

7:      TxtTerminationDate.DataBindings.Add("Text", EmployeeIDTable, "TerminationDate")

8:      RTFReasonForTermination.DataBindings.Add("Text", EmployeeIDTable, "ReasonForTermination")

9:      txtAddress.DataBindings.Add("Text", EmployeeIDTable, "Address")

        '/////////////////////////////////////////////////////////////////////////

10:     txtCity.DataBindings.Add("Text", EmployeeIDTable, "City")

11:     txtState.DataBindings.Add("Text", EmployeeIDTable, "State")

12:     txtZip.DataBindings.Add("Text", EmployeeIDTable, "ZipCode")

13:     txtBirthDate.DataBindings.Add("Text", EmployeeIDTable, "BirthDate")

14:     lblPhotoFileBefore.DataBindings.Add("Text", EmployeeIDTable, "Photo")

        '///////////////////////

15:     txtOccupation.DataBindings.Add("Text", EmployeeIDTable, "EmployeeCategory")

16:     RTFNotes.DataBindings.Add("Text", EmployeeIDTable, "Notes")

17:     txtReferredBy.DataBindings.Add("Text", EmployeeIDTable, "ReportsTo")

18:     txtHomePhone.DataBindings.Add("Text", EmployeeIDTable, "HomePhone")

19:     txtWorkPhone.DataBindings.Add("Text", EmployeeIDTable, "EmgPhone")

20:     txtCellPhone.DataBindings.Add("Text", EmployeeIDTable, "EmgContact")
 

        '////////////////////////////////////////////////////////////////////////////////////////////

21:     txtSS.DataBindings.Add("Text", EmployeeIDTable, "EmployeeSS")

22:     TxtPayrollID.DataBindings.Add("Text", EmployeeIDTable, "PayrollID")

23:     TxtEmpSchedID.DataBindings.Add("Text", EmployeeIDTable, "EmpSchedID")

24:     TxtPayRate.DataBindings.Add("Text", EmployeeIDTable, "PayRate")

25:     txtRate.DataBindings.Add("Text", EmployeeIDTable, "TypeRate")

26:     TxtMarriedEmp.DataBindings.Add("Text", EmployeeIDTable, "MarriedEmp")

27:     TxtNotMarried.DataBindings.Add("Text", EmployeeIDTable, "NotMarried") 'This is what is causing the update error message,,but why?See second tabNOTE: Temporarily removed it from the select statement.

28:     TxtDependents.DataBindings.Add("Text", EmployeeIDTable, "Dependents")

        '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 

29:     Me.EmployeeIDTable.Columns("Male").DefaultValue = False

30:     Me.EmployeeIDTable.Columns("Female").DefaultValue = False

31:     RBFemale.DataBindings.Add("Checked", EmployeeIDTable, "Female")

32:     RBMale.DataBindings.Add("Checked", EmployeeIDTable, "Male")

    End Sub
 

    ''' <summary>

    ''' This is fired when the user moves to another row in the grid

    ''' </summary>

    ''' <param name="sender"></param>

    ''' <param name="e"></param>

    ''' <remarks></remarks>

    Private Sub EmployeeIDManager_PositionChanged(ByVal sender As Object, ByVal e As EventArgs)

1:      Me.ShowUnbound()

    End Sub
 

    Private Sub LastNameList_PositionChanged(ByVal sender As Object, ByVal e As EventArgs)

1:      Me.FillEmployees()

    End Sub
 

    ''' <summary>

    ''' Saves the current record

    ''' </summary>

    ''' <remarks></remarks>

    Public Sub Save()

1:      Me.Validate()

2:      Me.EmployeeIDManager.EndCurrentEdit()

3:      If txtLastName.Text.Trim = "" Or txtFirstName.Text.Trim = "" Or txtTitle.Text.Trim = "" Then
 

4:          ErrorProviderEmployees.SetError(txtLastName, "You must supply a Last Name for the Employee")

5:          ErrorProviderEmployees.SetError(txtFirstName, "You must supply a First Name for the Employee")

6:          ErrorProviderEmployees.SetError(txtTitle, "You must supply a Title for the Employee")

7:          MessageBox.Show("You must have a Last Name, FirstName and Title entered before you can save.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

8:          txtLastName.Focus()

9:          ErrorProviderEmployees.SetError(txtLastName, "")

10:         ErrorProviderEmployees.SetError(txtFirstName, "")

11:         ErrorProviderEmployees.SetError(txtTitle, "")

12:         Exit Sub

13:     End If
 

14:     Try

15:         Dim drv As DataRowView = CType(Me.EmployeeIDManager.Current(), DataRowView)

16:         Dim row As DataRow = drv.Row
 

            'keep the previous Last Name, if an existing row is updated

17:         Dim origLastName As String = String.Empty

18:         If row.HasVersion(DataRowVersion.Original) Then 'it's not a new row

19:             origLastName = CStr(row("LastName", DataRowVersion.Original))

20:         End If
 

21:         If Me.EmployeeIDAdapter.Update(New DataRow() {row}) > 0 Then

22:             Dim lastName As String = CStr(row("LastName"))

23:             If lastName <> origLastName Then 'Last Name was changed, so add it in the list

24:                 If Me.LastNameList.Rows.Find(lastName) Is Nothing Then

25:                     Me.LastNameList.Rows.Add(New Object() {lastName})

26:                 End If
 

27:                 If origLastName <> String.Empty Then 'existing row

                        'check if the old Last Name should be in the list

28:                     Using cmd As New OleDbCommand("SELECT DISTINCT LastName FROM Employees WHERE LastName ='" & origLastName & "'") '"DISTINCT" keyword doesn't load duplicates

29:                         cmd.Connection = Me.EmployeeIDConnection

30:                         Try

31:                             cmd.Connection.Open()

32:                             Dim result As Object = cmd.ExecuteScalar

33:                             If result Is Nothing Then 'there are no records matching origLastName

                                    'delete it from the list

34:                                 Dim lnRow As DataRow = Me.LastNameList.Rows.Find(origLastName) 'this works only with primary key columns

35:                                 If lnRow IsNot Nothing Then

36:                                     Me.LastNameList.Rows.Remove(lnRow)

37:                                     Me.LastNameList.AcceptChanges()

38:                                 End If

39:                             End If

40:                         Catch ex As Exception

41:                             Console.WriteLine()

42:                         Finally

43:                             If Me.EmployeeIDConnection.State <> ConnectionState.Closed Then

44:                                 Me.EmployeeIDConnection.Close()

45:                             End If

46:                         End Try
 
 

47:                     End Using
 

48:                 End If
 
 

49:             End If
 
 
 
 
 

50:         End If

51:         Call ShowUnbound()

52:         Call SetState("View")

53:         Call controltabs()

54:     Catch ex As Exception

55:         Console.WriteLine(ex.ToString)

56:     End Try
 

    End Sub
 

    Public Sub Delete()
 

1:      Try

            'get the current row

2:          Dim drv As DataRowView = CType(Me.EmployeeIDManager.Current(), DataRowView)
 

3:          If drv.IsNew Then 'if it's new, it means there is not saved in the database so there's nothing to delete: just cancel the action

4:              Me.btnCancel.PerformClick()

5:              Exit Sub

6:          End If
 

            'it will be used after the delete action to check if this name should be in the list

7:          Dim lastName As String = CStr(drv("LastName"))
 

8:          Dim id As Integer = CInt(drv("EmployeeID"))

            'set the value of the parameter

9:          Me.EmployeeDeleteCommand.Parameters("Original_EmployeeID").Value = id

            'use EmployeeDeleteCommand

10:         Me.EmployeeDeleteCommand.Connection.Open()
 

11:         If Me.EmployeeDeleteCommand.ExecuteNonQuery > 0 Then 'the record was deleted from database

                'now delete the row from the datatable

12:             drv.Delete()

                'now the row still exists in the DataTable(EmployeeIDTable), but it has its RowState property = Deleted

13:             Me.EmployeeIDTable.AcceptChanges() 'to make sure the row is deleted for ever
 

                'check if Last Name still should be there

14:             If Not String.IsNullOrEmpty(lastName) Then 'this should never happen, since LastName is validated during save
 

15:                 Using cmd As New OleDbCommand("SELECT DISTINCT LastName FROM Employees WHERE LastName ='" & lastName & "'") '"DISTINCT" keyword doesn't load duplicates

16:                     cmd.Connection = Me.EmployeeIDConnection
 

                        'the connection is already open

17:                     Dim result As Object = cmd.ExecuteScalar

18:                     If result Is Nothing Then 'there are no records matching lastName

                            'delete it from the list

19:                         Dim lnRow As DataRow = Me.LastNameList.Rows.Find(lastName) 'this works only with primary key columns

20:                         If lnRow IsNot Nothing Then

21:                             Me.LastNameList.Rows.Remove(lnRow)

22:                             Me.LastNameList.AcceptChanges()

23:                             Me.FillEmployees()

24:                         End If
 

25:                     End If
 

26:                 End Using
 

27:             End If
 

28:             Call ShowUnbound()

29:             Call SetState("View")

30:             Call controltabs()
 

31:         End If
 
 

32:     Catch ex As Exception

33:         Console.WriteLine(ex.ToString)

34:     Finally

            'this code will run even if there are exceptions

35:         If Me.EmployeeDeleteCommand.Connection.State <> ConnectionState.Closed Then

36:             Me.EmployeeDeleteCommand.Connection.Close()

37:         End If

38:     End Try

    End Sub
 

    ''' <summary>

    ''' Gets the value of the primary key from the database and updates the EmployeeID in DataRow

    ''' This way concurrency errors are avoided

    ''' </summary>

    ''' <param name="sender"></param>

    ''' <param name="e"></param>

    ''' <remarks></remarks>

    Private Sub EmployeeIDAdapter_RowUpdated(ByVal sender As Object, ByVal e As OleDb.OleDbRowUpdatedEventArgs)

1:      If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then

            'create an OleDbCommand which will get the value of the primary key

2:          Using cmdIdentity As New OleDb.OleDbCommand("SELECT @@IDENTITY", Me.EmployeeIDConnection)

3:              Dim id As Integer = CInt(cmdIdentity.ExecuteScalar) 'this is the new value of EmployeeID from the database
 

                'get the current EmployeeID of the data row

4:              Dim cId As Integer

5:              If Not e.Row.IsNull("EmployeeID") Then

6:                  cId = CInt(e.Row("EmployeeID"))

7:              End If
 

8:              If id <> cId Then

9:                  e.Row("EmployeeID") = id 'set the new value

10:                 e.Row.EndEdit()

11:             End If

12:             Dim ad As OleDb.OleDbDataAdapter = DirectCast(sender, OleDb.OleDbDataAdapter)

13:             If ad.AcceptChangesDuringUpdate Then

14:                 e.Row.AcceptChanges()

15:             End If

16:         End Using

17:     End If

    End Sub
 

#End Region
 
 

    Private Sub ShowPhoto()

1:      If lblPhotoFileBefore.Text <> "" Then

2:          Try

3:              Photo.Image = Image.FromFile(lblPhotoFileBefore.Text)

4:          Catch ex As Exception

5:              MessageBox.Show(ex.Message, "Error Loading Photo", MessageBoxButtons.OK, MessageBoxIcon.Error)

6:          End Try

7:      Else

8:          Photo.Image = Nothing

9:      End If
 
 

    End Sub
 

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click

1:      EmployeeIDManager.Position = 0

2:      Call ShowUnbound()
 

    End Sub
 

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

1:      EmployeeIDManager.Position -= 1

2:      Call ShowUnbound()

    End Sub
 

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

1:      EmployeeIDManager.Position += 1

2:      Call ShowUnbound()
 

    End Sub
 

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click

1:      EmployeeIDManager.Position = EmployeeIDManager.Count - 1

2:      Call ShowUnbound()
 

    End Sub

    Private Sub SetState(ByVal AppState As String)
 

1:      Try

2:          MyState = AppState

3:          Select Case AppState

                Case "View"

4:                  btnFirst.Enabled = True

5:                  btnPrevious.Enabled = True

6:                  btnNext.Enabled = True

7:                  btnLast.Enabled = True

8:                  btnEdit.Enabled = True

9:                  btnSave.Enabled = False

10:                 btnCancel.Enabled = False

11:                 btnAdd.Enabled = True

12:                 btnDelete.Enabled = True

13:                 btnPrint.Enabled = True

14:                 btnExit.Enabled = True

15:                 txtFirstName.ReadOnly = True

16:                 txtLastName.ReadOnly = True
 

                    'dtpBirthdate.Enabled = False

17:                 btnLoadPhoto.Enabled = False

                    'ErrorProviderEmployees.Clear()
 
 

18:                 RBFemale.Enabled = True

19:                 RBMale.Enabled = True

20:                 Me.dgEmp.Enabled = True
 

21:             Case "Add", "Edit"
 

22:                 btnFirst.Enabled = False

23:                 btnPrevious.Enabled = False

24:                 btnNext.Enabled = False

25:                 btnLast.Enabled = False

26:                 btnEdit.Enabled = False

27:                 btnSave.Enabled = True

28:                 btnCancel.Enabled = True

29:                 btnAdd.Enabled = False

30:                 btnDelete.Enabled = False

31:                 btnPrint.Enabled = False

32:                 btnExit.Enabled = False

33:                 txtFirstName.ReadOnly = False

34:                 txtLastName.ReadOnly = False
 

                    'dtpBirthdate.Enabled = True
 

35:                 btnLoadPhoto.Enabled = True

                    ''CheckHome.Enabled = True
 
 
 

36:                 RBFemale.Enabled = True

37:                 RBMale.Enabled = True

38:                 Me.dgEmp.Enabled = False
 

39:                 End Select
 

40:         txtLastName.Focus()
 

            'txtStatus.Text = CStr(CDbl(ComboBox3.SelectedIndex.ToString) + 1) & " of " & EmployeeIDManager.Count - 0

41:     Catch ex As Exception

42:         System.Windows.Forms.MessageBox.Show(ex.ToString)

43:     End Try

    End Sub
 

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

1:      Call SetState("Edit")
 

    End Sub
 

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
 

1:      Me.Save()

        'If txtLastName.Text.Trim = "" Or txtFirstName.Text.Trim = "" Or txtTitle.Text.Trim = "" Then
 

        '    ErrorProviderEmployees.SetError(txtLastName, "You must supply a Last Name for the Employee")

        '    ErrorProviderEmployees.SetError(txtFirstName, "You must supply a First Name for the Employee")

        '    ErrorProviderEmployees.SetError(txtTitle, "You must supply a Title for the Employee")

        '    MessageBox.Show("You must have a Last Name, FirstName and Title entered before you can save.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        '    txtLastName.Focus()

        '    ErrorProviderEmployees.SetError(txtLastName, "")

        '    ErrorProviderEmployees.SetError(txtFirstName, "")

        '    ErrorProviderEmployees.SetError(txtTitle, "")

        '    Exit Sub

        'End If
 

        '' If txtFirstName.Text.Trim = "" Then
 

        ''ErrorProviderEmployees.SetError(txtFirstName, "You must supply a First Name for the Employee")

        '' MessageBox.Show("You must have a First Name entered before you can save.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        '' txtFirstName.Focus()

        '' ErrorProviderEmployees.SetError(txtFirstName, "")

        '' Exit Sub

        '' End If
 

        ''If txtTitle.Text.Trim = "" Then
 

        ''ErrorProviderEmployees.SetError(txtTitle, "You must supply a Title for the Employee")

        '' MessageBox.Show("You must have a Title entered before you can save.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        ''  txtTitle.Focus()

        ''ErrorProviderEmployees.SetError(txtTitle, "")

        ''Exit Sub

        '' End If
 

        'Dim SavedItem As String = TxtEmployeeID.Text

        'Dim SavedRow As Integer

        'EmployeeIDManager.EndCurrentEdit()

        'EmployeeIDTable.DefaultView.Sort = "LastName"

        '' EmployeeIDTable.DefaultView.Sort = "FirstName"

        ''EmployeeIDTable.DefaultView.Sort = "Title"

        'SavedRow = EmployeeIDTable.DefaultView.Find(SavedItem)

        'EmployeeIDManager.Position = SavedRow
 

        ''Me.ComboBox3.SelectedIndex = EmployeeIDManager.Position
 
 

        'Call ShowUnbound()

        'Call SetState("View")

        'Call controltabs()

    End Sub
 

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

1:      EmployeeIDManager.CancelCurrentEdit()

2:      If MyState = "Add" Then

3:          EmployeeIDManager.Position = MyBookmark

4:          Me.EmployeeIDTable.RejectChanges()

5:          EmployeeRowPosition = 0

6:          EmployeeIDManager.Position = 0

7:      End If

8:      Call ShowUnbound()

9:      Call SetState("View")

10:     TabControl1.SelectedTab = TabPage1

11:     Call controltabs()

    End Sub
 

    Private Sub btnLoadPhoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadPhoto.Click

1:      Try

2:          If dlgOpen.ShowDialog = Windows.Forms.DialogResult.OK Then

3:              lblPhotoFileBefore.Text = dlgOpen.FileName
 

4:              Call ShowPhoto()

5:          End If
 
 

6:      Catch ex As Exception

7:          MessageBox.Show(ex.Message, "Error Opening Photo", MessageBoxButtons.OK, MessageBoxIcon.Error)

8:      End Try

    End Sub
 

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

1:      Call SetState("Add")
 

        'ComboBox3.Text = ""
 

2:      MyBookmark = EmployeeIDManager.Position
 

3:      EmployeeIDManager.AddNew()
 

4:      Photo.Image = Nothing

5:      lblPhotoFileBefore.Text = ""
 

6:      lblPhoto.Text = ""
 

        'Call controltabs()

    End Sub
 

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 

1:      If MessageBox.Show("Are you sure you want to delete this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then

2:          Me.Delete()

            'EmployeeIDManager.RemoveAt(EmployeeIDManager.Position)

            ''btnSave.PerformClick()

            'Me.Save()

            ''Call ShowUnbound()

3:      End If

        'Call SetState("View")

        'Call controltabs()
 

    End Sub
 

    Private Sub txtFirstName_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtFirstName.KeyPress

1:      If e.KeyChar = ControlChars.Cr Then

2:          txtLastName.Focus()

3:      End If

    End Sub
 
 

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click

        'Declare the document

1:      Dim EmployeeIDDocument As Drawing.Printing.PrintDocument

        'Create the document and name it

2:      EmployeeIDDocument = New Drawing.Printing.PrintDocument()

3:      EmployeeIDDocument.DocumentName = "EmployeeID"

        'Add code handler

        'AddHandler EmployeeIDDocument.PrintPage, AddressOf Me.Print employeeID

        'Print document in preview control

4:      Dim SavedPosition As Integer = EmployeeIDManager.Position

5:      dlgPreview.Document = EmployeeIDDocument

6:      dlgPreview.ShowDialog()

        'Dispose of document when done printing

7:      EmployeeIDDocument.Dispose()

8:      EmployeeIDManager.Position = SavedPosition

    End Sub
 

    Private Sub PrintEmployeeID(ByVal sender As Object, ByVal e As Drawing.Printing.PrintPageEventArgs)

        'draw border

1:      On Error Resume Next 'this just handles the pic box not having an image.

        'no reason not to load it even if it doesn't have a picture lots of work to do here to get this to look clean.

2:      Dim MyPen As Pen = New Pen(Color.Black, 3)

3:      e.Graphics.DrawRectangle(MyPen, e.MarginBounds.X, e.MarginBounds.Y, 300, 350)

4:      Dim MyFont As Font = New Font("Arial", 12, FontStyle.Bold)

5:      Dim S As String = "Employee Data"

6:      Dim SSize As SizeF = e.Graphics.MeasureString(S, MyFont)

7:      e.Graphics.DrawString(S, MyFont, Brushes.Black, e.MarginBounds.X + CInt(0.5 * (300 - SSize.Width)), e.MarginBounds.Y + 15)

        'position picture

8:      e.Graphics.DrawImage(Photo.Image, e.MarginBounds.X + 25, e.MarginBounds.Y + 50, 200, 300)

9:      e.HasMorePages = False

        'first/last name/grade

10:     MyFont = New Font("Arial", 14)

11:     Dim Y As Integer = e.MarginBounds.Y + 260

12:     S = txtFirstName.Text
 

13:     SSize = e.Graphics.MeasureString(S, MyFont)

14:     e.Graphics.DrawString(S, MyFont, Brushes.Black, e.MarginBounds.X + CInt(0.5 * (300 - SSize.Width)), Y)

15:     Y += CInt(MyFont.GetHeight(e.Graphics))

16:     S = txtLastName.Text

17:     SSize = e.Graphics.MeasureString(S, MyFont)

18:     e.Graphics.DrawString(S, MyFont, Brushes.Black, e.MarginBounds.X + CInt(0.5 * (300 - SSize.Width)), Y)

19:     Y += CInt(MyFont.GetHeight(e.Graphics))

20:     S = "Photo " + txtAddress.Text

21:     SSize = e.Graphics.MeasureString(S, MyFont)

22:     e.Graphics.DrawString(S, MyFont, Brushes.Black, e.MarginBounds.X + CInt(0.5 * (300 - SSize.Width)), Y)

    End Sub
 

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
 

1:      Me.Close()

    End Sub
 

    Private Sub ShowUnbound()

1:      Call ShowPhoto()

2:      txtStatus.Text = (Me.EmployeeIDManager.Position + 1).ToString & " of " & EmployeeIDManager.Count.ToString

    End Sub
 

    Private Sub rdoSex_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RBMale.CheckedChanged, RBFemale.CheckedChanged

1:      Dim ButtonChecked As RadioButton = CType(sender, RadioButton)

2:      If ButtonChecked.Text = "M" Then

3:          RBMale.Text = "M"

4:      Else

5:          If ButtonChecked.Text = "F" Then

6:              RBFemale.Text = "F"

7:          End If

8:      End If

    End Sub
 
 

    Private Sub BtnLoadPhotoAfter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

1:      Try

2:          If dlgOpen.ShowDialog = Windows.Forms.DialogResult.OK Then

3:              lblPhoto.Text = dlgOpen.FileName

4:              Call ShowPhoto()

5:          End If

6:      Catch ex As Exception

7:          MessageBox.Show(ex.Message, "Error Opening Photo", MessageBoxButtons.OK, MessageBoxIcon.Error)

8:      End Try

    End Sub
 

    Private Sub BTNClosePhotoBefore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

1:      lblPhotoFileBefore.Text = ""

2:      Photo.Image = Nothing 'yes this seems to work

    End Sub
 
 
 
 
 

    Private Sub ComboBox3_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedValueChanged

1:      Me.FillEmployees()

    End Sub

  
 
 
 

    Private Sub controltabs()

1:      If TabControl1.SelectedTab Is TabPage1 Then

2:          btnFirst.Enabled = True

3:          btnNext.Enabled = True

4:          btnPrevious.Enabled = True

5:          btnLast.Enabled = True

            'btnAdd.Enabled = True

6:          btnDelete.Enabled = True

7:          ComboBox3.Enabled = True
 

8:      End If
 

9:      If TabControl1.SelectedTab Is TabPage2 Then

10:         btnFirst.Enabled = False

11:         btnNext.Enabled = False

12:         btnPrevious.Enabled = False

13:         btnLast.Enabled = False

14:         btnAdd.Enabled = False

15:         btnDelete.Enabled = False

16:         ComboBox3.Enabled = False

17:     End If
 

18:     If TabControl1.SelectedTab Is TabPage6 Then

19:         btnFirst.Enabled = False

20:         btnNext.Enabled = False

21:         btnPrevious.Enabled = False

22:         btnLast.Enabled = False

23:         btnAdd.Enabled = False

24:         btnDelete.Enabled = False

25:         ComboBox3.Enabled = False

26:     End If
 

    End Sub
 

    Private Sub TabControl1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.Click
 

1:      Try

2:          If TabControl1.SelectedTab Is TabPage1 Then

3:              btnFirst.Enabled = True

4:              btnNext.Enabled = True

5:              btnPrevious.Enabled = True

6:              btnLast.Enabled = True

7:              btnAdd.Enabled = True

8:              btnDelete.Enabled = True

9:              ComboBox3.Enabled = True
 

10:         End If

11:         If TabControl1.SelectedTab Is TabPage2 Then

12:             btnFirst.Enabled = False

13:             btnNext.Enabled = False

14:             btnPrevious.Enabled = False

15:             btnLast.Enabled = False

16:             btnAdd.Enabled = False

17:             btnDelete.Enabled = False

18:             ComboBox3.Enabled = False
 

19:         End If
 
 

20:         If TabControl1.SelectedTab Is TabPage6 Then

21:             btnFirst.Enabled = False

22:             btnNext.Enabled = False

23:             btnPrevious.Enabled = False

24:             btnLast.Enabled = False

25:             btnAdd.Enabled = False

26:             btnDelete.Enabled = False

27:             ComboBox3.Enabled = False
 

28:         End If

29:     Catch ex As Exception

30:         System.Windows.Forms.MessageBox.Show(ex.ToString)

31:     End Try

    End Sub
 
 
 
 
 

    Private Sub dgEmp_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgEmp.DataError

1:      Console.WriteLine(e.Exception.ToString)

    End Sub
 
 
 

End Class

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:jcoehoorn
ID: 20384537
Because the concatentation is done by the sql server, not the local machine.  sql server needs the + syntax.
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 20384548
Also: I learned the & syntax first, but lately I have been favoring + anyway, because it works with both C# and VB so it's easier to remember if you ever have to move back and forth.
0
 
LVL 1

Author Comment

by:mindserve
ID: 20384981
The backend database is Access, not SQL server, but not sure it that would make a difference. So far I can't seem to find out why the + works but not the & with that particular concatenation string other than I am concatenating fields in the database and not really a string. Although to me it looks like it's a string in the code above ( form load event)
This code works:
Me.EmployeeIDTable.Columns.Add("FullName", GetType(String), "LastName + ', ' + FirstName")



0
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 50 total points
ID: 20385339
I just checked, and to my surprise Access itself does support the & syntax.  It's the only database system I know of that allows it.  Unfortunately, that doesn't explain the problem here.

I can still say this:  it's the database that is doing the concatenation here, not visual basic.  The database is it's own language, and neither knows nor cares anything about how visual basic works.

Hmm, that gives me one other thought:  you're likely going through an OLE connection to talk to access.  It might be that OLE doesn't like the & syntax.
0
 
LVL 1

Author Comment

by:mindserve
ID: 20385531
Yes, OLEDB connection. This is an odd occurance. I just don't want to end up with some code that suddenly doesn't want to concatenate and starts to add, although if it is concatenating the database fields and not a string then it shouldn't be an issue.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now