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

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

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.

Does it give you the expression?

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.

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

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)

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?

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?

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?

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

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

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

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.

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.

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

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

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

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

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

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

commented: if they would just read what we post. +0

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.