[RESOLVED]Copy command in form view

Hi,

I have a form view that lets the user create, edit and delete data in a sql database. 

I want to add a copy command.  When a user views a record (ItemTemplate) I would like an option to copy that particular record. 

All the data would be copied to another row with the exception of the ID which would need to be incremented by 1.

I wish to to do this with a sql statment as all my other commands (INSERT, DELETE AND EDIT) are all created this way.

If someone could show me how to write the sql statement and how my OnClick="CopyButton_Click" would call the statement I would really appreciate it.

Many Thanks

Hi

Here is an example of code to explain you how do it (try it in sql server management studio)

-- create and fill a temp table for demo purpose
CREATE table #demotable
(
id int primary key identity, -- identity is for the auto-increment
column1 nvarchar(max),
column2 nvarchar(max),
column3 nvarchar(max),
);

INSERT INTO #demotable VALUES('val test 1', 'val test 2', 'val test 3');
INSERT INTO #demotable VALUES('val test 1b', 'val test 2b', 'val test 3b');
INSERT INTO #demotable VALUES('val test 1c', 'val test 2c', 'val test 3c');

SELECT * FROM #demotable -- show what's in the table now

-- copy row with id  1

INSERT INTO #demotable  (column1, column2, column3)
       select column1, column2, column3
	   FROM #demotable t
		where t.id=1

SELECT * FROM #demotable -- the row with id 1 is copied

DROP TABLE #demotable

Hi sam,

Thank you for taking the time to reply.

Your example works perfectly, but for my situation I cant use this copy row
with id 1

The user may select any records to copy and therfor the id number will vary.  My main reason for attempting this is to save time entering data into many fields.

Thank you for your time so far, it’s much appreciated.

of course you have to set the id with a parameter, ‘id = 1′ was for the example

you have to retrieve the id of the record you want to copy and to use it as a parameter for your sql command

you will have to use the SqlParameter class : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx

here is an example : http://www.dotnetperls.com/sqlparameter

Oh I see.  I’ll read through the liks you supplied and have a bash.

Cheers

Last question I promise.

My SQL statement is in my aspx page not in the code behind.  Can my button OnClick event still refrence the sql statment from there?

it depends how it’s defined…

can you post a code sample for the markup and code behind to make it easier for us to understand ?

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:SomeConnection %>"

InsertCommand="INSERT INTO [Table]
([Company], [PartA2], [PartA3], [PartA4], [PartA5], [PartB1], [PartB2], [PartB3], [PartB4], [PartB5], [PartB6], [PartB7], [PartB8], [PartB9], [PartB10], [PartB11], [PartB12], [PartB13], [PartB14], [PartB15], [PartB16], [PartB17], [PartB18], [PartB19], [PartB20], [PartB21], [PartB22], [PartB23], [PartB24], [PartB25], [PartB26], [PartB27], [PartB28], [PartB29], [PartB30], [PartCCarrier], [PartC1], [PartC2], [PartC3], [PartC4], [PartC5], [PartC6], [PartC7], [PartD1], [PartD2], [PartD3], [PartD4], [PartE1], [PartE2], [PartE3], [PartE4], [PartE5], [PartE6], [PartE7], [PartE8], [PartE9], [PartE10], [PartE11], [PartE12], [PartE13], [PartE14], [PartE15], [PartE16], [PartE17], [PartE18], [PartE19], [PartE20], [PartE21], [PartE22], [PartE23], [PartE24], [PartE25], [PartE26], [PartE27], [PartE28])
VALUES (@Company, @PartA2, @PartA3, @PartA4 , @PartA5, @PartB1, @PartB2, @PartB3, @PartB4, @PartB5, @PartB6, @PartB7, @PartB8, @PartB9, @PartB10, @PartB11, @PartB12, @PartB13, @PartB14, @PartB15, @PartB16, @PartB17, @PartB18, @PartB19, @PartB20, @PartB21, @PartB22, @PartB23, @PartB24, @PartB25, @PartB26, @PartB27, @PartB28, @PartB29, @PartB30, @PartCCarrier, @PartC1, @PartC2, @PartC3, @PartC4, @PartC5, @PartC6, @PartC7, @PartD1, @PartD2, @PartD3, @PartD4, @PartE1, @PartE2, @PartE3, @PartE4, @PartE5, @PartE6, @PartE7, @PartE8, @PartE9, @PartE10, @PartE11, @PartE12, @PartE13, @PartE14, @PartE15, @PartE16, @PartE17, @PartE18, @PartE19, @PartE20, @PartE21, @PartE22, @PartE23, @PartE24, @PartE25, @PartE26, @PartE27, @PartE28)"

SelectCommand="SELECT [Company], [PartA2], [PartA3], [PartA4], [PartA5], [PartB1], [PartB2], [PartB3], [PartB4], [PartB5], [PartB6], [PartB7], [PartB8], [PartB9], [PartB10], [PartB11], [PartB12], [PartB13], [PartB14], [PartB15], [PartB16], [PartB17], [PartB18], [PartB19], [PartB20], [PartB21], [PartB22], [PartB23], [PartB24], [PartB25], [PartB26], [PartB27], [PartB28], [PartB29], [PartB30], [PartCCarrier], [PartC1], [PartC2], [PartC3], [PartC4], [PartC5], [PartC6], [PartC7], [PartD1], [PartD2], [PartD3], [PartD4], [PartE1], [PartE2], [PartE3], [PartE4], [PartE5], [PartE6], [PartE7], [PartE8], [PartE9], [PartE10], [PartE11], [PartE12], [PartE13], [PartE14], [PartE15], [PartE16], [PartE17], [PartE18], [PartE19], [PartE20], [PartE21], [PartE22], [PartE23], [PartE24], [PartE25], [PartE26], [PartE27], [PartE28] FROM [Table] WHERE ([NoteID] = @NoteID)">

</asp:SqlDataSource>

Above is my rather large SQL statement.
All my other commands ie. update, insert and delete are serviced from another SQL statement and therefore I don’t really use the code behind apart from showing update complete of failed messages.  Is this not best practise??

I think you will have to set your query in the code behind and do the work by yourself.

Qball777

Is this not best practise??

IMOH it’s better to split the ui from the data access, but it depends of the projects and a lot of parameters (for instance I never used the sqldatasource control)

No problem, thanks for your help. Laughing

Leave a Reply