Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot retrieve data from the database Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 5:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 8:57 AM
Points: 5, Visits: 5
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.
Post #1388905
Posted Monday, November 26, 2012 8:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 1:56 PM
Points: 1,324, Visits: 530
Is there more than one result that satisfies your where clause?
Post #1388916
Posted Monday, November 26, 2012 8:52 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 1:56 PM
Points: 1,324, Visits: 530
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.
Post #1388917
Posted Monday, November 26, 2012 9:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388921
Posted Monday, November 26, 2012 9:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 8:57 AM
Points: 5, Visits: 5
There is only one result in the table: Mountain View, CA
Post #1388922
Posted Monday, November 26, 2012 9:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 8:57 AM
Points: 5, Visits: 5
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.
Post #1388926
Posted Monday, November 26, 2012 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 8:57 AM
Points: 5, Visits: 5
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?
Post #1388928
Posted Tuesday, November 27, 2012 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 8:57 AM
Points: 5, Visits: 5
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.
Post #1389190
Posted Tuesday, November 27, 2012 8:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1389235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse