SQL Error-VBForums
Results 1 to 6 of 6

Thread: SQL Error

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2007
    Location
    Newport, South Wales
    Posts
    88

    SQL Error

    Hey All,
    Im not quite sure where to post this error, as I am trying to write a stored procedure to be used in a asp.net application, but I cant seem to get the SQL right:

    Code:
    select cast('<Page><Id>' As varchar(266)) + cast(pa_pageid AS int) + '</Id><Path>' + pa_path + '</Path><Replace>' + pa_replace + '</Replace><Scrape>' + pa_scrape + '</Scrape><Select>' + pa_selectorid + '</Select>
    	<Sel-pos>' + pa_selpos + '</Sel-pos><Sel-tag>' + pa_seltag + '</Sel-tag><Regex>' + pa_regex + '</Regex></Page>' from WST_Page
    Thats the query I am running, and I get this error:

    Code:
    Conversion failed when converting the varchar value '<Page><Id>' to data type int.
    Could anyone point me in the right direction?

    Cheers,
    Andrew
    Andrew

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,112

    Re: SQL Error

    it's this bit:
    + cast(pa_pageid AS int) +

    presumably pageid is an integer to being with so there's no reason to cast it as one... but even still you don't want it as an integer, but as a string... SQL sees one of the items to add as a number and expects the whole expression to be a number. When it cannot convert the string it throws up its hands, barfs and spits out the error.

    -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??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2007
    Location
    Newport, South Wales
    Posts
    88

    Re: SQL Error

    Thanks for the quick reply. The reason for the cast() is because I was trying hopelessly to fix the error I get the same error with the code like this:

    Code:
    select '<Page><Id>' + pa_pageid + '</Id><Path>' + pa_path + '</Path><Replace>' + pa_replace + '</Replace><Scrape>' + pa_scrape + '</Scrape><Select>' + pa_selectorid + '</Select>
    	<Sel-pos>' + pa_selpos + '</Sel-pos><Sel-tag>' + pa_seltag + '</Sel-tag><Regex>' + pa_regex + '</Regex></Page>' from WST_Page
    The cast were trying to make sure the system knew what each element was to stop it from throwing the error.
    Andrew

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,112

    Re: SQL Error

    Right... you've got some elements, at least one, that's a nu,mber, and you're trying to put it into a string... so you need to CAST IT AS AS A STRING... pa_pageid is a number... but what you're returning is a string... so you can't leave it as a number... you need to cast it to a string. Anything tha'ts a number needs to be cast or converted to a string before it can be concatenated to a string.


    -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??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2007
    Location
    Newport, South Wales
    Posts
    88

    Re: SQL Error

    Thanks, I follow you now, I cast it as a string and I get a different error now, which I was mistaking for the same error! Thanks for your help!
    Andrew

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,112

    Re: SQL Error

    Bottom line, anything that isn't astring needs to be cast as one in order for it to be concatenated into a larger string.

    -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??? *

Tags for this Thread

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

Survey posted by VBForums.