DataSource Controls :: Generate A Unique Ref Number And Insert Into The Database
Jul 5, 2010
I am generating a unique Ref number using this sql statement: SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0) as REF Subsequently, I need to insert this REF into sql database table in stored procedure. Here is what I did in my stored procedure:
USE [iBankRecords]
GO
/****** Object: StoredProcedure [dbo].[stp_addPayee] Script Date: 07/05/2010 21:57:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[stp_addPayee]
(
@CUSTID char(10),
@PAYEEACCNUM nvarchar(20),
@PAYEEACCTYPE char(10) ,
@PAYEENAME char(20),
@PAYERINITIAL char(20),
)
AS
DECLARE
@ReturnValue int
INSERT INTO PAYEE (CUSTID, PAYEEACCNUM,PAYEEACCTYPE,PAYEENAME,PAYERINITIAL, REF)
VALUES (@CUSTID,@PAYEEACCNUM,@PAYEEACCTYPE,@PAYEENAME,@PAYERINITIAL, REF)
IF @@ERROR<>0
BEGIN
PRINT 'ERROR'
SET @ReturnValue=1
RETURN @RETURNVALUE
END
ELSE
BEGIN
SET @ReturnValue=0
RETURN @RETURNVALUE
END
May I know how do I have generating a unique ref num and inserting the REF into the above statement?
I want to generate a unique random number everytime a user submits a form. As the form is submited the data of the form should be placed in a database and a unique random number should be generated so that the user can later use this unique random number to reterieve his details from the database that refer to his unique random id. The unique random number should be atleast 9 digits long.
I need to generate a Unique Reference number that has 7 digits.
It should be formatted in the following way:
1st: B (Ball), G (Gift), C (Cat)
2nd: represents year of booking A(2010), B(2011),C(2012), etc
3rd: represents the month of booking J(Jan),F(Feb),M(Mar),A(Apr),Y(May), U(Jun), L(Jul),G(Aug), S(Sep),O(Oct),N(Nov),D(Dec)
4th: days of booking A(1st) - Z(26th), 1(27th) - 5(31st)
5,6,7th: These are counters for the number issues each day. Each can be A-Z,0-9 giving a total of 46,656 combinations. So 0 is first, then 1, 2, 3, 4, 5, 6, 7, 8, 9, A-Z, then 00 to 0Z, then 10 to 1Z, then 20 to 2Z, etc
Now , the 1st Digit is a Character that I generate based on a condition and that shouldn't be a problem.
I have an easy problem for the experts here...been a few years since my last development work. I have an appl that will track complaints. When an administrator logs in, they go to the main admin.page. From there, they can create a new complaint record-here is where i'm stuck and am looking for a better way. When the admin loads the create.aspx page. i need a way go generate a unique number that i can insert into the db. What is the best way to generate the number...i don't care whether it's numerical or combo, just unique and durning postback the number does not change...the number will be given out as a reference back to the complaint... This seems pretty standard but I can't think of a way that will not duplicate numbers and am looking for best practice with low
For example: INSERT INTO table (col1, col2, col3) VALUES ('value1','value2,'value3')But I would only like to insert the row if value3 isnt already present in col3 in some previous row. What is the best way to do that?And the insert command is run 50+ times in a loop (with different values each time for value3)
I have developed a Portal (Request submission) which sends an Email and also it updates all values to the Database. What ever values gets updated to the database, will be sent as an email message to an address. It works fine. I need a Unique number to be generated for a Request submission which has to be sent in the Email message and also it has to be updated in the database. I use SQL Server 2005 and Framework 2, C# laungauge.
How generate the unique no. 1,2,3 and so on .... on button click of each new user ..
the code mentioned below is a readwrite coding in vb.net ...
but the problem is it generate the same id for different users on button click event... but i want the no. of times button clicked the new ids will be generated
The scenario is that I wanna insert a record to the database via INSERT query and I need to user the identity number of this record in the INSERT query.
But I don't know how to achieve this.
Here is my table structure and code for your better understanding.
There are three columns in the Table: categoryID, categoryName, categoryPath. I need the path based on the categoryID which is an identity int column.
I am a beginner in ASP.NET using VB Code. I want to generate a Unique reference no by concortinating three column fields.Example:
Column unique reference no (System Generated) - 10 Column with Product Code (User Entry) - APPLE Column with Product Title (User Entry) - FRUITS Unique ref No = FRUITS/APPLE/10
I want to concatenate this three fields to the column Unique Ref No.
generating random numbers in texbox and then submit to database. code should check if the generated number already exist in database before submiting to database.
I am having a field called Semester, i will enter it as 1 initially. Then it should automatically increase 1 semester every six months till sixth semester in the database (sql server 2005) is it possible?
I've got a MS SQL (Express) database. its quite complex and uses a lot of tables. Is there a way to generate a SQL script for creating the complete database, but not the content of it.
I like to copy the database. Right now i copy the database. After that i need manually to clear all fields. There are also some relationships, so its a lot of work to clear all of it manually.
i am using sql 2005 db. i have created a table called attendance where i store employee attendance, i want to separate number of absence for a particular employee and particular month. It have to show number of absence of a particular employee and particular month. Also i have to count no of absence. for eg: if an employee absence for two days for a particular month, it have to display 2 count.. how to do this? also how to write the store procedure for this?
I now want to count the number of returned rows so that I can say:IF rows_returned = 1 then login else deny I think you get the idea.P.S. I know this is not the most secure method of login but I do not want to use the ASP.NET configuration manager as I like to understand what is happening. I am starting off very simple and I shall work up from there.
in above SP I define that if users tell or Mobile number was in Estate_free ro Black_list table it doesn't insert data into database and show 'You can just register 1 Time' and if there wasn't their mobile or Tell in those table it insert their data into Estate_free table and show 'You have been registered'.
problem is that when I enter data in database with new mobile number it didn't insert data in database and show 'you can just register 1 Time'.
iI am using Two textboxes and one button.one textbox is for Fromdate and Another textbox is for Todate.i am selecting date from textboxes using Ajex calenderExtender.i want to insert every date(Fromdate to Todate) in database on button click.Example Like:i select 12/30/2010 (MM/DD/YYYY) in FromDate textbox and 01/05/2011 (MM/DD/YYYY) in Todate text box. Now i want to insert these dates
It's probably something small but I can't seem to find what went wrong. I am trying to insert information into my database. It goes through the code just find but it doesn't insert into my database. Here's my code:
I m uploading a text file. I want to read that text file line by line and I want to save that data into database if there are no errors in that text file. I have 5 different columns in database and I want to take those 5 columns data in the text file and insert into respective columns.
For this, which is the best way to do. Can we read and insert data into respective fields in no time.
I want to insert into database with the following code. I want the Appliers instead of Choice to be inserted. Now this code insert the Choice field only