1

i want to store auto increment id with string "SDMP" HOW TO DO THAT? when id increment it will be like SDMP1, SDMP2,....Like that

"Insert concat('SDMP', m_id) as id from merchant where m_id='" + id + "'";

I WANT TO SHOW DRIVER ID LIKE SDMP1 IN GRIDVIEW BUT I WANT ID IN SDMP1 FORMAT

Shital Ghadge
  • 11
  • 6
  • you want to update ID in the database as SDMP1 or just while display in the gridview column as SDMP1 ?? – Mangesh Auti Jul 02 '19 at 07:02
  • i want to store Auto increment ID with SDMP and after that search that id in gridview – Shital Ghadge Jul 02 '19 at 07:11
  • 1
    If the code will always be SDMP then what's the point of it? It doesn't make it any more unique. You can't store that in an auto-increment column anyway. Just concatenate it on to the stored ID whenever you want to display it. You could do that either in a SQL select query or in some asp.net code – ADyson Jul 02 '19 at 07:16
  • if you have solution please tel me i cant find out that solution i use MYSQL and i want query in mysql to store in database – Shital Ghadge Jul 02 '19 at 07:20

1 Answers1

0

If you want to update the existing table (where m_id is int and auto increment) then you can use this(before update query you need to change datatype of m_id)

ALTER TABLE merchant 
MODIFY COLUMN id varchar(50);

then use update query

UPDATE merchant set m_id=CONCAT("SDMP",m_id);

OR

If you want to insert m_id as SDMP1, SDMP2 autoincrement then you can use this

  • Get Last ID
  • Get the INT part within the string
  • Increment the value
  • Concatenate
  • Save to DB

moreinfo

INSERT INTO merchant  
SELECT CONCAT("SDMP",SUBSTRING(m_id,5)+1),"abc4" 
FROM merchant order by m_id desc limit 1;

NOTE: if no row in the DB then manually insert SDMP1 as the first row in DB

OR

Keep thing simple, you keep m_id as int auto_increment in DB and while retrieving data from DB concat with SDMP CONCAT("SDMP",m_id) and while searching in DB take integer part from gridview of m_id column

Mangesh Auti
  • 1,123
  • 1
  • 7
  • 12
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/195882/discussion-on-answer-by-mangesh-auti-how-to-store-auto-increment-id-with-string). – Samuel Liew Jul 02 '19 at 23:09