SQL Data Source Delete Statement
Feb 3, 2008
Hey everyone,
Currently I have a problem that with a gridview delete command because the database I currently want to delete from is a PK to 1 other table. So how can I delete from that "Membership" table before I use the gridview to delete from the "Events" table? Here is a very shortened version of the tables
Events
EventID (PK)
Membership
MembershipID
EventID (FK)
UserID (FK)
I have to delete ALL the members from the event in the membership table first before I can delete the actual Event...So how can I do that when the gridview only allows you to delete from 1 table, if I understand this correctly.
Here is the sqldatasource delete command as of right now.
DeleteCommand="DELETE FROM [Events_Events] WHERE [EventID] = @original_EventID"
Obviously I am getting a error because of the FK.
Thanks,
Chris
View 2 Replies
ADVERTISEMENT
Apr 23, 2008
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
View 2 Replies
View Related
Feb 6, 2008
Is there a SQL statement that will delete the primary key data and data in the other tables that depend on that primary key?
View 3 Replies
View Related
Jul 17, 2015
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?Â
View 3 Replies
View Related
Jan 12, 2007
Hello,
Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?
The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.
The SQL command in the custom properties doesn't cross reference back to the variable.
If anyone can help or needs further detail to help please post a resonse.
Thanks,
Marcus Simpson
View 4 Replies
View Related
Mar 13, 2008
hi ,
i am trying for a drill through report (rdlc)
ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like
An error has occurred during report processing.
A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".
the code is
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
using DAC;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
DataSet ds = new DataSet();
ds = obj.get_order();
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";
ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
Label1.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
string order_id = Label1.Text;
DataSet ds = new DataSet();
ds = obj.get_orderdetail(order_id);
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");
ReportViewer1.LocalReport.Refresh();
}
}
the code in method get_orderdetail(order_id) is
public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.
View 1 Replies
View Related
Aug 12, 2007
I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?
View 5 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Feb 2, 2008
,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?
Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************
How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));
db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;
dsNew.Update();
2)
RelationalDataSourceView rdsv;
rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));
rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);
*************************************************************
4)
// Make sure we have the name we thought
dss.Tables[0].TableName = tableName;
// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();
5)
MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";
Add columns:
ScalarMiningStructureColumn smsc;
// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);
*******************************************
6)
MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));
mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);
MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);
mm.Update();
Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.
View 1 Replies
View Related
Mar 2, 2007
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
Thanks in advance
ADG
View 2 Replies
View Related
Jan 31, 2013
is it possible to delete a source file(*.txt) when a SSIS package is done with it?
View 3 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
May 8, 2007
hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?
thanks for you helps
View 1 Replies
View Related
Dec 11, 2007
Today I was making a few reports.
When I tested the reports in Visual Studio, they worked great: I got the expected result.
But when I deployed the reports to our reportserver the problem started.
When I click on the directory in which my reports are deployed, I got my 4 reports.
Till now everything worked correct.
But when I click on a report to view the results it went wrong.
I got an error:
"Cannot create a connection to data source 'Live'"
(Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work.
I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works.
The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server.
Maybe that is the problem?
Can someone tell me what is wrong?
View 1 Replies
View Related
May 20, 2007
Hi there,
I'm trying to build a report for Windows Forms (C#) using .rdlc files.
But every time I run it, I get this error message. I've followed the step-by-step from MSDN instructions... but it doesn't work!
Could anyone help me?!
Tks a lot!
Luis Antonio - Brazil
View 1 Replies
View Related
Dec 13, 2007
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
View 13 Replies
View Related
Apr 11, 2007
Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients
WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN
(SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan
View 1 Replies
View Related
Nov 13, 2007
Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C: A's primary key is aID and has no foreign key B's primary key is bID and has 1 foreign key: aID (linking this table to table A); C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C INNER JOIN B ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB
View 2 Replies
View Related
Sep 15, 2004
hi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything),
so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?
View 10 Replies
View Related
Dec 14, 2001
When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.
View 7 Replies
View Related
Oct 2, 2006
Hi what do i need to add to this stmt to delete the result ?
---------------
select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1
---------
i have tried delete * from TABLE_NAME where (select...)
not great at SQL appreciate any help...
View 3 Replies
View Related
Dec 6, 2006
Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn
View 6 Replies
View Related
Sep 26, 2007
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks!
James
View 2 Replies
View Related
Mar 12, 2006
Hey guys...
I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select.
I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply.
however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire.
Do anyone on the forum know why this has happened?
Sub DeleteUserTicket(sender as Object, e as EventArgs)
Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
End Sub
'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size
Sub RunSQL(strSQL)
Dim objCmd As SqlCommand
Dim sqlConn = New SqlConnection(strConn)
objCmd = New SQLCommand(strSQL, sqlConn)
objCmd.Connection.Open()
'Check that the rows can be deleted if not then return a error.
Try
objCmd.ExecuteNonQuery()
response.redirect("ticketsystemtest2.aspx")
Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>"
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please " _
& "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number
Message.Style("color") = "red"
End If
End Try
objCmd.Connection.Close()
sqlConn = nothing
objcmd = nothing
End Sub
cheers
View 2 Replies
View Related
Sep 6, 2001
One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up)
Thanks-
Jack
View 2 Replies
View Related
Feb 1, 2001
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
View 1 Replies
View Related
Dec 12, 2005
Hi,
I've got a table with about 500 000 records and growing monthly by about 40 000 records
When I perform the following query:
DELETE from [myTable] WHERE Month = '07' AND Year='2005'
This query will take about 10 minutes to execute. Columns Month & Year are both indexed.
Surely MSSQL can't be this slow on only 500 000 records.
Must I do some other database optimization ???
Thanks
View 6 Replies
View Related
Jan 27, 2005
Hello, currently I have a query like this:
PHP Code:
SELECT *
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
ORDER BY relations.node1
Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:
PHP Code:
DELETE relations
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
But that would delete only from the relations table and not from the paths table. I need to delete from both tables.
Can anyone help me please? Its kinda urgent.
Thansk!
View 5 Replies
View Related
Oct 26, 2004
Hi,
I need to delete the following records (from enrollment_fact):
SELECT
a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN
"dbo"."ENROLLMENT_FACT" a
on c."LOC_SID" = a."LOC_SID"
WHERE
b."LOC_KEY" = c."LOC_KEY"
and
a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
This is the approach (excuse the misuse of the concat function, but you get the idea)
DELETE FROM "dbo"."ENROLLMENT_FACT"
WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID")
IN (
SELECT DISTINCT CONCAT (a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
)
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a
ON c."LOC_SID" = a."LOC_SID"
AND a."DATE_SID" BETWEEN b."MIN_DATE"
AND b."MAX_DATE")
comments? better way? (without using an sp)
thanks
View 8 Replies
View Related
Mar 2, 2005
well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'
PLZ help
View 1 Replies
View Related
Nov 4, 2007
Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !
View 6 Replies
View Related
Apr 29, 2008
I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status.
IDStatus
1Good
2So-So
3So-So
4Bad
5Bad
6Bad
7Bad
8Bad
9So-So
10Good
So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.
View 10 Replies
View Related
Jul 30, 2013
i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. i have table having three column,
ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4
in my case first two record are duplicate i want to keep first one and delete the other.
View 8 Replies
View Related
