By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

select entire sql table but display specific cell with WHERE or LIKE condition

100+
P: 120
im able to select & display all table data from sql database on to a webpage.
but when I use the response.write method is it possible to output a specific cell with a WHERE or LIKE condition?

currently im pulling out a specific cell using <% Response.Write objRS("Date_Band_1") %> but is it possible to do something like

<% Response.Write objRS("Date_Band_1" WHERE ID="1" ) %> ??

thanks in advance
Omar.
Mar 17 '10 #1

✓ answered by Soniad

Hi,

You dont need to open connection again and again .If Your ResultSet is more than 1 record then Use while loop to loop through records and display it on page.And I think when you are checking the condition "ID = 1" in SQL , Then obviously it will return only those row(s) whose ID is 1 ,Hence you don't need to check again in VBScript "if rs("id") = 1 then".

Regards,

"D"

Share this Question
Share on Google+
5 Replies


Soniad
P: 66
Hi,

This is not possible.If u want to retrieve specific rows then use where condition in sql statement.
Can you specify what exactly you want to display on webpage. Means All data or specific data?

Regards,

"D"
Mar 17 '10 #2

100+
P: 120
hey soniad : )

oh i see - thats a shame. this would mean me requesting each row's data turn by turn in individual sql statements - am i right?

is there any negative impact caused by more than 1 connection / request in the same asp page? as I need to display data from 38 rows

omar.
Mar 17 '10 #3

100+
P: 120
I've found some more info on this matter which may help someone in the future

To only show records where id is 1 [or with a condition] using SQL:

Expand|Select|Wrap|Line Numbers
  1. sql= "select * from your_table_name where ID= 1"
  2.  
To manipulate a returned recordset:

Expand|Select|Wrap|Line Numbers
  1. if rs("id") = 1 then
  2. response.write something
  3. end if
  4.  
I have a sql db table with 38 rows of data - and I would like to dispay each row on a seperate html table using the manipulated recordset method

so far I've been able to select all from the table and use the manipulated recordset to display the first row. But I'm unable to display the second row? do I have to open up a new connection and query the database each time to display the second row and so forth? so I have to open and close the connection to the database 38 times?

this seems a bit long winded as I have to redeclare & rename variables 38 times.

thanks in advance
Omar.
Mar 18 '10 #4

Soniad
P: 66
Hi,

You dont need to open connection again and again .If Your ResultSet is more than 1 record then Use while loop to loop through records and display it on page.And I think when you are checking the condition "ID = 1" in SQL , Then obviously it will return only those row(s) whose ID is 1 ,Hence you don't need to check again in VBScript "if rs("id") = 1 then".

Regards,

"D"
Mar 18 '10 #5

100+
P: 120
thank you very much for the for loop idea. each record is dynamically creating itself within a table now.

my further question is - I'm trying to now allow each price/date to be updateable via a css popup box which then updates the record.

iv already got this working but I can only make 1 price/date box work - I have 38 price/box's and I can't get my head around how to make this process dynamic rather than the unneccesary 38 connection strings;

below is my code

Expand|Select|Wrap|Line Numbers
  1. <div id='PopUpLGWYYZ' style='display: none; position: absolute; left: 450px; top: 50px; border: solid black 1px; padding: 10px; background-color: rgb(255,255,225); text-align: justify; font-size: 12px; width: 210px;'>
  2. <form name="UKSpecialsUpdate" id="UKSpecialsUpdate" method="post" action="update.asp" />
  3. <table align="center" width="100%" cellpadding="0" cellspacing="0" style="margin:0">
  4.   <tr>
  5.     <td width="30%">Price</td>
  6.     <td>Outbound Date/s</td>
  7.   </tr>
  8.   <tr>
  9.     <td><input name="ID1PriceBand1" type="text" id="ID1PriceBand1" size="4" maxlength="4" value=""></td>
  10.     <td><input name="ID1DateBand1" type="text" id="ID1DateBand1" size="20" maxlength="100" value=""></td>
  11.   </tr>
  12.   <tr>
  13.     <td><input name="ID1PriceBand2" type="text" id="ID1PriceBand2" size="4" maxlength="4" value=""></td>
  14.     <td><input name="ID1DateBand2" type="text" id="ID1DateBand2" size="20" maxlength="100" value=""></td>
  15.   </tr>
  16.   <tr>
  17.     <td><input name="ID1PriceBand3" type="text" id="ID1PriceBand3" size="4" maxlength="4" value=""></td>
  18.     <td><input name="ID1DateBand3" type="text" id="ID1DateBand3" size="20" maxlength="100" value=""></td>
  19.   </tr>
  20.   <tr>
  21.     <td><input name="ID1PriceBand4" type="text" id="ID1PriceBand4" size="4" maxlength="4" value=""></td>
  22.     <td><input name="ID1DateBand4" type="text" id="ID1DateBand4" size="20" maxlength="100" value=""></td>
  23.   </tr>
  24.   <tr>
  25.     <td valign="middle"><div style='text-align: left;'><a onmouseover='this.style.cursor="pointer" ' style='font-size: 12px;' onfocus='this.blur();' onClick="document.getElementById('PopUpLGWYYZ').style.display = 'none' " ><span style="text-decoration: underline;">Close</span></a></div></td>
  26.     <td style="padding:20px 0 0 0" align="right"><input name="LGWYYZ" id="LGWYYZ" type="submit" value="Update!!"/></td>
  27.   </tr>
  28. </table>
  29. </form>
  30. </div>
  31.  
Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. If Request.Form("LGWYYZ") <> "" Then 
  4.  
  5. 'declare your variables
  6. Dim connection, sSQL, sConnString
  7. Price_Band_1=Request.Form("ID1PriceBand1")
  8. Date_Band_1=Request.Form("ID1DateBand1")
  9. Price_Band_2=Request.Form("ID1PriceBand2")
  10. Date_Band_2=Request.Form("ID1DateBand2")
  11. Price_Band_3=Request.Form("ID1PriceBand3")
  12. Date_Band_3=Request.Form("ID1DateBand3")
  13. Price_Band_4=Request.Form("ID1PriceBand4")
  14. Date_Band_4=Request.Form("ID1DateBand4")
  15.  
  16. 'declare SQL statement that will query the database
  17. sSQL="UPDATE MYTABLENAME SET Price_Band_1='" & Price_Band_1 & "', Date_Band_1='" & Date_Band_1 & "', Price_Band_2='" & Price_Band_2 & "', Date_Band_2='" & Date_Band_2 & "', Price_Band_3='" & Price_Band_3 & "', Date_Band_3='" & Date_Band_3 & "', Price_Band_4='" & Price_Band_4 & "', Date_Band_4='" & Date_Band_4 & "'  WHERE ID='1'"
  18.  
  19. 'create an ADO connection object
  20. Set connection = Server.CreateObject("ADODB.connection")
  21.  
  22. 'define the connection string, specify database
  23. 'driver and the location of database
  24. sConnString="myconnectionstringhere"
  25.  
  26. 'Open the connection to the database
  27. Connection.Open sConnString
  28.  
  29. 'Execute the SQL statement
  30. Connection.Execute sSQL
  31.  
  32. 'Now close the connection object
  33. connection.Close
  34. Set connection = Nothing
  35.  
  36. Response.write "Prices / Dates Updated! - Redirecting you back"
  37.  
  38. Else 
  39. Response.Write "Error! Nothing to Update!"
  40. END IF
  41. %>
  42.  
many thanks in advance
Omar.
Mar 19 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.