Combobox ValueMember and SelectedValue error in datatype mismatch

[beginner_emman]

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...

[john.knapp]

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

[beginner_emman]

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.

[john.knapp]

Does it give you the expression?

[john.knapp]

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.

[beginner_emman]

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...

[john.knapp]

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)

[beginner_emman]

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?

[john.knapp]

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?

[beginner_emman]

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?

[john.knapp]

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

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

[beginner_emman]

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..

[john.knapp]

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.

[TnTinMN]

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.

[beginner_emman]

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,....

[beginner_emman]

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..

[beginner_emman]

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..

[john.knapp]

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

[john.knapp]

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

[TnTinMN]

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

[john.knapp]

Definitely the query string. I didn't rewrite the whole project though... :)

[Reverend Jim]

For future reference, it is not necessary to post files on RapidShare or any other external website. Files can be attached directly to posts here.