Kaare
|
| Posted: 07/30/2005, 7:57 AM |
|
I make a select like this to make a alias table :
sql4 = "Select * from pameld1 as pm2 where ID = "&Blag
I want to use ALIAS because I want make a update for two different WHERE values in the same table PAMELD1.
The first update is ok, but the Sql for my ALIAS did not update the table PAMELD1. I did't get any errormessages when I update my ALIAS.
The update for my ALIAS (PM2) is like this:
sql1 = "UPDATE PM2 SET ANTMAAL = ANTMAAL + "&bmal&" , ANTMALMOT = ANTMALMOT + "&hmal&" , maldifferanse = maldifferanse - "&mdiff&" , antkamper = antkamper + 1 , anttap = antTap + 1 WHERE (ID = "&Blag&")"
CONN.EXECUTE(SQL1)
errormessage1 = CCProcessError(conn)
Anyone who can give me a hint 
Regards
Kaare Simensen
|
|
|
 |
DonB
|
| Posted: 07/30/2005, 9:55 AM |
|
SQL doesn't utilize a table alias on the updated table (e.g., UPDATE PM2),
it thinks there should be an actual table named "PM2". Also, not putting
spaces before and after the "&" may be a problem for VBscript to interprete.
I would display the completed sql statement (sql1), and copy it into a
suitable query tool to see whether the database chokes on your query, or if
it is 'updating' a non-existent row ("Blag" isn't what you think it should
be).
--
DonB
http://www.gotodon.com/ccbth
"Kaare" <Kaare@forum.codecharge> wrote in message
news:642eb956a8a2db@news.codecharge.com...
> I make a select like this to make a alias table :
> sql4 = "Select * from pameld1 as pm2 where ID = "&Blag
>
> I want to use ALIAS because I want make a update for two different WHERE
values
> in the same table PAMELD1.
> The first update is ok, but the Sql for my ALIAS did not update the table
> PAMELD1. I did't get any errormessages when I update my ALIAS.
>
> The update for my ALIAS (PM2) is like this:
> sql1 = "UPDATE PM2 SET ANTMAAL = ANTMAAL + "&bmal&" , ANTMALMOT =
ANTMALMOT +
> "&hmal&" , maldifferanse = maldifferanse - "&mdiff&" , antkamper =
antkamper +
> 1 , anttap = antTap + 1 WHERE (ID = "&Blag&")"
>
> CONN.EXECUTE(SQL1)
> errormessage1 = CCProcessError(conn)
>
> Anyone who can give me a hint
> Regards
> Kaare Simensen
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Kaare
|
| Posted: 07/30/2005, 3:30 PM |
|
Thank you Don B.
I have a solution of my problem, maybe it could be done esalier, but I first make a update of my first sql, then I make a select of my other update, and so do a update for my second update.
Here is my code and the result is just what I need.
sql = "UPDATE pameld1 SET pameld1.ANTMAAL = pameld1.antmaal + "&hmal&" , pameld1.ANTMALMOT = pameld1.antmalmot + "&bmal&" , pameld1.maldifferanse = pameld1.maldifferanse + "&Mdiff&" , pameld1.antkamper = pameld1.antkamper + 1 , pameld1.antseier = pameld1.antseier + 1, pameld1.antpoeng = pameld1.antpoeng + 3 WHERE (pameld1.ID ="&Hlag&")" ' and (pameld1.klubbid = "&Hklubb&")"
conn.execute(sql)
' Here is my select from my other ' where'
sql4 = "Select * from pameld1 where ID = "&Blag
conn.execute(sql4)
sql1 = "UPDATE pameld1 SET ANTMAAL = ANTMAAL + "&bmal&" , ANTMALMOT = ANTMALMOT + "&hmal&" , maldifferanse = maldifferanse - "&mdiff&" , antkamper = antkamper + 1 , anttap = antTap + 1 WHERE (ID = "&Blag&")" ' and (klubbid = "&Bklubb&")"
conn.execute(sql1)
and the result make me happy.
Regards
Kaare
|
|
|
|
|
