Insert Value in Database in required Format? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Insert Value in Database in required Format?

Author

Topic

nickjack
Starting Member

34 Posts

Posted - 2008-11-15 : 02:33:04

int Max_Id = 0;
string OT = "";
string Final_TypeID = "";

try
{
con.Open();
string sql_Query = "select isnull(max(counter+1),0)c from Authors";

SqlCommand cmd_Id = new SqlCommand(sql_Query, con);

cmd_Id.CommandTimeout = 9999;

dr = cmd_Id.ExecuteReader();

dr.Read();

Max_Id = Convert.ToInt32(dr["c"].ToString().TrimEnd());

if (Max_Id == 0)
{
Max_Id = 1;

}

if (Max_Id <= 9)
{
OT = "OT000";
Final_TypeID = OT + Max_Id;
}
else if (Max_Id > 9 && Max_Id <= 99)
{
OT = "OT00";
Final_TypeID = OT + Max_Id;
}
else if (Max_Id >= 100 && Max_Id <= 999)
{
OT = "OT0";
Final_TypeID = OT + Max_Id;
}

else
{
OT = "OT";
Final_TypeID = OT + Max_Id;
}

}

And In try Block i wrote insert command the value is inserting.Value is coming from a TextBox where i also used Split() function. So if user enter value like 1,2,3,4 the value will insert in Database like

col1 col2

OT0001 1
OT0001 2
OT0001 3
OT0001 4

But i need Like this

col1 col2

OT0001 1
OT0002 2
OT0003 3
OT0004 4

How is it Possible

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-15 : 16:38:35

I think you are doing things very wrong.

first of all, change the above to


int Max_Id = 0;
string OT = string.empty;
string Final_TypeID = string.empty;

To assign your csv files to a variable you can use an array which is done as thus below.


string textboxvalues = TexBox1.Text;
string[] Final_TypeID = textboxvalues.Split(',');

But I would prefer you try


string textboxvalues = ','+TexBox1.Text;
textboxvalues = texboxvalues.replace(',',',OT00');

Then you can pass the value which will be in this format OT0001,OT0002,OT0003,OT0004 to your Stored procedure, and I would advice you use a split csv function to insert the values into your db. Much neater this way, There are many split functions in this forum, do a search.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-11-16 : 21:56:52

First, to format an integer variable i as "OT0001", "OT0002", etc, you can just do something like this:

String.Format("OT{0:0000}",i)

However, more importantly, I strongly recommend that you read this article:

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources