delete issue when sending multiple values 1

[bcardona]

if i send one value everything is fine, i.e. delete whatever from table where value ='100' now if i send multiple values i.e delete whatever from table where value = '100','101','102' and so on,

here is the actual results,

delete id from table where CAST(id As varChar(200)) ='88501, 88534, 88535, 88536, 88537, 88538, 88539, 88540, 88541, 88542, 88543, 88544, 88545, 88546'

it will not delete anything.any ideas

 

[bborissov]

How you send these values?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[dgrewe]

Your use of the delete command is wrong
From the VFP help file
DELETE [Scope] [FOR lExpression1] [WHILE lExpression2]
[IN nWorkArea | cTableAlias] [NOOPTIMIZE]

so if you try
DELETE ALL FOR INLIST(alltrim(CAST)
, '88501' , '88534', '88535', '88536', '88537', '88538', '88539', '88540', '88541', '88542', '88543', '88544', '88545', '88546') in WorkAreaAlias

or

DELETE ALL FOR alltrim(cast) $ '88501,88534,88535,88536,88537,88538,88539,88540,88541,88542,88543,88544,88545,88546' IN WorkAreaAlias

it might work



David W. Grewe Dave

 

[Mike Lewis]

Some confusion here about the use of CAST().

BCardona was using it to cast the ID to a Varchar. (I've no idea why.) It is a not a variable, as indicated by Dave's code.

The following should work:

DELETE FROM SomeTable WHERE INLIST(ID, ;
'88501','88534','88535','88536', ... etc)

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Thanks Mike,
I was only in the "Think VFP Mode". (I hate getting old)
Cast is a Oracle Keyword that creates a nested Table from a table.
so I guess he needs 3 code lines because he wants a copy of the delete records.

Select WorkAreaAlias

copy to TempTable all for inlist(alltrim(FieldName)
, '88501' , '88534', '88535', '88536', '88537', '88538', '88539', '88540', '88541', '88542', '88543', '88544', '88545', '88546')

DELETE ALL FOR INLIST(alltrim(FieldName)
, '88501' , '88534', '88535', '88536', '88537', '88538', '88539', '88540', '88541', '88542', '88543', '88544', '88545', '88546')


David W. Grewe Dave

 

[bcardona]

the reason i had to is i was getting overflow numeric errors whoever created the sql server db, did it wrong and i cant change it now, put yes i was passing it as a variable to a delete page. ill give your guys suggestions a try and let you know and thanks.

 

[bcardona]

mike,


the numbers i gave 88534 etc are values from a previus page(querystring), so you code should look like this,

INLIST(ID, ;'variable from previous page') is that correct?

 

[bborissov]

bcardona,
You should start with SQL Server
So my question is still actual:
HOW you send this?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bcardona]

heres how it works,

someone enters a number, based on the results, i put the values into a form, once they make sure the results are correct, they hit submit the form with the multiple values to the delete page.

so the sql statement looks like this.

delete id from table where CAST(EDPNO As varChar(200)) ='"&stredp&"'"

stredp = request("id") this is the variable i use from the previous page.

 

[Mike Lewis]

Bcardona,

I'm sorry, but you are now confusing me. I don't understand what you are saying about the "previous page" and the "delete page", or what "&stredp&" is all about. Also, it would have helped if you had said at the outset that this is a SQL Server query.

Let's go back to first principles. You have a table with field named ID. You want to delete records from that table where the ID field equals '88501' or '88534' or '88535', etc. Is that right?

If so, you can't use INLIST(), as I previously suggested, because T-SQL doesn't support it. However, you can use the IN operator, which is similar:

DELETE FROM SomeTable ID IN (
'88501','88534','88535','88536', ... etc)

As far as I can see, that should work. You don't need to do the cast (unless I'm missing something).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bcardona]

MIKE,
SORRY I WAS TRYING TO ANSWER SOMEONE WHEN THEY ASKED ABOUT EXACTLY WHAT I WAS DOING, YES YOUR ARE EXACTLY RIGHT WITH THIS You want to delete records from that table where the ID field equals '88501' or '88534' or '88535', etc.


SORRY I SHOULD HAVE SAID SQLSERVER, ALSO IM USING ASP TO DO IT. AS FAR AS THE CAST IF I DONT USE IT I GET THIS ERRROR
SQL Server]Arithmetic overflow error converting varchar to data type numeric.THATS THE ONLY REASON IM USING IT, I WILL TRY YOU CODE AND SEE WHAT HAPPENS.

 

[bborissov]

OK, ASP and SQL Server. So I'm wondering what is VFP relation here?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Mike Lewis]

BCardona,

So, it looks like it is the cast that's causing the error. If that's so, leaving it out should certainly improve matters. Let us know if that works.

On another point, you say you are using ASP to send the SQL Server query. So, where does VFP come into the equation?

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bcardona]

MIKE,
here is the query
delete from table WHERE
CAST(id As varChar(20))IN('"&stredp&"')"

stredp is the variable i use i.e stredp=request.form("id")

i send these values and nothing gets deleted
('88597, 88598, 88599, 88600, 88601, 88602, 88603, 88604, 88605, 88606, 88607, 88608, 88609, 88610, 88611, 88612, 88613, 88614, 88615, 88616, 88617')

 

[bborissov]

bcardona,
COULD YOU PLEASE SAY WHAT YOU USE FOR YOUR FRONTEND AND WHAT YOU USE AS DATABASE?
That will never WORK, period!
Could you post your code WHERE that happens?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bcardona]

bborissov

my front end is an asp page, back end is sql server, its a 3 step process, user puts in a id number, it then displays the results which is multiple id numbers, those id numbers then get submitted via a html form to the delete page, here is the code. this is my original sql statement, keep in mind stredp is a querystring value from the submit page.

dim stredp
stredp = request("id")

delete from table WHERE cast(EDPNO As varchar(20))='"&stredp&"'"

 

[Mike Lewis]

my front end is an asp page, back end is sql server,

So where does VFP come into the equation?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Try to build the whole string. Do not use parameters (BUT FIRST YOU NEED TO MAKE SURE THAT THERE ARE NOT SQL INJECTIONS):

sqlString = 'DELETE FROM TABLE WHERE Id IN(" & stredp & ')'
** execute this string from your front end

Just make sure that the string you execute is something like:

delete 
  from table
WHERE id  IN (88597, 88598, 88599, 88600, 88601, 88602,
              88603, 88604, 88605, 88606, 88607, 88608,
              88609, 88610, 88611, 88612, 88613, 88614,
              88615, 88616, 88617)

So you could try:

delete
   from table 
   WHERE id IN(" & stredp & ")"

Can you see changes?
I remove CAST funtion and remove single quotes you put around the stredp.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bcardona]

bborissov ,

thank you very much, that code was it, thanks everybody for your time and help.

 

[bborissov]

bcardona,
You welcome,
but next time I suggestion:
1. Ask in the right forum (mostly in your FrontEnd language one)
2. Give enough information what about you need.
3. Answer to the questions othe rmember asked.


I'm glad you resolve your issue.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP