dcsimg
Results 1 to 6 of 6

Thread: Conversion Failed

Hybrid View

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2009
    Posts
    1,058

    Conversion Failed

    Hi,

    I have the foolowing select statement in my query;

    Code:
    SELECT        '-- Please Select --' AS PhysicianID, 0 AS OrderCol1, 0 AS OrderCol2
    UNION ALL
    SELECT        PhysicianID, PracNo, PhysicianID AS Expr1
    FROM            TblPhysicianInfo
    WHERE        (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY OrderCol1, OrderCol2
    PhysicianID is an INT Data type in the database..

    I receive the error message;

    Code:
    Conversion failed when converting the varchar value '-- Please Select -- ' to data type int
    How can I go round this?

    Thanks

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,751

    Re: Conversion Failed

    Shouldn't '-- Please Select --' be replaced with some actual statement??

    What is the first SQL statement before the UNION at all doing in the query?

  3. #3
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,604

    Re: Conversion Failed

    PhysicianID is an Int, and your trying to union that column to "-- Please Select --" which is a string, so you need to convert PhysicianID to a Varchar so they are both of the same type.

    That is your problem, if you union columns together they must be of the same type, so just add another convert statement to your select like this -

    SELECT '-- Please Select --' AS PhysicianID, 0 AS OrderCol1, 0 AS OrderCol2
    UNION ALL
    SELECT CONVERT(PhysicianID,VARCHAR(50)), PracNo, PhysicianID AS Expr1
    FROM TblPhysicianInfo
    WHERE (CONVERT(nvarchar(50), PracNo) = @cmbPracID)
    ORDER BY OrderCol1, OrderCol2
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,751

    Re: Conversion Failed

    Aha, adding a fake first record for a databound control ...

  5. #5
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Conversion Failed

    Several other weird things about this code... Firstly, the names used for your columns / fields don't match... I think It'd be better to use something like:
    Code:
    SELECT '-- Please Select --' AS PhysicianIDText, 0 AS PracNo, 0 AS PhysicianID
    UNION ALL
    SELECT CONVERT(PhysicianID,VARCHAR(50)) AS PhysicianIDText, PracNo, PhysicianID
    The 2nd thing I noticed was the WHERE-clause... If PracNo is a numeric field in the DataBase, why are you converting it to a varchar? I'm not even sure if that clause will work if the Value of what I'm assuming is a ComboBox is also numeric, whcih again I'm assuming. If my 2nd assumption is wrong in that the ValueMember of the ComboBox is not numeric, but is numeric in the database, that just begs the question why you'd set up your ComboBox's Value member to have a different format? The whole purpose for a ComboBox having both a DisplayMember as well as a ValueMember is that you can format the ComboBox's DisplayMember in any way that is easier to read for the user while the ValueMember is kept hidden from the user so that it can match the actual data in the database.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,733

    Re: Conversion Failed

    They don't have to match names... the resultset will return the col names from the first union... as long as the types and number of fields match, that's all SQL cares about.

    as for the different format - it's an effort to get the combo box to display "-- Please Select --" initially when it is bound to the data. I have mixed feelings about it... I used to do this type of stuff in VB6, but since .NET introduces the ErrorNotification component and other elements, I've largely gotten away from it. So PhysicianID is probably a number, but in order to display the -- Please Select -- text, it needs to be converted. As to why PracNo is being cast as a string in the where clause, no clue. If it's a number, it should be a number all the way through. I suspect though that the source of PracNo in the where clause is the same kind of situation where the PracNo combo has the -- Please Select -- which means PracNo was converted to a String on the way out... it doesn't need to remain that way... it should be parsed out, converted BACK to a number THEN passed back to the database and used AS a NUMBER in the SQL.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width