.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Increament the string value

Posted By: Geetha     Posted Date: October 06, 2010    Points:2   Category :Sql Server
hi,

I have the ID column .The values should be incremented as pt0001
and pt0002 like this ...
Wat is the Query to do like this ??????

Responses
Author: Asha.MP             
Posted Date: October 06, 2010     Points: 5   

HI...

In the sql server there is an identity column,u can set that column as identity column. While inserting the sql server itself increment that value automatically.



Thanks and Regards,
Asha.mp
Author: Syed Shakeer Hussain             
Posted Date: October 06, 2010     Points: 5   

Hi,

As Asha told keep the Identity column in a Table.
Retrive the last Value of Identity column and append it With 'pt'.
Use the Below Code to get Last Identity value from a Table.



String strQuery="select MAx(ISNULL(col_Id,0)+1) from tableName";

SqlCommand cmd=new SqlCommand(strQuery,conn);

SqlDataReader dr=cmd.ExecuteReader();

//dispaly the value in Label1 as 'pt001'

label1.Text="pt"+dr["col_Id"].ToString();







Author: Santosh             
Posted Date: October 06, 2010     Points: 5   

Hi Geetha

There is no DB query to auto generate the ID which is a string data type.
so frond end,u need to generate the the id and after that u need to insert..

so i m giving u the code according to ur requirment to generate ID in vb.net

just try the below code and let me know if u r facing any problem...

COde:

Private Sub FillSLno()
'pt0001
Dim GenID As String = ""
Dim DBID As String = ""
Dim ID As Integer
Dim objDS As New DataSet()
Dim objda As New SqlDataAdapter("SELECT max(ID) as 'ID' FROM Test", conn)
objda.Fill(objDS)
If objDS.Tables(0).Rows.Count = 0 Then
ID = 1

Else
DBID = objDS.Tables(0).Rows(0).Item("ID").ToString
ID = CInt(objDS.Tables(0).Rows(0).Item("ID").ToString.Substring(2, 4)) + 1
End If
For i As Integer = 0 To (4 - ID.ToString.Length) - 1
GenID = GenID + "0"
Next
GenID = "pt" + GenID & ID
'txtSlno.Text = CInt(objDS.Tables(0).Rows(0).Item("COUNT").ToString) + 1
End Sub



Regards
Santosh





Author: Deco             
Posted Date: October 07, 2010     Points: 5   

Geetha,

Have an identity column, Starting from 1

while showing the data in the front-end

you can concatenate what you want along with that value

Regards,
Deco


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend