Automating the creation of a database

Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]

> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser

> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Automatically Generating PDFs With Reports Requiring Parameters

Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql

Automatic PDF/TIFF generation and saving?

Ello folks...
Question for you all - I'd like to make a call from a webpage (asp or aspx)
that will call up a report with a given set of parameters, format it as a pdf
or tiff file, and save it to a location. Any ideas?
I know I can use the /ReportServer/ instance to directly call the report,
pass it the parameters I need, and render it out as a different format, but
it's the saving part that's throwing me.
Thanks!
- TerosTeros,
How about a subscription instead, or does it have to be on request?
A subscription can do this for you, but rendering on demand will always
ask.
Sorry, I don't think it can be done.
Chris
Teros wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx) that will call up a report with a given set of parameters,
> format it as a pdf or tiff file, and save it to a location. Any
> ideas?
> I know I can use the ReportServer instance to directly call the
> report, pass it the parameters I need, and render it out as a
> different format, but it's the saving part that's throwing me.
> Thanks!
> - Teros|||I have seen someone say you can save the output stream as a file . I looked
for a previous post but didn;t find it.
The other way is to create a data-based subscription... in the subscription
row you can choose the filename and path...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:1BCA0EE4-E9B9-4BE9-8F35-55A0EB09FD96@.microsoft.com...
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx)
> that will call up a report with a given set of parameters, format it as a
> pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format,
> but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Check out the web method - RenderStream - you can render to a stream then
save the stream to a file
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Teros,
AFAIK there is no way to do this from URL access. We do what you're doing
by calling the web service and saving the stream off to a file.
Ted
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Thanks for the input - the report does need to be rendered on demand, so the
subscriptions wouldn't work. I'm playing with the renderstream now to see
what I can play with. It's looking pretty hopeful thus far.
Thanks!|||For PDF or TIFF files, you use the Render() method. RenderStream is used for
things like images on an HTML page.
Ted
"Mary Bray [SQL Server MVP]" wrote:
> Check out the web method - RenderStream - you can render to a stream then
> save the stream to a file
> "Teros" wrote:
> > Ello folks...
> >
> > Question for you all - I'd like to make a call from a webpage (asp or aspx)
> > that will call up a report with a given set of parameters, format it as a pdf
> > or tiff file, and save it to a location. Any ideas?
> >
> > I know I can use the /ReportServer/ instance to directly call the report,
> > pass it the parameters I need, and render it out as a different format, but
> > it's the saving part that's throwing me.
> >
> > Thanks!
> > - Teros|||Any luck Teros? I'm trying to do the same thing (I think). We have a report
that will be called from a third party application, and when it is called we
want it to create a pdf to a specific location, and then close. I'd be very
interested in knowing how you did it.
Michael C.
"Teros" wrote:
> Thanks for the input - the report does need to be rendered on demand, so the
> subscriptions wouldn't work. I'm playing with the renderstream now to see
> what I can play with. It's looking pretty hopeful thus far.
> Thanks!
>

Automatic Data Fill-up

Hello guys!

I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition.

I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.

I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).

How do I accomplish this kind of dynamic filling up of fields? Thanks a lot!

When the client clicks the button ,you can get client ip using the following code:

Dim strClientIPAs StringstrClientIP = Request.UserHostAddress()

Then you can save the strClientIP into your table:

 
Dim connAs New SqlConnection("Data Source=.\yourserver;Database=pubs;Integrated Security=SSPI;")Dim cmdAs New SqlCommand("INSERT INTO yourtable(clientip) SELECT @.clientIP", conn) conn.Open() cmd.Parameters.AddWithValue("@.clientIP",strClientIP)Dim iAs Int32 i = cmd.ExecuteNonQuery() Response.Write(i.ToString() +" row(s) has been written to clientIP") conn.Dispose()
 

Automate setting permissions to databases (SQL 2005)?

Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>I set a long list of granular user database permissions with every reinstall
of my development database and I would like to automate this work with a
script instead of doing this manually in SQL Server Management Studio.
Is there a security settings export to file option? Or maybe some script
samples that show how to set user permissions?
I saw this example, but it requires both old and new databases to be running
at once. I reinstall the same PC repeatedly so this doesn't work for me:
http://www.sql-server-performance.c...ion_scripts.asp
Thanks!
Adam|||Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>

Auto update to sql server tables

I'm changing data storage for an asp.net project from MS Access to Sql Server. I've got the web site working, but I need to update the sql server tables with data from our Oracle db daily. What is the "best" way to do that?

I've read about DTS, but have never done anything like that. Would it be worth the time and effort to study? (So far I've created a package, with the import wizard, that doesn't work & I don't have the authority to delete :-)

I know I could create a dataset with my Oracle data and use that to update sql server. But is there a way to schedule an aspx to run authomatically? Would this affect performance? The sql server db isn't very big (30-40,000 records), but the Oracle db is & I need to do quite a bit of manipulation to the data.

This is new to me & I'm don't know what I should be searching for to find help. And if there is a more appropriate place to post this question, please let me know.

Thanks.Yes. DTS is one good way to go and it is very easy to do.

Do you need to purge the data in your SQL before each load? Then the account has to have the right.

You can schedule the DTS to run at whatever time you want.

auto insert fro date in database ..how?

how i make date field in database auto updated,some one told me to use '" Now "' in insert command, but it dos't work, i think it's for asp. regardsYou need to use either GetDate() to get the local machine date/time or GetUtcDate() to get the date time in UTC (GMT).|||You can set up a column with a default value of Getdate() and not worry about manually inserting a value into it, or even use the timestamp column.|||hey guy's thanks for information it's working good. now the getdate() for date, what alse i can use like getdate() to import information??
regards|||

