Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: Error converting data type varchar to int.

    hi alll, hope you can help.

    got this error:
    Microsoft OLE DB Provider for SQL Server (0x80040E07)
    Error converting data type varchar to int.
    /admin/stores/index.asp, line 520

    but no idea what to do? what it is?

    line 520 is below:

    Code:
    Sub UpdateZoneDisplay()
        sSQL = "EXEC spUpdateZones " & iStoreID & ",'" & sfk_zoneID & "," & sfk_storeID & "'"
        SET oRS = oDB.execute(sSQL)
        istoreID = oRS("storeID")
    End Sub
    any help would be brill

    kind regards
    MG

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    apparently you must be passing a non-numeric value. Looks like the line you provided is calling an SP, what data are you passing into and what is being done with it?
    Dave

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The code itself is subject to SQL Injection attacks.

    As to your immediate problem, the stored procedure hits an error converting a varchar value to an integer. No RS object is returned, and you get an error at the line you check the (non-existent) Recordset Object.

  4. #4
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi thanks for the reply,

    It is calling a SP, the SP is the following but im not sure if its correct.

    ALTER PROCEDURE [dbo].[spUpdateZones]
    (
    @iStoreID DECIMAL,
    @sfk_storeID INT,
    @sfk_zoneID INT
    )
    AS
    SET NOCOUNT ON
    BEGIN
    IF EXISTS(SELECT fk_storeID FROM tblstores2zones WHERE fk_storeID = @sfk_storeID )
    BEGIN
    UPDATE tblStores2Zones
    SET fk_storeID = @sfk_storeID,
    fk_zoneID = @sfk_zoneID
    WHERE fk_storeID = @sfk_StoreID
    END

    END
    SET NOCOUNT OFF

    The values in the database are all numeric values, if that what you mean?

    Quote Originally Posted by dav1mo
    apparently you must be passing a non-numeric value. Looks like the line you provided is calling an SP, what data are you passing into and what is being done with it?
    Dave

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Thank you for the detail, how does one go about sorting it? I dont have any varchars, they are all ints?

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Thank you for the detail, how does one go about sorting it? I dont have any varchars, they are all ints?

    Quote Originally Posted by MCrowley
    The code itself is subject to SQL Injection attacks.

    As to your immediate problem, the stored procedure hits an error converting a varchar value to an integer. No RS object is returned, and you get an error at the line you check the (non-existent) Recordset Object.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Most likely either sfk_zoneID or sfk_storeID is not really an integer. They may have whitespace included.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your procedure accepts 3 parameters, 1 decimal and 2 integer variables, but you are passing only 2. The first one is fine, but the second one is the problem:
    '" & sfk_zoneID & "," & sfk_storeID & "'

    The call you're making will look like this:

    EXEC spUpdateZones 1,'2,3'

    And the second parameter ('2,3') is not an integer.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup - agreed. Also, quite a lot of unneeded stuff in there....
    Code:
    ALTER PROCEDURE [dbo].[spUpdateZones]
        (
            @sfk_storeID INT,
            @sfk_zoneID INT
        )
    AS
    
    SET NOCOUNT ON
    
    UPDATE  tblStores2Zones
    SET     fk_storeID = @sfk_storeID,
            fk_zoneID = @sfk_zoneID
    WHERE   fk_storeID = @sfk_StoreID
    
    SET NOCOUNT OFF
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...and you're virtually updating fk_StoreID to itself, which is definitely an unnecessary step, and can be harmful to performance.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Code:
    UPDATE  tblStores2Zones
    SET     fk_storeID = @sfk_storeID,
            fk_zoneID = @sfk_zoneID
    WHERE   fk_storeID = @sfk_StoreID
    why do you need to set something equal to itself?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Correct - I was editing the OP's SQL and missed that.
    Code:
    ALTER PROCEDURE [dbo].[spUpdateZones]
        (
            @sfk_storeID INT,
            @sfk_zoneID INT
        )
    AS
    
    SET NOCOUNT ON
    
    UPDATE  tblStores2Zones
    SET     fk_zoneID = @sfk_zoneID
    WHERE   fk_storeID = @sfk_StoreID
    
    SET NOCOUNT OFF
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all, so many replies, thank you.

    I need to work through them and see if i can suss it.

    I'm new to T-SQL, stored procedures and SQL serevr etc. so please forgive the lack of knowledge and terminology.

    I will take all suggestions on board and check back once things have been implemented, by way of keeping you in the loop. thank you once again.

    Regards
    MG

Posting Permissions

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