1,105,581 Community Members

Combobox ValueMember and SelectedValue error in datatype mismatch

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

hi, can anyone please check why im getting dataype mismatch error... im using combobox and msaccess db which consist of formNo(autonumber) LastName FirstName MiddleName Address (text) ... if i use address as valuemember there is no error,, but if i use FormNo as valuemember then there this datatype mismatch error.. here is my codes..

    Private Sub frmPayment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmbName.Enabled = False

         Try
                'Dim fillcon As New OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path to access file.mdb;")
            Dim asql As String = ("SELECT LastName +', '+ FirstName +'   '+ MiddleName AS FullName,FormNo FROM tbleMember")
                Dim da As New OleDbDataAdapter(asql, MyConn)
                Dim ds As New DataSet
                ds.Clear()

                da.Fill(ds)
                cmbName.DisplayMember = "FullName"
            cmbName.ValueMember = "FormNo"
                cmbName.DataSource = ds.Tables(0)
                cmbName.SelectedIndex = 0
            Catch ex As Exception
                MsgBox("ERROR : " & ex.Message.ToString)
            End Try
    End Sub

    Private Sub cmbName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbName.SelectedIndexChanged


        MyConn.Open()
        Try
            Dim myCommand As OleDbCommand
            myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo='" & cmbName.SelectedValue.toString & "'", MyConn)
            Dim reader As OleDbDataReader = myCommand.ExecuteReader
            While reader.Read()
                txtIDNum.Text = reader("FormNo")
            End While
            reader.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        End sub

im stuck with this..thanks in advance...

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

What is the text (and details, if any) of the error?

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

error said: "Data Type Mismatch criteria in Expression."
i thought it has something to do with the data type of FormNo which has an autonumber, and changed it to number only.but it did not work also.

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Does it give you the expression?

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

In your SQL query, you're passing FormNo as a string, if it's an int value in the DB, that's your problem. See this post for an explanation.

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

yes sir, in DB FormNo is autonumber and im using it to get the unique value of each row in the database has.honestly sir, before i posted my query here, ive seen that post you've linked to me...and i tried it and the error appeared.. no value given for parameters...

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

That's a different error though, right? That error indicates that you are setting up a parameterized query, but not actually passing a parameter to it - do you have code where you assign a value to the parameter?

Back to the original error and query string with the DataType Mismatch - you're using an integer value in your WHERE FormNo = clause, but you're passing a string value.

Instead of: myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo='" & cmbName.SelectedValue.toString & "'", MyConn)

Try this:
myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo=" & cmbName.SelectedValue.ToString, MyConn)

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

i have already tried it sir, but the error is changed to no value given for required parameter,,,i have a question, why is it if im using the address as valuemember and selectedvalue, it runs correctly?

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Please confirm the exact line of code where the exception occurs.

why is it if im using the address as valuemember and selectedvalue, it runs correctly?

Good question. I do not know, as I do not see [address] in your original DataAdapter CommandText. Where does [address] come into play?

Can you execute this query directly? (From Server Explorer, right-click database ->refresh, right-click database -> new query)
SELECT * FROM tbleMember WHERE FormNo = REPLACE_THIS_TEXT_WITH_A_VALID_VALUE

no value given for parameters

This implies a parameterized query (as I stated before) OR a filter parameter on a dataset. I assume you would know if you have a parameterized query, is it possible you have a parameter on one of your datasets?

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

i dont have parameter sir....

Line 6, 13, and 27, by replacing the FormNo by Address, it will run correctly, but given this code i can't use Address as their unique data, because data can have same address that's why i must get the Unique one and that is the FormNo..

i tried your codes, it work out fine,...why is it like that?

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Line 6, 13, and 27, by replacing the FormNo by Address

Where does Address come from? It is not in your original query

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

yes sir, it is not my original query, but FormNo, LastName, FirstName, MiddleName, and Address came from the same table from the database. FormNo is a Autonumber while the rest is text in database... if i use address instead of FormNo in my select query, the program runs perfectly, but the uniqueness issues occur, so i must use the FormNo to get the uniqueness of each data... i still dont know how to use the parameter..

now im searching the net how to get the parameter to what have said in you previous reply, though i want it badly to resolve it maybe the parameter error can solve this, i dont know sir because im eager to learn how this thing work...and thank you for looking my query..

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Can you zip up your project folder and upload it to Rapidshare?
Clean the solution first, please. Post the link at Rapidshare once you have it uploaded.

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

Your code should execute if your change

myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo='" & cmbName.SelectedValue.toString & "'", MyConn)

to

myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo=" & cmbName.SelectedValue.toString , MyConn)

Unless you intend to extract more values than are shown in the SelectedIndexChanged handler, I would suggest that you rethink this code a bit. There is no need for this additional query as you have the valu you are trying to retrieve.

Also, I would also suggest that you change:

Dim asql As String = ("SELECT LastName +', '+ FirstName +'   '+ MiddleName AS FullName,FormNo FROM tbleMember")

to

Dim asql As String = ("SELECT LastName +', '+ FirstName + IIF(MiddleName is Null,'',' ' + MiddleName) AS FullName,FormNo FROM tbleMember")

This will prevent a blank entry in the combobox if there is no MiddleName.

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

sir john.knapp here's link - http://rapidshare.com/files/4059597494/Project.rar

i replaced FormNo by Address on the attached file so that it till run, but if you change it to FormNo, there will an error..thanks for guiding me sir,....

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

sir TnTinMN, im afraid that i dont what ti rethink this codes,bcoz it will lead to a major changes in my program, yes i have there already the data, but in the last query it will determine so many info about each data.

i have tried this query already..

    myCommand = New OleDbCommand("SELECT * FROM tbleMember WHERE FormNo=" & cmbName.SelectedValue.toString , MyConn)

but the error is no value given for one or more required paramter..

Member Avatar
beginner_emman
Newbie Poster
9 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

to sir John.knapp and TnTinMN, sorry for my lack of knoweldge, i beleive experience is the best teacher, soon if i will be good at it, i will be returning favors to the others who might need help too..

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

I have downloaded the project source - but I'll need the database as well, or at least the table structure

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Never mind, found a copy of your DB in the bin folder.

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

Well after realizing that I was using files with Timestamps a day ahead of me, I was able to look at your code and made the changes that we had recommended. It works fine now. I also made a few minor changes to assist you.

Modified project

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: