how to autogenerate value in a column using Update sql statement.

 have value like 0809000001 and would like to autoincrement this field starting with this value.
Without reading each individual record. The value should be (last no +1) for each row.
The column datatype is varchar.

0
Bhushan
1/6/2009 8:34:41 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
539 Views

Similar Articles

[PageSpeed] 2

Seems like a perfect scenario for an int (or bigint for large numbers) identity field instead. You can seed the identity field to start at 809000001.

If you really need the 0 at the beginning you could always accomadate this when selecting data by CASTing identity column to a varchar and padding with extra 0's as needed.

An alternative would be to use a trigger on the table to automatically populate this column based on SELECT MAX(CAST(columnName AS varchar(10))) + 1 ... Not very efficient though! Or similarly use a user defined function called from the default value or binding for the column.

Hope this helps ;)


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
1/6/2009 9:00:47 AM
Reply:

Similar Artilces:

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Can variable be used in SQL UPDATE statement in VB.NET
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery()   When I don't use a variable after SET statement, everything work fine. This code wor...

SQL Update Statement Set Field Value to Column Default
Is there a way to set a field value to the column default in an update statement? Eg. UPDATE Table2 SET field1 = DefaultValue where DefaultValue is the field1 column default in the table definition. The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, ...

when to use sql update and sql insert
I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?Jeremy HusonSenior Network EngineerCarden Computers - PC Repair Brighton Hi,   Generally in such a situation we normally differentiate  the records for updation and inserion through the...

Executing the i-sql statements using i-sql interface.
I am writing the c/c++ interface which will execute i-sql query statements to retrive information from the isql. It involves parsing the isql output to get the required information objects ( database, devices, ...etc). This parsing string method seems to be not appropriate, as the format of the isql statement might change in future release of ASEs. I am wondering is there any otherway I can access the information(like querying info of databases, devices, ...etc) from sybase server from "C/C++" program in structural manner??. Is it possible to get the return status(succe...

Viewing a SPIDs last SQL statement using sqltext()...though sometimes only partial SQL statements are shown
The dbcc sqltext() command seems to allow you to view the last SQL statement issued by an active SPID on the server. However I've noticed that sometimes only part of the SQL statement is returned - does anyone know why this might be, and if so whether it's due to a server configuration. Anyway, here's my notes on how to view the last SQL statement processed: ** How to see the last SQL a SPID has processed -- dbcc traceon(3604) Directs trace output to the client, rather than to the error log 1> dbcc traceon(3604) 2> go DBCC execution completed. If DBCC pr...

Using Gridview for delete, update and select using seperate sql statements and parameters
Hi everyone, I have a gridview which is bound to a sqldatasource which runs a stored procedure using userid as a parameter.  This then puts into a gridview all the surveys that that user has created. This all work fine, the problem is that when I put a delete button on the grid it expects the same parameter that the select statement uses which is the userid.  However the stored procedure which deletes from the database uses surveyID as the parameter as i need the user to be able to choose which survey they want to delete. My question is how do you have a delete button which ru...

How to use session value in sql statement?
In my global.asa, I set a session value for UserID.   How can I reference that session value in subsequent sql select statements.  My code is below, and the error I keep getting is Must declare the variable '@Session'.  Session isn't the variable I want to use in the statement.  I want to user "UserID"   comm = New SqlCommand("SELECT DefaultID, SuID, AttID FROM tDefaultSubmitter where SubmitterID=@Session(UserID)", conn)Never make important decisions on a Monday! Try this:   comm = New SqlCommand("SELECT Default...

Using Linq to SQL to insert into an SQL Server XML column
I'm getting an error on this line: ' auditData.PCAuditDataXML = Xdoc.Element("Model") 'Can anyone see where I'm going wrong here?    Private Sub insertDataIntoAuditDataTable(ByVal userName As String, ByVal computerName As String, ByVal Xdoc As XDocument)        Dim ADDC As AuditDataDataClassesDataContext = New AuditDataDataClassesDataContext        Try            Dim auditData As New AuditData_ad     ...

Using DropDownList values in an SQL statement
Hello, I have been using Visual Web Developer 2008 to create a simple dynamic web page (at least I thought it would be simple).  I used to use ColdFusion, but my requirements shifted, and I need to use ASP.NET.  So, I'm trying to learn enough to convert over.  This is what I have so far: I have 3 DropDownList boxes populated by my database.  Essentially, these lists will act as filters for my main SQL statement result.  However, I do not know how to tie the list box choices into my SQL statement.  Let me show:  1 <html xmlns="http://www.w3...

using sql statements in vb.net
I really need help with this.............. I cannot figure this out........whenever the user selects something from the dropbox i need to show only one record from the database that has one field the same as that statement in dropbox...........how do i do that? how do i use sql statement inside of vb.net code??? I would really aprreciate any kind of help........ Thanks...... Lara One possible solution is to create a SQLDataSource in your form.  You would then set your query parameter to be the value of selected dropdown box item.  And you can bind a grid view or most any othe...

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

update multiple columns in one sql statements
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax. I have a table with transactions - with tran types as the key. in this example, types = A,B,C ,D. in this first example I am updating the sum of QTY to value t_A based on tran types =A. can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values. Create table #t_reconcile( t_lot_number int not null, t_A ...

Web resources about - how to autogenerate value in a column using Update sql statement. - asp.net.sql-datasource

jessicabowman (@jessicabowman) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

The Consultants: Inverting Your Control
Hits a little close to home. IoC is much easier when its the initial design. I’ve been burned on this where *half* of the conversion from monolithic ...

Tutorials
The list below consists of tutorials for The Sims 3 ( + expansions). Please make a note that these are just links to other sites who have the ...

Silicon Valley's top startup factory has a clever way to make sure investors follow through on 'handshake' ...
... When an investor clicks on the handshake button, they can specify if they have already settled on the terms with the startup and it autogenerates ...

Google open-sources XML-alternative Protocol Buffers
Company releases software for encoding data in multiple formats in a single binary format for network communications or storage.

News You Can Bruise for 2005 March
(4) Tue Mar 01 2005 09:29 PST World Famous Ultra Gleeper : So what did I find out today but that the Ultra Gleeper , which still needs to be ...

Google Search adds support for hashtags, pulls related info from Google+
... rail display relevant Google+ posts that were either shared publicly or to you. Even if you're not an avid user of the #, since Google+ autogenerates ...

Chrome Web Store - CSS Grady
Generating CSS Gradients was never this easy,autogenerate multibrowser css3 code in a snap

Laser-Cut Clock Kicks Your CAD Tools to the Curb and Opts for Python
... In a world where many fancy CAD tools can auto-generate gear models, [Lawrence] went back to first principles and wrote scripts to autogenerate ...

Migrate - MSHC Migrate Utility
MSHC Migration Utility

Resources last updated: 11/24/2015 10:46:06 PM