Cannot retrieve data from the database

  • Here is the code I am using to retrieve data that I have seen on the database:

    <!-- #INCLUDE FILE="sqlconnect.asp" -->

    <%

    Dim sqlString, cityState, storeTime, RStimes

    cityState = "Mountain View, CA"

    storeTime = 9

    sqlString = "SELECT city_time FROM citytime WHERE city_state='" & cityState & "'"

    SET RStimes = Con.Execute( sqlString )

    IF NOT RStimes.EOF THEN

    storeTime = RStimes("city_time")

    END IF

    Response.Write sqlString & vbCrLf

    Response.Write "storeTime=" & storeTime & vbCrLf

    Response.End

    %>

    Here are the results of the write statements:

    SELECT city_time FROM citytime WHERE city_state='Mountain View, CA'

    storeTime=9

    I know that city_state contains Mountain View, CA and that city_time contains 0. Why am I getting city_time 9 ? I have reviewed the SQL query many times and I'm not seeing any errors.

  • Is there more than one result that satisfies your where clause?

  • Can you explain what the intention of this piece of code is?

    IF NOT RStimes.EOF THEN

    storeTime = RStimes("city_time")

    END IF

    It would seem that the predicate is failing here and your store_time is never changing from the 9 you have set it to at the beginning.

  • What do you get for the following???

    SELECT *

    FROM citytime

    WHERE city_state='Mountain View, CA'

    ;

    If the answer is "more than 1 row", then you've probably identified the problem. Look at the data and see if your assumptios about what the citytime should be are actually correct.

    As a sidebar, the code you posted is VERY suseptible to SQL INJECTION and should be changed to parameterized embedded SQL instead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There is only one result in the table: Mountain View, CA

  • This code sets the storeTime from the table only if the city_state = Mountain View, CA

    Otherwise, the storeTime is set to 9 if the cityState value is not found in the table. This is just an example of the code I am using that shows the problem.

  • I have used the literal 'Mountain View, CA' and still receive a storeTime = 9.

    There is only one instance of it. Can you give me an example of the second line of your response?

  • I found an extra space after the , and before the state in the city_state column. Problem solved and all is well. Thanks Jeff for taking the time and for your comments.

  • rich_a_wms (11/27/2012)


    I found an extra space after the , and before the state in the city_state column. Problem solved and all is well. Thanks Jeff for taking the time and for your comments.

    Two things.

    1) The solution to your problem is a good example of why you should not store city and state in the same field.

    2) Your classic asp appears to be the beginning of a page that will use user input for city_state? The way you have this coded is wide open to sql injection attack. You should NEVER NEVER NEVER execute inputs from the user, especially on a website!!! You need to parameterize your queries, or even better, use stored procs to retrieve your data.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply