| Author |
Topic |
|
g3jimha
Starting Member
10 Posts |
 Posted - 2011-06-27 : 09:18:37
|
| Hi, I'm having a problem deleting all the rows in my database with the same unique ID number. Im using vb.net and Ive created a loop to go through my tables and delete the ID(CITBRegNo) from each;Tabs = {"CPCSBlue", "CPCSRed", "CCSNG", "IPAF", "IOSH", "SSSTS", "SMSTS", "RescueFromHeight", "FirstAid", "Safety"}Dim i As Integer = 0DoWhile (i < (Tabs.Length - 1)) sql2 = "DELETE FROM " & Tabs(i) & " WHERE CITBRegNo='" &txtCITB.text "')" ConnectDB(sql2, listviewboxname, ReadIn) i = i + 1Loopany help would be appreciated :) thanks, Jim |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-27 : 09:44:52
|
| Insert a print statement or step using debugger to look at the sql2 statement; copy it and run it from a SQL Server Management Studio to see if that actually parses, and deletes the rows you expect it to delete.If it does, examine your ConnectDb function. Does it actually connect to the database and send the execute command to the server? Is it connecting to the correct database on the server etc. |
 |
|
|
g3jimha
Starting Member
10 Posts |
Posted - 2011-06-27 : 09:50:04
|
| il go and give that a shot just now, its definately connected. the connectDB is just a function to cut down typing the same thing over and over again, il let you know how i get on with running it on the server management studio but i fear I havent set the DB up correctly because It's saying its something to do with the foreign keys, I'll go and do it just now though |
|
|
|
g3jimha
Starting Member
10 Posts |
Posted - 2011-06-27 : 09:53:26
|
| "The DELETE statement conflicted with the REFERENCE constraint "FK_IPAF_StaffDetails". The conflict occurred in database "Training", table "dbo.IPAF", column 'CITBRegNo'" Is there anyway to get round the foreign keys? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-27 : 10:06:14
|
| Take a look at this page http://msdn.microsoft.com/en-us/library/ms175464.aspx especially the section on Referential Integrity. It has a nice explanation of the foreign key constraint.In plain English, what it is trying to tell you is that the row that you are trying to delete is referenced by a column in another table. So if you do want to delete this row, you will first need to delete the rows in the table that are referencing the data in this table. |
|
|
|
g3jimha
Starting Member
10 Posts |
Posted - 2011-06-27 : 10:09:59
|
| ah rightttttttt! thanks very much, especially for cutting down the jargon in those msdn, they just do everything to death them. thanks again :) |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2011-06-28 : 05:09:11
|
| I suggest you use stored procedure to perform that action.It is more reliable, fast when it comes to query parameter..Make a module in your vb.net that holds the parameter of the stored procedure then pass the value to sql server then create your stored procedures in sql server that will execute the parameter value. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-28 : 07:11:59
|
| Ok, two things (well 3)Never, never, never use the sa account for an application. Way too many permissions. Read up on the principal of least privilege and apply it. (and go and change that sa password immediately if it's the real one. You've just given anyone who reads the key to your server and it's not impossible to find where that server is if someone is bored and malicious (see sony)).Read up on SQL Injection. Your code lets anyone who's bored and malicious and has access to your app to do anything including drop your DB, stop SQL, possibly even damage the network depending on the service account SQL is running as.--Gail ShawSQL Server MVP |
|
|
|
g3jimha
Starting Member
10 Posts |
Posted - 2011-06-29 : 10:11:15
|
| nah it wasnt the password just a temporary measure while im creating it. but youve scared me enough that I removed the post I will read up on the suggested topics. thanks |
|
|
|
|
Deleting in SQL