Unanswered: dynamic web query design req. Check this out!!!!!!!
I want to create a query for 12 tables. coz all tables have comman fields and condition is also same.
Againt one itemid there are 12 possible entries for diferrents states. one idea i had in my mind is nested table but i dono how to use that.
column names are:
Now i am using 12 tables one for each state.....
Wat should be the proper design and tell me the cursor mode to save my resources..
See code below......
'''''making array of tables
arr_detail=split("delhi,kol,banglore,ahmd,chennai, hyd,mumbai,tvm,pun e,foreign,courier_national,courier_mumbai,courier_ delhi",",")
if detail=1 then
for i=lBound(arr_detail) to UBound(arr_detail)
str_detail= str_detail & "select * from "& arr_detail(i) &" where itemid='" & itemid &"'^"
'''''''making array of Query
arr_str = Split(str_detail,"^")
for i=lBound(arr_str) to UBound(arr_str)-1
' Response.Write "
set rs_detail =Server.CreateObject("ADODB.RecordSet")
rs_detail.Open arr_str(i),connview, adOpenStatic, adLockOptimistic
do while not rs_detail.EOF
for k=0 to rs_detail.Fields.count-1
if isnull(rs_detail(k)) or trim(rs_detail(k))=""or trim(rs_detail(k))="--" then %>
<% end if %>
Add a state field to your schema and use only one table.
You didn't mention what database you're using. If it's SQL Server and you're worried about performance, create a stored procedure that accepts state as an argument and returns your filtered table. Any updates required could then also be handled via stored procedure. That allows you to only open a connection for extremely brief periods of time, perform all of the processing server-side and add a layer of security.
Database is in MS-Access and i hav to do manage it ..
Well if i add a field 'state' and import all data to one table then struct. of table will remain same? and if i want to stick with the database i m having right now then wat is the possible solution...
the problem domain:
User will select and issue and i have to get only those states where record is enterd and skip the states where no record found..
Tell me another thing about how to import all 12 tables in to one table..