如何使用唯一的，预编号的参考密钥将新记录插入SQL dbase? [英] How do I insert a new record to SQL dbase with unique, prenumbered Reference Key?

问题描述

大家好.我必须插入一个订单表单，在其中必须插入诸如Order_Num，Order_Date，Product_ID，Order_Amount等之类的字段.我的问题是Order_Num(这是记录ID)的格式是这样的:< P1109-0001>其中:

Hi all. I have to insert an Orders form where I have to insert fields like Order_Num, Order_Date, Product_ID, Order_Amount, etc... My problem is that the Order_Num (which is the record ID) is preformatted like this: <P1109-0001> where:

"P" constant character (PurchaseOrder); "11" is the current year '2011'; "09" is the current month (Sept); "-" is constant character (separator); "0001" is the series number.

每当将新记录保存到SQL表"Orders table"时，此序列号就会递增.

在VB6中，我可以遍历表以搜索重复的ID，当没有重复的ID时，将插入具有该ID的记录.

您能在VB.net上指导我如何做吗?我也不熟悉SQL中的stored_proc，因此我通常使用VB进行编码.

在此先谢谢您.

This series number increments whenever a new record is saved to the SQL table "Orders table".

In VB6, I am able to loop through the table to search for a duplicate ID and when there is no duplicate, the record with that ID is inserted.

Can you please guide me on how to do it in VB.net? I am not also familiar with stored_proc in SQL so I usually code using VB.

Thanks in advance.

推荐答案

您应该在数据库中有另一个表，用于存储您的序列号.然后，只要您想插入新的采购订单，创建唯一的ID，增加序列号并将其全部保存到数据库中，就可以检索该编号.

希望这对您有帮助

You should have another table in the database that stores your series number. Then you can just retrieve this number whenever you want to insert a new purchase order, create your unique id, increment the series number and save all to database.

Hope this helps

存储过程将表名，列名作为输入参数并重新运行下一个数字
员工表将返回输出为E11090001

store procedure takes the tablename, columnname as input parameter and retruns the next number
Employee table will return output as E11090001

create proc GetNextNumber
@tableName nvarchar(100),
@columnName nvarchar(100)
as
declare @strSql nvarchar(1000)
set @strSql = ' select ''' + upper(right(@tableName,1)) + ''' + 
		left(convert(nvarchar,GETDATE(),12),4) + 
		right(''0000'' + convert(nvarchar,max(right(' +@columnName + ',4))+ 1),4) 
	from ' + @tableName
exec sp_executesql  @strsql

韦恩，当年份或月份更改时，序列号重置为零.例如，订单记录< P1108-0326>日期更改为9月后，

Hi Wayne, the series number resets to zero when the Year or the Month changes. For example, the Orders record <P1108-0326>

(2011-Aug, series# 326)

预计将设置为P1109-0001. 01(2011-Sep-01).

will be expected to set to P1109-0001 once the date changes to September 01 (2011-Sep-01).

这篇关于如何使用唯一的，预编号的参考密钥将新记录插入SQL dbase?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！