SQL and VB.NET

[Guest 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()

[Guest dexmix]

there goes his table...

[Guest aXiS_PoWeR]

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

[Guest bkellis]

Can you use the "ALTER TABLE" command?

[Robby]

Sorry dexmix, I couldn't help but laugh. :)

[Guest wild wolf]

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 you'll need to cast noBox.Text to an Integer.

[Guest wild wolf]

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 you'll 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..?

[Guest wild wolf]

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"?

[Guest 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.

[Guest wild wolf]

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 haven't done actual SQL code in quite a while so I'm a bit rusty in trying to figure this out.. but I just did a search on http://www.mysql.com (yes I know you're 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. :eek:

 

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

[Guest wild wolf]

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

 

asrar