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

[omar999]

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.

[Soniad]

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"

[omar999]

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.

[omar999]

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:

sql= "select * from your_table_name where ID= 1"
 

To manipulate a returned recordset:

if rs("id") = 1 then
response.write something
end if
 

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.

[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"

[omar999]

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

<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;'>
<form name="UKSpecialsUpdate" id="UKSpecialsUpdate" method="post" action="update.asp" />
<table align="center" width="100%" cellpadding="0" cellspacing="0" style="margin:0">
  <tr>
    <td width="30%">Price</td>
    <td>Outbound Date/s</td>
  </tr>
  <tr>
    <td><input name="ID1PriceBand1" type="text" id="ID1PriceBand1" size="4" maxlength="4" value=""></td>
    <td><input name="ID1DateBand1" type="text" id="ID1DateBand1" size="20" maxlength="100" value=""></td>
  </tr>
  <tr>
    <td><input name="ID1PriceBand2" type="text" id="ID1PriceBand2" size="4" maxlength="4" value=""></td>
    <td><input name="ID1DateBand2" type="text" id="ID1DateBand2" size="20" maxlength="100" value=""></td>
  </tr>
  <tr>
    <td><input name="ID1PriceBand3" type="text" id="ID1PriceBand3" size="4" maxlength="4" value=""></td>
    <td><input name="ID1DateBand3" type="text" id="ID1DateBand3" size="20" maxlength="100" value=""></td>
  </tr>
  <tr>
    <td><input name="ID1PriceBand4" type="text" id="ID1PriceBand4" size="4" maxlength="4" value=""></td>
    <td><input name="ID1DateBand4" type="text" id="ID1DateBand4" size="20" maxlength="100" value=""></td>
  </tr>
  <tr>
    <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>
    <td style="padding:20px 0 0 0" align="right"><input name="LGWYYZ" id="LGWYYZ" type="submit" value="Update!!"/></td>
  </tr>
</table>
</form>
</div>
 

<%
 
If Request.Form("LGWYYZ") <> "" Then 
 
'declare your variables
Dim connection, sSQL, sConnString
Price_Band_1=Request.Form("ID1PriceBand1")
Date_Band_1=Request.Form("ID1DateBand1")
Price_Band_2=Request.Form("ID1PriceBand2")
Date_Band_2=Request.Form("ID1DateBand2")
Price_Band_3=Request.Form("ID1PriceBand3")
Date_Band_3=Request.Form("ID1DateBand3")
Price_Band_4=Request.Form("ID1PriceBand4")
Date_Band_4=Request.Form("ID1DateBand4")
 
'declare SQL statement that will query the database
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'"
 
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.connection")
 
'define the connection string, specify database
'driver and the location of database
sConnString="myconnectionstringhere"
 
'Open the connection to the database
Connection.Open sConnString
 
'Execute the SQL statement
Connection.Execute sSQL
 
'Now close the connection object
connection.Close
Set connection = Nothing
 
Response.write "Prices / Dates Updated! - Redirecting you back"
 
Else 
Response.Write "Error! Nothing to Update!"
END IF
%>
 

many thanks in advance
Omar.