Solved

combo box display member not working

Posted on 2016-10-25
11
47 Views
Last Modified: 2016-10-25
Let me try 3 from 3 for EE:)

I'm getting an error when trying to load a dataGridView, through a Search funtion, from a combobox.
The combo box is loaded from a table like this:

Private Sub MySearches()
        Dim strSql As String = ""
        Dim dsData As DataSet
        cbJob.DataSource = Nothing
        strSql = " SELECT JobID, Active, Description "
        strSql = strSql & " FROM tblJob"
        strSql = strSql & " WHERE Active = 'Y'"
        strSql = strSql & " ORDER BY Description "
        dsData = SelectDataSet(strSql)
        cbJob.DataSource = dsData.Tables(0)
        cbJob.DisplayMember = "Description"
        cbJob.ValueMember = "JobID"
    End Sub

Open in new window


The datagrid is loaded like this:
Sub Load_Data()

        Dim strSQL As String = ""
        Dim dsData As DataSet

        Try
            strSQL = " SELECT DISTINCT ExampleID, SurName, JobID "
            strSQL = strSQL & " ,'N' RowModified "
            strSQL = strSQL & " From tblExample "
            strSQL = strSQL & " Where ExampleID = '1' " 
            If txtName.Text <> "" Then
                strSQL = strSQL & " And SurName = '" & Trim(UCase(Replace(txtSurName.Text, "'", ""))) & "'"
            End If
            If cbDesignation.Text <> "" Then
                strSQL = strSQL & " And JobID = '" & Trim(UCase(Replace(cbJob.Text, "'", ""))) & "'"
            End If
            strSQL = strSQL & " ORDER BY SurName, JobID ASC "

Open in new window


my problem is that the cbJob.DisplayMember works 100% when I do this:
       
cbJob.DisplayMember = "JobID"

Open in new window


as soon as I change it to diplay the Job description, then it errors out and cannot load the datagridview
The code that does not work:
cbJob.DisplayMember = "Description"

Open in new window

I obviously have some calsses etc going, but the just of the issue should be contained in the code I managed to post.
So to summarize:
I need to be able to SEARCH using the Combox cbJob, not on JobID but rather using the Description field from the same table feeding the cbJob, the JobID works fine, description does not.
0
Comment
Question by:fadiel ras
  • 7
  • 2
  • 2
11 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41858716
I've seen odd behaviour when one uses a reserved keyword.  This is a guess but does this make any difference?

Private Sub MySearches()
        Dim strSql As String = ""
        Dim dsData As DataSet
        cbJob.DataSource = Nothing
        strSql = " SELECT JobID, Active, Description as Descr "
        strSql = strSql & " FROM tblJob"
        strSql = strSql & " WHERE Active = 'Y'"
        strSql = strSql & " ORDER BY Description "
        dsData = SelectDataSet(strSql)
        cbJob.DataSource = dsData.Tables(0)
        cbJob.DisplayMember = "Descr"
        cbJob.ValueMember = "JobID"
    End Sub

Open in new window



you might need this modification if the ORDER BY clause gives a problem with the other change
[code]Private Sub MySearches()
        Dim strSql As String = ""
        Dim dsData As DataSet
        cbJob.DataSource = Nothing
        strSql = " SELECT JobID, Active, Description as Descr "
        strSql = strSql & " FROM tblJob"
        strSql = strSql & " WHERE Active = 'Y'"
        strSql = strSql & " ORDER BY Descr "
        dsData = SelectDataSet(strSql)
        cbJob.DataSource = dsData.Tables(0)
        cbJob.DisplayMember = "Descr"
        cbJob.ValueMember = "JobID"
    End Sub

Open in new window

[/code]
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41858724
One observation:
Line 14 in the code you posted is comparing cbDesignation.Text in the IF statement, but your SQL on line 15 is using cbJob.Text.

In your SQL, instead of using cbJob.Text on line 15, you can use cbJob.SelectedValue in that line of SQL.
0
 

Author Comment

by:fadiel ras
ID: 41858806
thanks Andy, I'll try it. Shaun, sorry man, I changed the naming of some for purpose of the public forum, will update it, thanks for spotting
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:fadiel ras
ID: 41858828
Hi AndyAinscow, I tried yours, with no luck unfortuantely. the form loads with the Descriptions, but still errors on the search (tried both)
if you do think of something else, please let me know, thank you
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41858835
>>then it errors out

