fadiel ras
asked on
combo box display member not working
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:
The datagrid is loaded like this:
my problem is that the cbJob.DisplayMember works 100% when I do this:
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:
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.
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
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 "
my problem is that the cbJob.DisplayMember works 100% when I do this:
cbJob.DisplayMember = "JobID"
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"
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.
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.
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.
ASKER
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
ASKER
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
if you do think of something else, please let me know, thank you
>>then it errors out
Do you get an error message? If yes then what is it saying
Do you get an error message? If yes then what is it saying
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
>>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
ASKER
Hi Shaun, unfortunately no luck. when I use the .SelectValue, I get this error:
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
{"Conversion from string "" to type 'Double' is not valid."}
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
ASKER
The JOIN also doesn't work. Please review my code below:
The Error
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 "
The Error
{"Conversion failed when converting the varchar value 'CREDIT CONTROLLER' to data type int."}
ASKER
found it. SHaun you idea solved it, thanks man!!!!!
the correct search code is:
the full code for reference purose is corrected below:
the correct search code is:
If cbJob.Text <> "" Then
strSQL = strSQL & " And tblJob .Description = '" & Trim(UCase(Replace(cbJob.Text, "'", ""))) & "'"
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 "
ASKER
Thanks Gents!
Open in new window
you might need this modification if the ORDER BY clause gives a problem with the other change
Open in new window
[/code]