Please mark the post as answered and thank you for the appreciation !

auto incrementing->Updating values in two related tables :Help!

hi there,
i am new to sql server database.i am doing small projects rightnow using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :

i have two tables ....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted intable 2 . so for eg if i have three records 1 ,2 and 3 ...then idelete 2 in table 1 ...i will have 1 and 3 in both table 1 and table2...now i want 3 to become 2 in both table 1 and table 2...so that idont have empty space between two student id's 1 and 3. so this is myproblem...if any one can help me out with suggestions pleasedo.
thank you all......
ahmed_ind
if you want my advice ... this is not a good solution or methodto follow .... you should not really care about the space ...once you create ID for a record and delete it, you should notreally care about it to have stabled data not to have confliced IDs.
if your concerns about the ID number that you will reach in 1 year ..you can avoid that when you create the ID columns and assign the datatype that has the range for what you know it will enough for you...... if i were you i will not bother myself with what are youlooking for.
By the way this is my idea from my experience and what database expertsthink..... but if some one has different opinion and beleive there isa better technique ... you can advise !!
|||hi fadil.....thanx for u r suggestion.
i wanted to have automation of operation at the background ; i.e. in the database (sql ) ...so i used identity function toauto-increment values of student id by one ....then i wanted to deleteby performing auto-decrement function.....so thats how i camewith the idea, i jus wanted to learn tough things...ok any way thanxagain for ur suggestion and if u have any way of solution for myquestion please do reply..thank u..
ahmed
|||look at this case and see what i mean
ID Name
1 Ahmed
2 John
3 Ali

if you deleted 'Ali' the decrement would be ok as it is the last one on the list but if you delete 'Ahmed' you will loose the primar key integritiy for John and Ali as they will be 1 and 2.....i beleive this is not a good methodlogy to do... you might injure your data !!|||hi fadil,
i get u...i understand what umean...ok then my logic is not a good one then...may be i have to trysomething else than auto decrementing.....
thanxanyway for the suggestions : i will take them and follow them now andin my future...

............ahmed

Auto Incrementing field

I have a asp.net form which is for orders with a field for numbers in it.
What I want is when a user raises a new order the Order number field will
be populated automatically with the next order number. I want this order
number to be incremented on the database table. The order number is of the
form 05/001 and when the next order is raised it will be 05/002. 05 is the
year.
Anyone any ideas how to do this.
Thanks.
Message posted via http://www.webservertalk.comHi
I'd recommend you to create a table with the following structure
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate DATETIME
)
Now you can easily to extract the order number along its orderdate.
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||here OrderID can me an IDENTITY column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Uri Dimant" wrote:

> Hi
> I'd recommend you to create a table with the following structure
> CREATE TABLE Orders
> (
> OrderID INT NOT NULL PRIMARY KEY,
> OrderDate DATETIME
> )
> Now you can easily to extract the order number along its orderdate.
>
> "macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
> news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>
>|||Hi,
Look into the identity property in books online.
Thanks
Hari
SQL Server MVP
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||Why?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:F05619B5-D96F-405E-B385-466995B09A71@.microsoft.com...
> here OrderID can me an IDENTITY column.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Uri Dimant" wrote:
>
message
it.
will
order
the
the|||Although it has been suggested, an Identity column would not satisfy your
requirement of "YY/SSS".
I would recommend a stored procedure:
PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
The procedure would generate a new Order_Num, insert a row into your Orders
table (and whatever else tables), and return the order number as an output
param.
If the Order_Num needs to be known when an order is first created, run the
procedure first, fill in your field, and set the Order_Status to incomplete.
When the submit the order, change the status.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"macca via webservertalk.com" wrote:

> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||I think this may cause concurency issue.
You can use an IDENTITY column plus another YEAR column (varchar).
whenever you show it to user, you just need to concat these 2 columns.
However, since YY is always current year, you can choose not to store this
value unless this column can be changed in the future.
Hope this may help.
Thanks.
Leo Leong
"Alex Papadimoulis" wrote:
> Although it has been suggested, an Identity column would not satisfy your
> requirement of "YY/SSS".
> I would recommend a stored procedure:
> PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
> The procedure would generate a new Order_Num, insert a row into your Order
s
> table (and whatever else tables), and return the order number as an output
> param.
> If the Order_Num needs to be known when an order is first created, run the
> procedure first, fill in your field, and set the Order_Status to incomplet
e.
> When the submit the order, change the status.
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "macca via webservertalk.com" wrote:
>

Auto generate IDs in MS SQL SERVER 2005

Hello,

I m creating forms in ASP.Net 2005 using C# language.

I'musing Microsoft SQL Server 2005 and my IDs are in A001, A002, A003...and so on. Howcan I auto generate this IDs? Like A001 +1=A002? Please help...

In SQL server 2005 which datatype i should select and how can i code in ASP.NET with C#??

On button click event the data is inserted and been shown onthe grid..

Thanks

identity columns use only a few datatypes such as the numeric operators (tinyint, int etc.), and GUID (for unique id's). AFAIK, you can't generate ID's in that form. I would recommend that you either use an integer column, and create a combined field e.g. "select 'A' + nameofidcolumn as businessid" , or else don't use identity columns and write the value for your required id into the table directly via custom code.

Cathal