Do you get an error message?  If yes then what is it saying
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41858857
Try using the cbJob.SelectedValue in your SQL instead of cbJob.Text.

If you wish to use cbJob.Text, you will need to modify your SQL in your search to include a join to your tblJob table to allow searching on the description in that table.
0
 

Author Comment

by:fadiel ras
ID: 41858872
thanks. let me try.
>>Do you get an error message?  If yes then what is it saying
 {"Conversion failed when converting the varchar value 'CREDIT CONTROLLER' to data type int."}

'CREDIT CONTROLLER' being the Job Description

the sql code from my vb load when I search is:
( the value member should have passed JobID)
 
 SELECT DISTINCT ExampleID, Surname, JobID,'N' RowModified  
  From tblExample   
  Where ExampleID  = '1'  
  And JobID = 'CREDIT CONTROLLER' 
  ORDER BY Name, JobID ASC

Open in new window

0
 

Author Comment

by:fadiel ras
ID: 41858889
Hi Shaun, unfortunately no luck. when I use the .SelectValue, I get this error:
 {"Conversion from string "" to type 'Double' is not valid."}

Open in new window

pretty much still the same...
I tried the join earlier, but could figure out if I need to now bring the description column in my Load sub?, let me try, then I'll post my new Code
0
 

Author Comment

by:fadiel ras
ID: 41858910
The JOIN also doesn't work. Please review my code below:

Sub Load_Data()
Dim strSQL As String = ""
Dim dsData As DataSet
        Try
            strSQL = " SELECT DISTINCT ExampleID, SurName, tblJob .JobID "
            strSQL = strSQL & " ,'N' RowModified "
            strSQL = strSQL & " From tblExample "
            strSQL = strSQL & " INNER JOIN tblJob "
            strSQL = strSQL & " ON tblExample.JobID = tblJob.JobID   "
            strSQL = strSQL & " Where ExampleID = '1' " 
            If txtName.Text <> "" Then
                strSQL = strSQL & " And SurName = '" & Trim(UCase(Replace(txtSurName.Text, "'", ""))) & "'"
            End If
            If cbJob.Text <> "" Then
                strSQL = strSQL & " And tblJob .JobID = '" & Trim(UCase(Replace(cbJob.Text, "'", ""))) & "'"
            End If
            strSQL = strSQL & " ORDER BY SurName, tblJob .JobID ASC "

Open in new window


The Error
{"Conversion failed when converting the varchar value 'CREDIT CONTROLLER' to data type int."}

Open in new window

0
 

Author Comment

by:fadiel ras
ID: 41858925
found it. SHaun you idea solved it, thanks man!!!!!
the correct search code is:

            If cbJob.Text <> "" Then
                strSQL = strSQL & " And tblJob .Description = '" & Trim(UCase(Replace(cbJob.Text, "'", ""))) & "'"

Open in new window


the full code for reference purose is corrected below:
Sub Load_Data()
Dim strSQL As String = ""
Dim dsData As DataSet
        Try
            strSQL = " SELECT DISTINCT ExampleID, SurName, tblJob .JobID "
            strSQL = strSQL & " ,'N' RowModified "
            strSQL = strSQL & " From tblExample "
            strSQL = strSQL & " INNER JOIN tblJob "
            strSQL = strSQL & " ON tblExample.JobID = tblJob.JobID   "
            strSQL = strSQL & " Where ExampleID = '1' " 
            If txtName.Text <> "" Then
                strSQL = strSQL & " And SurName = '" & Trim(UCase(Replace(txtSurName.Text, "'", ""))) & "'"
            End If
            If cbJob.Text <> "" Then
                strSQL = strSQL & " And tblJob .Description = '" & Trim(UCase(Replace(cbJob.Text, "'", ""))) & "'"
            End If
            strSQL = strSQL & " ORDER BY SurName, tblJob .JobID ASC "

Open in new window

0
 

Author Closing Comment

by:fadiel ras
ID: 41858929
Thanks Gents!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
location of a form 2 25
VB.net Progress Bar - Maximum Value too large 2 16
MailAddress in vb 4 29
VB.NET Application Installation with sqlserver 8 32
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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