Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Beginning VB 6
| Search | Today's Posts | Mark Forums Read
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 19th, 2006, 12:30 PM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data type mismatch -- Combo box bound to database

Hi,

I have a combo box that I've bound to a field in my database. The field is an autonumber. I want to show the corresponding record associated with the number when it is selected in the combo box. I want The corresponding records to appear in textboxes automatically...

I get a Data type mismatch:

Private Sub Combo1_GotFocus()
   cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist Security Info=Fals"
   cn.Open


   rs.Open "SELECT * FROM tblMaterial WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", cn, adOpenDynamic, adLockOptimistic

    With orders
        .qty1 = rs.Fields(1)
        .desc1 = rs.Fields(2)
        .price1 = rs.Fields(3)
    End With

    rs.Close

I dont know if the str(combo1.txt) in my query is allowed at all. But the query highlights as the error when I try to select an item in my combo box. Also, I'm unsure wether I should use combo1_Change or the one above which is combo1_Getfocus functions for me to get the effect that I want.

my combo1 is bound to the column MaterialId in the table tblMaterial. It is an autonumber in my access database. I'm just a newbie at this and I really dont know if this is the right approach. what am I doing wrong here?

Thanks in advance
:)

  #2 (permalink)  
Old August 21st, 2006, 02:50 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

If you use [cod e] (but without the space I included) and its [/cod e] counterpart, your code will be easier to read... 9Makes it fixed pitch)
Code:
Private Sub Combo1_GotFocus()

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & Path & ";" & _
                          "Persist Security Info=Fals"
    cn.Open


    rs.Open "SELECT * " & _
            "FROM   tblMaterial " & _
            "WHERE  MaterialID = '" & Str(Combo1.Text) & "' ;", _
            cn, adOpenDynamic, adLockOptimistic

    With orders
        .qty1 = rs.Fields(1)
        .desc1 = rs.Fields(2)
        .price1 = rs.Fields(3)
    End With

    rs.Close
    I think the problem is in your SQL. IDs (like MaterialID) are usually numeric. You encapsulated the results of Str(Combo1.Text) in quotes (shown just below in bold, underlined red):
Code:
    rs.Open "SELECT * " & _
Code:
            "FROM   tblMaterial " & _
            "WHERE  MaterialID = [u]'</u>" & Str(Combo1.Text) & "[u]'</u> ;", _
            cn, adOpenDynamic, adLockOptimistic
            That compares the quoted literal string with a number, a type mismatch.
  #3 (permalink)  
Old September 1st, 2006, 08:36 PM
Authorized User
 
Join Date: Aug 2006
Location: san fernando, la union, , Philippines.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

change this code:
rs.Open "SELECT * " & _
            "FROM tblMaterial " & _
            "WHERE MaterialID = '" & Str(Combo1.Text) & "' ;", _
            cn, adOpenDynamic, adLockOptimistic
to:
rs.Open "SELECT * " & _
            "FROM tblMaterial " & _
            "WHERE MaterialID = " & clng(Combo1.Text), _
            cn, adOpenDynamic, adLockOptimistic

Note: An auto number is a long integer if you are using Acccess; a BigInt if you are using SQL Server.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding data in a combo box from the database Gini Visual Studio 2008 0 June 20th, 2008 02:17 AM
Combo Box Show all bound columns Coby Excel VBA 2 October 22nd, 2007 02:24 PM
Data type mismatch sharon5469 ASP.NET 1.0 and 1.1 Basics 2 May 27th, 2007 10:01 AM
data type mismatch karma SQL Language 3 April 6th, 2006 07:58 AM
Getting Data Type mismatch for autonumber somissac General .NET 4 March 1st, 2006 12:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.