Deleting from sql question.

I have a data source which I created a custom statement for which I need to delete from. I created the folowing selecct statement

SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)

I wrote the following DELETE statement

DELETE FROM SrcComputer WHERE (CompId = @.CompId)

I have tested it in SQL server management and it does what I want it to do, but I don't know how to use it in my code. I put it inside of the DELETE tab inside of my custome statement and added a delete button inside of the gridview which utializes my data source, but it's not working. I don't know how to set the CompId variable? I want the statement to delete the row that the user clicks on. Can anyone give me some advice?

You can set the delete parameter like in this sample:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SNo" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SNo" HeaderText="SNo" ReadOnly="True" SortExpression="SNo" />
<asp:BoundField DataField="CQNo" HeaderText="CQNo" SortExpression="CQNo" />
<asp:BoundField DataField="WorkDate" HeaderText="WorkDate" SortExpression="WorkDate" />
<asp:BoundField DataField="Analysis" HeaderText="Analysis" SortExpression="Analysis" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"
DeleteCommand="DELETE FROM [effort] WHERE [SNo] = @.SNo"
SelectCommand="SELECT [SNo], [CQNo], [WorkDate], [Analysis] FROM [effort]"
UpdateCommand="UPDATE [effort] SET [CQNo] = @.CQNo, [WorkDate] = @.WorkDate, [Analysis] = @.Analysis WHERE [SNo] = @.SNo">
<DeleteParameters>
<asp:Parameter Name="SNo" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CQNo" Type="String" />
<asp:Parameter Name="WorkDate" Type="DateTime" />
<asp:Parameter Name="Analysis" Type="Decimal" />
<asp:Parameter Name="SNo" Type="Int32" />
</UpdateParameters>

</asp:SqlDataSource>

|||

ok I believe that I have done what you said. Most of it was already there the Parameter CompId's type wasn't there but the rest was there here is what I have.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:srcConnectionString1 %>"SelectCommand="SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @.UserName)"OnSelecting="SqlDataSource2_Selecting"DeleteCommand="DELETE FROM SrcComputer WHERE [CompId] = @.CompId"><SelectParameters><asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/></SelectParameters><DeleteParameters><asp:ParameterName="CompId"Type="int64"/></DeleteParameters></asp:SqlDataSource>

My question is where do assign a value for CompId. When I click the delete link it does nothing. Just seems to refresh the page and nothing changes.

|||You need to set DataKeyNames="CompId" from your gridview .|||

limno,

Thank you. That is exactly what I needed. I have one other question for you. If I am already using the DataKeyNames for a different variable is it possible to have 2? It just so happened in this case I could use the same variable for both selecting and deleting.

Thanks, Matt

|||

Matt,

You can have more than one as your DataKeyNames, just separate them with comma like DataKeyNames="id1, id2".

|||Thanks that's good to know also.sql

Deleting duplicate records from a table.....

I loaded one table via SSIS and found that it contained many duplicate records (from the input source). I can create a SQL task to delete them, but I wonder if SSIS offers and task "out of the box" to delete dups?

TAI,

barkingdog

I don't know about anything in SSIS to do so but here's a great way to do it using CTE's and Row_Number()

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

|||

Use a Sort transform from SSIS is a possible alternation - Sort on certain keys and check "remove duplicate records" at Sort transform.

hth

wenyang

Deleting and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

delete last added row -how to

hi i have a question how can i delete last added row. I have 2 tables .
source and destination . I take a 1 row from source table , do some
operation on it and save to destination table . after succesfull written I
want to delete added row from source table.. i'm using a coursors. the main
problem is : is there any function to check which row was last added. Now I
am doing it using select * from destionation where (and necessary
conditions). but if destination table will be 100000000 rows for example it
takes too much time... Is there another possibility to do it ?
please help

Marcin Wolku
wolkuOne important think both tables don't have primary key

Uytkownik "Marcin Wolku" <wolku@.epf.pl> napisa w wiadomoci
news:42805b7b@.news.vogel.pl...
> hi i have a question how can i delete last added row. I have 2 tables .
> source and destination . I take a 1 row from source table , do some
> operation on it and save to destination table . after succesfull written I
> want to delete added row from source table.. i'm using a coursors. the
> main problem is : is there any function to check which row was last added.
> Now I am doing it using select * from destionation where (and necessary
> conditions). but if destination table will be 100000000 rows for example
> it takes too much time... Is there another possibility to do it ?
> please help
> Marcin Wolku
> wolku|||Unless you can identify the last inserted row by some column or columns
in the table you cannot delete that row. There is no special feature
for determining the insertion order.

The most important problem you have is the lack of a primary key. Why
don't you fix this? This is a fundamental design flaw as I hope you
know.

--
David Portas
SQL Server MVP
--