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

    Unanswered: Microsoft OLE DB Provider for SQL Server (0x80040E14)

    Morning all, how are you ?

    I had the following error, and at one point seemed to have a vague idea about how to rectify it, but alas no joy. Any help would be very much welcome.

    ERROR:
    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Unclosed quotation mark after the character string ',4'.
    /admin/stores/index.asp, line 401. (code below)

    I have a "Save" button in an ASP page, that when selected should update a table in a database and save the information added.

    Here is the code / line that is being referenced:

    Sub UpdateStoreDetailsRow()
    sSQL = "EXEC storedprocedure_one " & iStoreID & ",'" & sOpensMonday & "'" &_
    ",'" & sClosesMonday & "'" &_
    ",'" & sOpensTuesday & "'" &_
    ",'" & sClosesTuesday & "'" &_
    ",'" & sOpensWednesday & "'" &_
    ",'" & sClosesWednesday & "'" &_
    ",'" & sOpensThursday & "'" &_
    ",'" & sClosesThursday & "'" &_
    ",'" & sOpensFriday & "'" &_
    ",'" & sClosesFriday & "'" &_
    ",'" & sOpensSaturday & "'" &_
    ",'" & sClosesSaturday & "'" &_
    ",'" & sOpensSunday & "'" &_
    ",'" & sClosesSunday & "'" &_
    ",'" & sTelNumber & "'" &_
    ",'" & sFaxNumber & "'" &_
    ",'" & sEmailAddress & "'" &_
    ",'" & sSocialProfileAddress & "'" &_
    ",'" & sPostalAddress & "'" &_
    "," & iWinID & "'"&_
    ","& iFloorID
    oDB.execute(sSQL) (line 401)
    End Sub

    My theory:
    Originally the "storedprocedure_one" (see below) didn't have any reference to "iFloorID", and so, when the Procedures was running through what it had to update, it arrives to the end and has no idea of "iFloorID" it went crazy and errored? However "ifloorId" was added where needed, but still it does not save.

    ALTER PROCEDURE [dbo].[spstoredprocedure_one]
    @iStoreID DECIMAL
    ,@sOpensMonday VARCHAR(8)
    ,@sClosesMonday VARCHAR(8)
    ,@sOpensTuesday VARCHAR(8)
    ,@sClosesTuesday VARCHAR(8)
    ,@sOpensWednesday VARCHAR(8)
    ,@sClosesWednesday VARCHAR(8)
    ,@sOpensThursday VARCHAR(8)
    ,@sClosesThursday VARCHAR(8)
    ,@sOpensFriday VARCHAR(8)
    ,@sClosesFriday VARCHAR(8)
    ,@sOpensSaturday VARCHAR(8)
    ,@sClosesSaturday VARCHAR(8)
    ,@sOpensSunday VARCHAR(8)
    ,@sClosesSunday VARCHAR(8)
    ,@sTelNumber VARCHAR(50)
    ,@sFaxNumber VARCHAR(50)
    ,@sEmailAddress VARCHAR(250)
    ,@sSocialProfileAddress VARCHAR(250)
    ,@sPostalAddress VARCHAR(500)
    ,@iWindowID DECIMAL
    AS
    BEGIN
    SET NOCOUNT ON

    IF EXISTS (SELECT fk_storeID FROM tblname_one WHERE fk_storeID = @iStoreID)
    BEGIN
    UPDATE tbname_one
    SET
    OpensMonday = @sOpensMonday
    ,ClosesMonday = @sClosesMonday
    ,OpensTuesday = @sOpensTuesday
    ,ClosesTuesday = @sClosesTuesday
    ,OpensWednesday = @sOpensWednesday
    ,ClosesWednesday = @sClosesWednesday
    ,OpensThursday = @sOpensThursday
    ,ClosesThursday = @sClosesThursday
    ,OpensFriday = @sOpensFriday
    ,ClosesFriday = @sClosesFriday
    ,OpensSaturday = @sOpensSaturday
    ,ClosesSaturday = @sClosesSaturday
    ,OpensSunday = @sOpensSunday
    ,ClosesSunday = @sClosesSunday
    ,TelNumber = @sTelNumber
    ,FaxNumber = @sFaxNumber
    ,EmailAddress = @sEmailAddress
    ,SocialProfileAddress = @sSocialProfileAddress
    ,PostalAddress = @sPostalAddress
    ,fk_windowID = @iWindowID
    WHERE
    fk_StoreID = @iStoreID

    END
    ELSE
    BEGIN
    INSERT INTO tblname_one (
    fk_StoreID
    ,OpensMonday
    ,ClosesMonday
    ,OpensTuesday
    ,ClosesTuesday
    ,OpensWednesday
    ,ClosesWednesday
    ,OpensThursday
    ,ClosesThursday
    ,OpensFriday
    ,ClosesFriday
    ,OpensSaturday
    ,ClosesSaturday
    ,OpensSunday
    ,ClosesSunday
    ,TelNumber
    ,FaxNumber
    ,EmailAddress
    ,SocialProfileAddress
    ,PostalAddress
    ,fk_windowID
    )
    VALUES (
    @iStoreID
    ,@sOpensMonday
    ,@sClosesMonday
    ,@sOpensTuesday
    ,@sClosesTuesday
    ,@sOpensWednesday
    ,@sClosesWednesday
    ,@sOpensThursday
    ,@sClosesThursday
    ,@sOpensFriday
    ,@sClosesFriday
    ,@sOpensSaturday
    ,@sClosesSaturday
    ,@sOpensSunday
    ,@sClosesSunday
    ,@sTelNumber
    ,@sFaxNumber
    ,@sEmailAddress
    ,@sSocialProfileAddress
    ,@sPostalAddress
    ,@iWindowID
    )
    END
    END

    I thought it was that, but im still getting errors. hope it all makes sense. If not please shout and I can provide further information if required.

    Kind regards
    MG

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Morning - I am splendid thank you.

    The value of sSQL is the important thing here. Can you please print out the value?

    Additional point - you are vulnerable to SQL Injection using this sort of methodology.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    end single quote after iWinID is the problem.

    "," & iWinID & "'"&_

  4. #4
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all, thank you for your input,

    I have since resolved the problem, it was simply a single quote where it should not have been. Please see below for answer, hope this helps people should they have similar problems in future. (highlighted in red). Also, iFloorID was required in the stored procedure, so half right Lol.

    "," & iWinID & "' ' "&_
    ","& iFloorID

    Thank you once again, take care.

    Regards
    MG

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi pootle flump, thanks for your reply.

    im new to this world and dont know what SQL Injection is, but any info would be great to know.

    Kind regards
    MG

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi PMASchmed, thank you for the reply,

    You were right.

    Kind regards
    MG

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    See some basic examples about SQL injection here:
    SQL Injection Attacks by Example
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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