|
hi guys
i have two tables named (table1) and (table2),,
there is a field named ID which belongs to both tables ,,
i want to delete matching records from both tables based on this ID field which is a nvarchar type ,, can any body tell me the exzact query
thanks in advance
hello
|
|
|
|
|
ghumman63 wrote: there is a field named ID which belongs to both tables
Which way does the relationship go? - You have to delete the child first otherwise you get an integrity violation. However, if you have cascading deletes then you can delete the parent and it will cascade delete the child too. (Most people have this off because it can cause devistation if not used properly)
|
|
|
|
|
what if there is no relationship between both tables ?
hello
|
|
|
|
|
If there is no physical relationship (as specified by a Foreign Key), but an implied (logical) one, then it doesn't matter which order you delete the records. What I would do though, is wrap both deletes in a transaction so that you don't get orphaned rows.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Create a trigger that deletes records from the second trigger based on the deleted values from the first
create trigger dbo.DeleteTrigger on dbo.table1<br />
FOR DELETE<br />
AS<br />
DELETE table2 <br />
WHERE table2.ID = ( SELECT ID FROM DELETED)<br />
go
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi
I am noob trying to learn ADO using C# 2005. I am trying to create a simple contact manager that can store contact data. I want to include Add, Edit & Delete buttons so that i can add, edit & delete a record from contract manager. Can anyone tell me how should i go about doing this? I can add the automated data navigator for this but i dont want to do that. I want to write my own code for it. Any help will be appriciated
Thanks
Irfan
I am still learning
|
|
|
|
|
Best approach is to write a DAL (Data Abstraction Layer). This essentially means creating a class (or group of classes) to handle your database interaction. Your application can then call the DAL class(es) rather than the database direct. At a conference I was at a couple of weeks ago one of the speakers gave some excellent advice: If you have anything from your System.Data namespace in the presentation layer (your WinForm or WebForm class, or control classes, etc.) then there is something wrong with the design.
In the DAL class(es) you connect to the database, perform your query, close the connection and return the data.
The way I do it is that each public method in the DAL is a proxy for a stored procedure in the database.
Remember that you can send pretty much and SQL into a SqlCommand. So if you can do it in Query Analyser or SQL Server Management Studio, then you can issue it via a SqlCommand (Just Remember that GO splits the batch, and that isn't supported - you must only issue one batch at a time, but you can have as many SQL Commands as you like in a batch)
Does this help?
|
|
|
|
|
Colin Angus Mackay wrote: If you have anything from your System.Data namespace in the presentation layer (your WinForm or WebForm class, or control classes, etc.) then there is something wrong with the design.
If we need DataTable or DataRow, then what should we do
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
uroojkhan wrote: If we need DataTable or DataRow, then what should we do
You don't need those things. They should be converted to business object on their way out of the DAL.
If the application is small enough, you can probably get away with it. But for any application that is large enough you should really look in to a proper 3+ tier architecture splitting the presentation, business, and data layers out from each other.
|
|
|
|
|
uroojkhan wrote: If we need DataTable or DataRow, then what should we do
Find something better.
Seriously, a DataTable/DataRow does not model your business object. It is just a handy wrapper that Microsoft provided. It does not make for a robust design for several reasons:
1. Suppose that you want to expose your object as a web-service. While .NET knows what a DataTable is, you would have to write your own wrapper for any other platform (e.g. Java).
2. What happens if you change a field name in the database? DataSets (and so on) are loosely coupled. You are more likely to forget to fix a DataSet than you are a properly designed business object.
3. They are large and unwieldy. They provide a lot of overhead because they are so generic. A lot of plumbing is needed to make sure that it works with different types of data.
4. It does not abstract the UI from the database. You still need knowledge of the database to use them. Which makes more sense to you? A class with a property called CustomerName or a DataRow with a value dr["cust_name"] (which must also be cast to the appropriate type).
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks guys... Will give i a try
I am still learning
|
|
|
|
|
Go into Google and look for ADO 2.0 tutorials sucg as W3 School and 15 seconds.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I want to create a new view in my database using SQLDMO com class.I encounter with a problem near my text property of the view .
If any one know about this dll.Please help me
Rupa
|
|
|
|
|
Hey first of all not sure if this is the right board so i apoligise now if it is not.
Any ways my question is I am using SQL with C#.NET and have a query:
SELECT groups FROM T_USERS
WHERE username = @USERNAME;
SELECT organisation FROM T_ORGANISATIONS
WHERE username = @USERNAME;
which i execute using cmd.ExecuteReader()
However does not seem to be returned in the form reader(0) reader(1).
How do i go about solving this problem (yes I know it is basic so sorry to bore you with it but i am not sure what to search for)
Any advise would be awesome
Thanks
Dan
|
|
|
|
|
One reader can return many result sets.
You use Read() to move to the next row in a result set (the result of a SELECT statement), you use MoveNext() to move to the next result set (i.e. to the result of the next SELECT statement)
Does this help?
|
|
|
|
|
Thats magic thanks remmber seeing that some where and when you said that it all fell into place.
Thanks again.
Dan
|
|
|
|
|
Hi - Is there a way we could return the result in above format - know of hours, minutes, seconds.
Thanks for your time.
Vani
|
|
|
|
|
VK-Cadec wrote: Is there a way we could return the result in above format - know of hours, minutes, seconds.
DateDiff returns the result in an internal format. It is merely the rendering of that data on the screen that gives it a format. The formatting of data should be the function of your application's presentation layer, not the database.
|
|
|
|
|
Hi,
I'm trying to get total of each row in my table. For eample, I have columns x, y and z (all int). To get total, i'm writing "select (x + y + z) As total from myTable".
It works fine as long as one of the colunm is not empty. If it finds any null data in x, y or z then total shows up null. How can i get total in such case?
Thanks in advance
|
|
|
|
|
|
Thank you very much! IsNull worked fine.
|
|
|
|
|
Any expression containing a null value will evaluate to null.
There are a couple of ways around this. You could define your database columns so that they are not allowed to contain null values, and assign them a default value of zero.
Another method would be to test each value for null, and use 0 for the value of that operand if the result is true.
Hope that helps.
|
|
|
|
|
Could try something like:
SELECT x,y,z,
COALESCE(x,0) + COALESCE(y,0) + COALESCE(z,0) as [Row Total]
FROM .............
|
|
|
|
|
without using linked server is there another method to do the linking between faxpro(.dbf) & mssql server
Gurudatta B. Shelke
|
|
|
|
|
Please stop reposting questions. People have already told you how to do this with linked servers. If you don't want to use linked servers (and to be honest, I can't see any reason why you wouldn't), you should reply to them on that post.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
