Solved

Visual studio  2012 datagrid view

Posted on 2016-08-09
11
73 Views
Last Modified: 2016-10-09
In Visual studio  2012 datagrid view when I delete a row the next row should automatically be assigned the deleted row Id. My data resides in My sql . kindly help me with the code.  Example if I have 5 rows 1,2,3,4 ,5 and delete no. 3 row the no. 4 row should become no. 3 and no.  5 should become no. 4 ( auto increment  is not  working).  kindly help me  with code and the procedure.
0
Comment
Question by:Nash Bawz
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 200 total points
ID: 41748858
That is not what autoincrement does in SQL. From Microsoft documentation
A table can only contain one auto-increment field. The numbers assigned to this field will not always be consecutive for the following reasons:

If you delete some records from a table, the numbers used for these records are not reused.
If several transactions are performed at the same time, they will each be assigned a different number. However, if one of these transactions is rolled back, the number that it was assigned is not reused.

If you add an auto-increment field to an existing table, the field automatically generates consecutive values and inserts them into the table. If you enable the AutoIncrement property for a field that already contains data, there must be no zero values in the field.

The AutoIncrement property is designed to always assign numbers automatically. If you want to insert a record, be sure that the value in this field is blank before you insert the record. This is even more critical when you are using the SQL Server Option.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41748864
it is not a usual request to change the ids when a row gets deleted. it is usually lost. there are many reasons varying from performance to data integrity. You normally let the holes in place.
0
 

Author Comment

by:Nash Bawz
ID: 41748897
Thanks for quick reply. My requirement is to collect data and keep on adding new data   as and  when I receive, if some data is irrelevant to me I am required to delete it without any trace of  it in my records. That is the reason I want to renumber my row id as and when I delete any row  in between rows. Kindly let me know the procedure and code both in visual studio 2012 and my sql Work bench 6.3 ( I am using My sql as local host). I am just a beginner in this field and studying in class 12 ( kindly ignore my ignorance) so kindly be simple in your reply. Thankks
Rgds
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 41748917
Not sure about MySQL (BTW your question topics refer to MS SQL which is a different beast).

One thing you need to keep in mind is that you will need to lock down the data while you will be doing your changes (using a transaction) to prevent other people trying to update and delete data at the same time you are trying to update yours!

You also need to keep in mind that all the foreign keys will also need to updated. For example, if you are using that id in another table, you will have to update all of them (unless you used cascading update/delete).

I would use a cursor in MS SQL server to achieve all that. But since you are using MySQL, I can't tell you much more!
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 200 total points
ID: 41748922
You state the following, "if some data is irrelevant to me I am required to delete it without any trace of  it in my records.". That is what will happens when you delete the record. The ID is used by the database for house keeping and is really not meant for user consumption. You really do not want to start reindexing the records.
0
 
LVL 9

Assisted Solution

by:Prakash Samariya
Prakash Samariya earned 200 total points
ID: 41748923
What kind of problem you face if you do not renumber your column?

In general good practice, once you made entry in DB with some keys always remains with the row. If you delete row means it will delete key as well. Relation between tables, Sorting, Indexing and many other things performs based on this!

I advice to keep as it is!
0
 

Author Comment

by:Nash Bawz
ID: 41751485
is there any way to reset the table Id number after I delete any in between row. I do not want to delete row Id,  I just want to delete row contents and the next subsequent row contents should move up so the row Id is always sorted out in sequence.  I. e. 1,2,3,4......
0
 
LVL 9

Accepted Solution

by:
Prakash Samariya earned 200 total points
ID: 41751559
There is no direct way to do it! you can copy table data into TempTable, truncate table, reset identity and re-input data into table.

Please check code below: (please also ready the comment above each sql statement to understand the logic)
/*Store data into TEMP table*/
SELECT * 
INTO #TEMP
FROM [TESTSTAGE].[DBO].[PRODUCTDETAILS]

/*Set identity column ON (if not) in YOUR table*/
SET IDENTITY_INSERT [TESTSTAGE].[DBO].[PRODUCTDETAILS] ON

/*Truncate all data from YOUR table*/
TRUNCATE TABLE [TESTSTAGE].[DBO].[PRODUCTDETAILS]

/*Remove identity column from TEMP table*/
ALTER TABLE #TEMP DROP COLUMN PRODUCTID

/*Set identity column OFF in YOUR table*/
SET IDENTITY_INSERT [TESTSTAGE].[DBO].[PRODUCTDETAILS] OFF

/*Insert data from TEMP table into YOUR table*/
INSERT INTO [TESTSTAGE].[DBO].[PRODUCTDETAILS]
SELECT * FROM #TEMP

Open in new window

You can make one SP in DB and call that after your deletion of record!
1
 

Author Comment

by:Nash Bawz
ID: 41751657
Thanks a lot Prakash Sir for a valuable input. I will try and come back to u if any further clarification needed.
Rgds
1
 
LVL 9

Expert Comment

by:Prakash Samariya
ID: 41763569
Thank you Nash for you comment, we are glad to help you!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
Error when creating a table from a function 6 18
Parse this column 6 27
SQL Recursion 6 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question