3 Replies - 1469 Views - Last Post: 14 December 2008 - 04:54 PM Rate Topic: -----

#1 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Delete record from SQL db

Posted 13 December 2008 - 12:26 PM

Hi, I have an SQL 2008 Express database and it is not allowing me to delete records...

These 3 tables are the ones that I'm dealing with that have relationships...

I checked all of the constraints and there are none and I checked all of the "enforce foreign key restraints" for all of the relationships and they are all set to "No"

Customers table
CustomerID - Identity
various_other_fields
TherapistID

With these Relationships

FK_Customers_Therapists
Primary key table - Therapists (Field - TherapistID)
Foreign key table - Customers (Field - TherapistID)

FK_Tickets_Customers
Primary key table - Customers (Field - CustomerID)
Foreign key table - Tickets (Field - CustomerID)

***************************************
Therapist table
TherapistID - Identity
various_other_fields

With these Relationships

FK_Customers_Therapists
Primary key table - Therapists (Field - TherapistID)
Foreign key table - Customers (Field - TherapistID)

FK_Tickets_Therapists
Primary key table - Therapists (Field - TherapistID)
Foreign key table - Tickets (Field - TherapistID)

***************************************
Tickets table
TicketID - Identity
various_other_fields
TherapistID
CustomerID

With these Relationships

FK_Tickets_Customers
Primary key table - Customers (Field - CustomerID)
Foreign key table - Tickets (Field - CustomerID)

FK_Tickets_Therapists
Primary key table - Therapists (Field - TherapistID)
Foreign key table - Tickets (Field - TherapistID)

Here is the Stored Procedure that I am using...
ALTER Procedure dbo.DeleteCustomer
(
	@CustomerID int
)
As
  Set NoCount On;
  Delete From Tickets Where (CustomerID = @CustomerID);
Delete From Customers Where (CustomerID = @CustomerID);



Here is the procedure that I am using...
			string sSQL = "DeleteCustomer";
			SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
			SqlCommand cmd = new SqlCommand();
			cmd.Parameters.Clear();
			cmd.CommandText = sSQL;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.AddWithValue("@CustomerID", Session["CID"].ToString());
			cmd.Connection = conn;
			conn.Open();
			cmd.ExecuteNonQuery();
			conn.Close();
			GridView1.DataBind();



Can anyone tell me what I am doing wrong?

Is This A Good Question/Topic? 0
  • +

Replies To: Delete record from SQL db

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: Delete record from SQL db

Posted 13 December 2008 - 07:31 PM

What is the error message that it is giving you?

I notice here from your relationships (If I am understanding it correctly) is actually setup in a loop. You have all three tables joined to one another with each table having two relationships.

Which is fine as long as all relationships are set properly. For instance if they are all set to cascade delete, there might be an issue. The message should tell you about the type of relationship violation you are having.

If you share it with us we can help you decipher it. :)
Was This Post Helpful? 0
  • +
  • -

#3 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Delete record from SQL db

Posted 14 December 2008 - 08:30 AM

Actually I'm not getting any kind of error at all, the records just aren't deleting...

I was wondering, as long as I handle the constraints on the front end, there really isn't any need to use relationships, is there?

I come from using PHP/MySQL in a world without constraints... LOL
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: Delete record from SQL db

Posted 14 December 2008 - 04:54 PM

Well the idea of the constraints is for data integrity (example would making sure there is no ticket records for a therapist that doesn't exist in the therapist table). But you could certainly handle everything on the front end if you like you just have to be careful and probably run everything inside of transactions (so if one of the deletes fail all the deletes for the transaction fails so nothing will happen because all changes would be rolled back).

But if no error message is happening and you are not deleting, it could be how you are doing the delete or the data you are passing to the delete. I would look to add a statement inside the stored procedure that records the incoming customerid just to make sure that it is being passed in. Then you can execute your code and see if the value you expect is what is actually coming into the stored procedure... or if you are actually triggering the stored procedure to begin with.

Something tells me there is a disconnect somewhere in there. My guess is the value you are passing to the stored procedure is probably not right... this would lead to delete statements executing but executing for records you don't actually have. This would lead to no errors and yet not deleting the data you expect it to.

:)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1