Question combobox issue

[BluRayz]

hi guys, im trying to use the combobox to update/edit/view the saved data (in contact form) from sql server.
my Load_Combobox (in contact form):

Private Sub Load_Combobox()

'Type
cboType.Items.Add("Director")
cboType.Items.Add("Principal")
cboType.Items.Add("Project Manager")
cboType.Items.Add("Technical Manager")

cboType.SelectedIndex = 0

My Public Sub New (in contact form):

Public Sub New(ByVal nDataFlag1 As Integer)
'Set combobox value
Load_Combobox()

End Sub

My Load_Contact (in Contact form):

Private Sub Load_Contact(ByVal nClientID As Integer)
Dim lcSQL As String
Dim lnComboboxIndex As Integer

lcSQL = " SELECT * FROM client WHERE CLIENTID = " & nClientID

frmMain.objDB.QueryRecord(lcSQL)
While frmMain.objDB.oQuery.Read()
If frmMain.objDB.oQuery.HasRows And Not frmMain.objDB.oQuery.IsDBNull(0) Then

txtName.Text = frmMain.objDB.oQuery.GetString(1)
txtFirm.Text = frmMain.objDB.oQuery.GetString(2)
txtAddress.Text = frmMain.objDB.oQuery.GetString(3)
txtFax.Text = frmMain.objDB.oQuery.GetString(4)
txtPhone.Text = frmMain.objDB.oQuery.GetString(5)
txtEmail.Text = frmMain.objDB.oQuery.GetString(6)

lnComboboxIndex = cboType.FindStringExact(frmMain.objDB.oQuery.GetString(7).Trim())
cboType.SelectedIndex = lnComboboxIndex

End If
End While
End Sub

In clsDBConnect.vb:

Public Sub GetContactType()

Dim loDataAdpr As OleDbDataReader
Dim loCmdSQL As OleDbCommand
oContactTypeDt = Nothing
isConnectDB()

Try
loCmdSQL = New OleDbCommand()
loCmdSQL.CommandType = CommandType.Text
loCmdSQL.Connection = oConn_ProjMgt
loCmdSQL.CommandText = " SELECT DISTINCT TYPE FROM CLIENT WHERE TYPE IS NOT NULL ORDER BY TYPE "
loDataAdpr = loCmdSQL.ExecuteReader()
oContactTypeDt = New DataTable()
oContactTypeDt.Load(loDataAdpr)

Catch OleDBExceptionErr As OleDbException
cMsgStatus = OleDBExceptionErr.Message
End Try

End Sub

the problem now is when i saved a record into sql server (EG Technical Manager) and try to view back the saved data, it always show the first item (Director) in combobox. but, my sql server has saved is "Technical Manager" for that saved record.

kindly assist.

---

 

[jmcilhinney]

You can make two significant improvements there.

First, you Client table should not contain text in the Type column. You should have a separate ClientType table that has a ClientTypeID column and a ClientTypeName column and it's that ClientTypeName column that should contain those text values. Your Client table then has a ClientTypeID column as well and you set up a foreign key relationship between those two like-named columns. That way, you get the immediate advantage where records that contain the same ID in that column actually do refer to the same value rather than different values that look the same.

When it comes time to load the data, you populate a DataTable with the data from the ClientType table and bind it to the ComboBox.

With clientTypeComboBox
    .DisplayMember = "ClientTypeName"
    .ValueMember = "ClientTypeID"
    .DataSource = clientTypeDataTable
End With

You then use the SelectValue property of the ComboBox to get and set the ClientTypeID, either manually or by data-binding. When you load a Client record you assign the ClientTypeID to the SelectedValue of the ComboBox and that will display the corresponding ClientTypeName. When the user selects a different name, the corresponding ID can be retrieved from the SelectedValue.

 

[BluRayz]

first at all, because my manager, im not allowed me to change or add any settings in the sql database.
he dont want the "old-fashioned style" to be changed into "new-fashioned style". so i have no choice, i need to follow his style.

if i allowed to change into "new-fashioned style", i already change from the beginning. no need to cracking my head from the beginning until now..

 

[jmcilhinney]

In that case you should just be binding the list of Strings to the ComboBox and then you can simply assign the Type value to or get it from the SelectedItem property of the ComboBox.