SQL and VB.NET

[aXiS_PoWeR]

can anyone tell me an SQL command for adding or deleting a field from SQL Server 2000 using a objCmd.ExecuteNonQuery() ?

 

[Derek Stone]

objCmd.CommandText = "DELETE * FROM myTable WHERE ID > 0"
objCmd.ExecuteNonQuery()

 

[dexmix]

there goes his table...

 

[aXiS_PoWeR]

lol...i need the table just need to delete or add a column or 2

 

[bkellis]

Can you use the "ALTER TABLE" command?

 

[Robby]

Sorry dexmix, I couldnt help but laugh.

 

[wild wolf]

Originally posted by Derek Stone

objCmd.CommandText = "DELETE * FROM myTable WHERE ID > 0"
objCmd.ExecuteNonQuery()

hi Derek, i have the following code,

command.CommandText = "DELETE * FROM table WHERE item_id LIKE "+noBox.Text;
command.ExecuteNonQuery();

and an exception error is caught saying "Syntax error near * "

what can the error be?

 

[wyrd]

Change

"DELETE * FROM table WHERE item_id LIKE "+noBox.Text

to

"DELETE * FROM table WHERE item_id LIKE " + noBox.Text + ""

You have to put strings inside . If the item_id is supposed to be a number then youll need to cast noBox.Text to an Integer.

 

[wild wolf]

Originally posted by wyrd
Change

"DELETE * FROM table WHERE item_id LIKE "+noBox.Text

to

"DELETE * FROM table WHERE item_id LIKE " + noBox.Text + ""

You have to put strings inside . If the item_id is supposed to be a number then youll need to cast noBox.Text to an Integer.

did not work, still says Syntax error near *

asrar

 

[wyrd]

Hmm.. well what exactly is in noBox.Text? Just numbers like 1, 2, 3, etc.. or something like ID1, ID2, ID3, etc..?

 

[wild wolf]

Originally posted by wyrd
Hmm.. well what exactly is in noBox.Text? Just numbers like 1, 2, 3, etc.. or something like ID1, ID2, ID3, etc..?

alphanumeric and the data type for item_id is also alphanumeric.

asrar

 

[Derek Stone]

You have a table named "table"?

 

[wild wolf]

hi, the table name is not table but journal_detail, i put table here just to clear things, sorry if i confused anyone.

asrar

 

[Derek Stone]

sSQL = "DELETE * FROM journal_detail WHERE item_id LIKE %" & noBox.Text & "%"

Give it one more shot.

 

[wild wolf]

Originally posted by Derek Stone

sSQL = "DELETE * FROM journal_detail WHERE item_id LIKE %" & noBox.Text & "%"

Give it one more shot.

hi did not work, the problem is not on how i take the input (i.e it has nothing to do with the noBox.Text), the problem is with the delete command, the error points me towards the * and says the error is before the * in that case its something to do with the delete keyword, and because of that there is a question i would like to ask, can we really use the delete command in this way?

asrar

 

[wyrd]

You know, I havent done actual SQL code in quite a while so Im a bit rusty in trying to figure this out.. but I just did a search on www.mysql.com (yes I know youre using Access or SQL Server but mysql.com is still a great source to quickly look up generic SQL code) and they have the delete command listed like so;

DELETE FROM table WHERE col = Stuff

Note the no * at all. Might as well give it a shot, nothing else seems to be working.

Just remember to back up your database before toying with any sort of delete command.

 

[wild wolf]

BINGO wyrd, that did it!!!!! thanx alot

asrar