Professional Web Applications Themes

the quote are killing me. Can anyone see the problem??? - Microsoft SQL / MS SQL Server

CREATE PROCEDURE GetAvailSales ( theSearchVal nvarchar (100), theSearchBy nvarchar (20), theCompanyList nvarchar (50), theRecordsCount int, theSalesDateFrom SmallDateTime, theSalesDateTo SmallDateTime, SelSort nvarchar(50), SelSortDir nvarchar(10) ) AS SET NOCOUNT ON; DECLARE SQLString varchar(1050) SET SQLString = 'SELECT tempSales.tempSalesID, Contacts.FIRST_NAME + '' '' + Contacts.LAST_NAME AS theName, company.COMPANY, Users.First_Name + '' '' + Users.Last_Name AS userName, tempSales.SalesDate, tempSales.SalesDateThrough FROM tempSales INNER JOIN Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID INNER JOIN company ON Contacts.COMPANY_ID = company.COMPANY_ID INNER JOIN Users ON tempSales.sentByID = Users.UserID WHERE (tempSales.SalesActive = 1)' IF theSearchBy <>'0' SET SQLString = SQLString + ' AND' + theSearchBy + ' like ''' + theSearchVal ...

  1. #1

    Default the quote are killing me. Can anyone see the problem???

    CREATE PROCEDURE GetAvailSales
    (
    theSearchVal nvarchar (100),
    theSearchBy nvarchar (20),
    theCompanyList nvarchar (50),
    theRecordsCount int,
    theSalesDateFrom SmallDateTime,
    theSalesDateTo SmallDateTime,
    SelSort nvarchar(50),
    SelSortDir nvarchar(10)

    )
    AS
    SET NOCOUNT ON;
    DECLARE SQLString varchar(1050)
    SET SQLString = 'SELECT
    tempSales.tempSalesID,
    Contacts.FIRST_NAME + '' '' + Contacts.LAST_NAME AS theName,
    company.COMPANY,
    Users.First_Name + '' '' + Users.Last_Name AS userName,
    tempSales.SalesDate,
    tempSales.SalesDateThrough
    FROM
    tempSales
    INNER JOIN
    Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
    INNER JOIN
    company ON Contacts.COMPANY_ID = company.COMPANY_ID
    INNER JOIN
    Users ON tempSales.sentByID = Users.UserID
    WHERE
    (tempSales.SalesActive = 1)'


    IF theSearchBy <>'0'
    SET SQLString = SQLString + '
    AND' + theSearchBy + ' like ''' + theSearchVal + '%'''

    IF theCompanyList <> 'A0000'
    SET SQLString = SQLString + '
    AND company.COMPANY_ID = ''' + theCompanyList + ''''
    BEGIN
    if theRecordsCount = 0

    if theSalesDateFrom <> ''''
    SET SQLString = SQLString + '
    AND (tempSales.SalesDate >= ''' + theSalesDateFrom + '''
    00:00:00 AM''')'

    if theSalesDateTo <>'''''
    SET SQLString = SQLString + '
    AND (tempSales.SalesDate >= ''' + theCompanyList + '''
    11:59:59 PM''')'

    END
    BEGIN
    if theRecordsCount <> 0
    SET SQLString = SQLString + '
    AND (tempSales.SalesDate >= GETDATE() - 4)
    END

    IF SelSort = ''
    SET SQLString = SQLString + '
    ORDER BY tempSales.SalesDate'

    IF SelSort <> ''
    SET SQLString = SQLString + '
    order by ' + SelSort + ''

    IF SelSortDir <> ''
    SET SQLString = SQLString + '
    ' + SelSortDir + ''

    print SQLString
    EXEC (SQLString)
    GO

    --

    ______________________
    David Fetrow
    HelixPoint LLC.
    http://www.helixpoint.com
    com

    Interested in Affordable Email Marketing?
    Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
    If you are interested in becoming a Reseller of HelixPoint products, contact
    com
    ______________________


    Davef Guest

  2. #2

    Default Re: the quote are killing me. Can anyone see the problem???

    You've got a Print statement in there - show us the result of the Print.
    Davef wrote: 


    Bob Guest

  3. #3

    Default Re: the quote are killing me. Can anyone see the problem???

    It won't pass the syntax



    "Bob Barrows" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Davef Guest

  4. #4

    Default Re: the quote are killing me. Can anyone see the problem???

    Oh! My bad. Sorry.

    As Amy points out, double up the single quotes (i.e., use two single quotes
    instead of one) to force the quote to be used literally.This is called
    "escaping" the quote.

    When I see this:
    Contacts.FIRST_NAME + '' '' + Contacts.LAST_NAME

    I can't be sure if you are using 2 quotation marks (double-quotes) or 4
    single quotes.

    You will need to use REPLACE with your parameters to escape any literal
    quotes that may appear in them as well. This:
    AND' + theSearchBy
    should be this:
    AND' + REPLACE(theSearchBy,'''','''''')

    Those are all single quotes in the above statement.

    <sarcasm>Isn't dynamic sql fun? </sarcasm>

    Bob Barrows

    Davef wrote: [/ref][/ref]


    Bob Guest

  5. #5

    Default Re: the quote are killing me. Can anyone see the problem???

    > Contacts.FIRST_NAME + '' '' + Contacts.LAST_NAME AS theName,

    Try:

    Contacts.FIRST_NAME + ' ' + Contacts.LAST_NAME AS theName

    or

    Contacts.FIRST_NAME + SPACE(1) + Contacts.LAST_NAME AS theName
     

    There needs to be a space after AND. Maybe you should try building these
    SQL statements in ASP first, then you can paste it into QA and test it,
    without needing the proc to satisfy syntax checks.


    Aaron Guest

  6. #6

    Default Re: the quote are killing me. Can anyone see the problem???

    When you want the string to contain a ' charecter, Use two of them:

    DECLARE str VARCHAR(50)
    SET str = 'here is a '' and here is another '''
    SELECT str

    HTH

    "Davef" <com> wrote in message
    news:%phx.gbl... 
    contact 


    Amy Guest

Similar Threads

  1. Single Quote Problem in Excel to Export
    By Prasad Dannani in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: October 27th, 12:12 AM
  2. Single quote problem
    By rockinrobin06 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: August 25th, 09:28 PM
  3. double single quote problem in cfquery
    By Bigio in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 25th, 09:46 PM
  4. Single quote (') problem in SQL Server query
    By Ben in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 09:14 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139