| Author |
 Topic  |
|
|
stealthc
Starting Member
21 Posts |
 Posted - 11 May 2001 : 22:46:31
|
Ok I'm having problems where using perfectly good SQL statements are locking up the site. Is there some sort of format to follow, any extra rules or something? Cuz this is really slowing things down and it's pissing me off completely. I have a deadline coming up for some work with asp and it isn't going to be met if I keep having problems with getting database connectivity. WTF is up with it?
Doesn't the newest MDAC have support for normal sql (or is it only limited support or what?)?
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 11 May 2001 : 23:27:52
|
| If you post some of the SQL Statements that are giving you problems, I'm sure someone will be able to tell whats going on. |
 |
|
|
MorningZ
Junior Member
USA
169 Posts |
Posted - 12 May 2001 : 00:16:35
|
and what do these SQL statements do in Query Analyzer ??
do they take forever to execute ???
need mo info 
 |
|
|
|
stealthc
Starting Member
21 Posts |
Posted - 12 May 2001 : 14:48:00
|
argh....some of them take forever to execute (I don't have query analyser though), some of them just don't work at all.
For example, I have a database where I want to re-order stuff, so I am doing a quick swap, and it doesn't like these two sql statements (they run but do nothing).
LINKS_ENTRY is the table name
L_ORDER is the field that determines order
SL_ID is the autoid given to each item
The script I'm using goes through trying to execute these statements (so that is ok) but they just simply do not work!!!
Ok so the first
UPDATE LINKS_ENTRY.L_ORDER FROM LINKS_ENTRY SET LINKS_ENTRY.L_ORDER=3 WHERE LINKS_ENTRY.SL_ID=2;
UPDATE LINKS_ENTRY.L_ORDER FROM LINKS_ENTRY SET LINKS_ENTRY.L_ORDER=2 WHERE LINKS_ENTRY.SL_ID=3;
The first sql statement takes item # 2 and gives it an order value of 3.
Then the second sql statement takes item #3 and gives it an order value of 2.
Why the heck doesn't this work?
I'm just trying to re-order stuff that's all, but this **** thing has selective sql or something how F**KING annoying is that.
I should figure out how to get an actual database server running....
But for the time being I want to stick with access (annoying as it is that it doesn't take perfectly fine syntax).
|
|
|
|
Marino
Starting Member
Canary Islands
42 Posts |
Posted - 12 May 2001 : 15:50:29
|
Try:
UPDATE LINKS_ENTRY SET L_ORDER=3 WHERE SL_ID=2
UPDATE LINKS_ENTRY SET L_ORDER=2 WHERE SL_ID=3
Marino
 |
|
|
|
stealthc
Starting Member
21 Posts |
Posted - 13 May 2001 : 04:48:28
|
OMG...maybe I screwed up somewhere with that. Yeah that sounds about right....lol. No wonder it wasn't doing the right thing.
|
|
|
|
stealthc
Starting Member
21 Posts |
Posted - 13 May 2001 : 05:04:45
|
still not working.
Here's my code for re-ordering:
If (MLvL=SectionID) or (MLvL=1) then
If AuthN="1" then
If Request.QueryString("LiD")<>"" then
If Request.QueryString("LMove")="U" then
OrDra = Request.QueryString("OrDr") - 1
LiDz = Request.QueryString("LiD") - 1
strsqla = "UPDATE LINKS_ENTRY SET L_ORDER=" & OrDra & "WHERE SL_ID=" & Request.QueryString("LiD") & ";"
strsql = "UPDATE LINKS_ENTRY SET L_ORDER=" & Request.QueryString("OrDr") & "WHERE L_ORDER=" & OrDra & ";"
set rsOrU = my_Conn.Execute (strsql)
rsOrU.close
set rsOrUD = my_conn.Execute (strsqla)
rsOrUD.close
end if
If Request.QueryString("LMove")="D" then
OrDra = Request.QueryString("OrDr") + 1
LiDz = Request.QueryString("LiD") + 1
strsqla = "UPDATE LINKS_ENTRY SET L_ORDER=" & OrDra & "WHERE SL_ID=" & Request.QueryString("LiD") & ";"
strsql = "UPDATE LINKS_ENTRY SET L_ORDER=" & Request.QueryString("OrDr") & "WHERE L_ORDER=" & OrDra & ";"
set rsOrU = my_Conn.Execute (strsql)
rsOrU.close
set rsOrUD = my_conn.Execute (strsqla)
rsOrUD.close
end if
end if
end if
end if
Now the LiD is the Identification number for the item. The LMove is the option that determines if the thing is moving up or down.
The OrDr is the current ordering number for the item that is to be moved. See this should work...I don't know why it wouldn't, although I have noticed a few problems with Querystrings, as they are returned as strings and not as a number. Maybe that is what has caused the problem?
|
|
|
|
Marino
Starting Member
Canary Islands
42 Posts |
Posted - 13 May 2001 : 07:14:58
|
quote:
OrDra = Request.QueryString("OrDr") - 1
LiDz = Request.QueryString("LiD") - 1
Yes, they are returned as strings, so maybe is better to try
OrdDra = Cint(Request.QueryString("OrDr")) - 1
LiDz = Cint(Request.QueryString("LiD")) - 1
quote:
strsqla = "UPDATE LINKS_ENTRY SET L_ORDER=" & OrDra & "WHERE SL_ID=" & Request.QueryString("LiD") & ";"
strsql = "UPDATE LINKS_ENTRY SET L_ORDER=" & Request.QueryString("OrDr") & "WHERE L_ORDER=" & OrDra & ";"
This will create a sql String like (Lid=3, and ordra=2, for example)
UPDATE LINKS_ENTRY SET L_ORDER=2WHERE SL_ID=3
so insert a blank space before WHERE
strsqla = "UPDATE LINKS_ENTRY SET L_ORDER=" & OrDra & " WHERE SL_ID=" & Request.QueryString("LiD")
strsql = "UPDATE LINKS_ENTRY SET L_ORDER=" & Request.QueryString("OrDr") & " WHERE L_ORDER=" & OrDra
And don't use trailings ";" if you want your code more DB independant. Anyway is not needed.
Marino
|
|
|
| |
Topic |
|
Topic Locked
