determine if autoincrement is set in a table, form asp.net

I want to access sqlserver table properties from asp.net .

How do i know that a table defined in sqlserver has autoincrement field or not.

Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.

I am not being able identify this property from codes.
Please help.
thank you

Hey,

When you connect to the database, you can't know that from ADO.NET; you have to recreate identities manually on the ADO.NET side. However, if you have SQL Server 2005 API's installed, you can use the SMO objects to connect to a 7.0, 2000, or 2005 database and find this information out.

|||Thanks brian

I am using sql 2000 and SqlDataAdapter object

I think the following code should work but its not working.
any ideas

Dim dsAs DataSet ' = ( got dataset from a table)
Dim hasAutoIncrementAsBoolean =FalseForEach columnAs DataColumnIn ds.Tables(0).Columns If column.AutoIncrementThen

hasAutoIncrement =

True
ExitFor EndIfNext|||

Hey,

The problem is there isn't any autoincrement returned back from the dataset; you have to establish that programatically. So you need to do:

dataSet.Tables(0).Columns(0).AutoIncrement = true
dataSet.Tables(0).Columns(0).AutoIncrementStep = 1

Etc. You have to set this up by yourself; it doesn't pull it back for you.

|||

SELECT *,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITYFROM INFORMATION_SCHEMA.COLUMNS

|||

Which would lead you to this query:

SELECT COUNT(*)

FROM (

SELECT

*,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITY

FROM

INFORMATION_SCHEMA.COLUMNS

) t1

WHERETABLE_NAME=@.MyTable AND IS_IDENITITY=1

|||Thank you very muchMotley

exactly what i was looking for.

Thanks

designing data entry form with scope_identity .....help

hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:

experience
First job.textbox
Secondjob.textbox
Thirdjob.textbox

i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ...
which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @.@.identity to get the ID from my first table(personel)
which is autogenerate number. It works fine with Firstjob table but not secondjob table.

Generated error message:

(1 row(s) affected)

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 6

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

INSERTINTO dbo.Personnel

(Name)

VALUES

('Jon')

insert

into Firstjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Teachier,'Gam','7thn',scope_identity())

insert

into Secondjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Musician','KKSB','8thn',scope_identity())

any suggestions...

Declare @.id1int, @.id 2intINSERT INTO dbo.Personnel (Name)VALUES ('Jon')SELECT @.ID1 = SCOPE_IDENTITY()insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1) SELECT @.id2 = SCOPE_IDENTITY() insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID2)

|||

it generate an error:

Msg 547, Level 16, State 0, Line 9

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_PnglmnKerja2". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

i changed line 5 to
SELECT @.id2= @.ID1

or reduce the code to

SELECT @.ID1 = SCOPE_IDENTITY()
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1)
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID1)

and it worked... thanx for the idea i'm just beginner,learning mssql through forums & ebook. I really appreciate it

do u foresee any problem if many people try to insert their data concurently?10-20 people at the same time

Designing category system

Hi,

I am trying to design tables for managing category system.

In my inventory management system, I have a receive form, where I have to enter a product from a subcategory of a category.

I can't figuire out how should i design my tables. Am I correct with these tables -

Products Table - ProdID, ProdName, CatID, SCatID
Category Table - CatID, CatName
SubCategory Table - SCatID, SCatName

I can't show only the subCategories associated with a Category. What will be the design?

Also in the Receive form, which one will be logical? Should I select a category first, and related subCategories appears, and then product list form that subcategory appear?

Or should I select the product first, then categories and subCategories should appear?

Thanx a lot in advance for your help.

Regards
Kapalic

It depends how your hierarchy is,
suppose if your hierarchy follows ,

Category
|- Sub-Category
|- Product

Then you can go for following design,

Category -> CatId, CatName, ParentCatId
Where
CatId Primary key
ParentCatId foreign Key of CatId

(if ParentCatid is null then Category otherwise Sub Category)

Product -> ProductId, CatId
Where
CatId is Foregitn key of Category(CatId)

Regarding selection,
Product -> Sub-Category is ONE ON ONE relation
Sub-Cateogry -> Category is ONE ON ONE Relation

But,
Category -> Sub-Category is ONE To MANY relation
Sub-Cateogry -> Product is ONE ON MANY relation

Again its depends how your business rules defines.

|||

Hi ManiD,

Thnx for your reply! I will use the table structure u provided. This is much more logical.

And regarding to choosing fields, i do not have any binding other than existance of category and subcategory id. I just want an expert openion on which approach will be better for the users and developers perspective.

Regards
Kapalic

Designing a Report without Dataset

Hello

We are trying to create an app where we pass dynamically a dataset (from our form in C#) to our {report}.rdl file.

We are having doubts about something.

How can we design the actual report if we don't have the datasource until runtime? The reason to do this is due to our complex calculations of data which becomes almost impossible to achieve in a T-SQL environment.

I know with a lot of patience and time (we don't have both) we can achieve it, but even though, we would need to process some data in the client side.

So, the question is... is it possible to design a report without a Dataset? I know we can drag the controls on the layout window, but we won't be able to test it. IS there any workaroung about this?

Thank you

Assuming that you use the ReportViewer control in local mode and you have the dataset schema, you can lay out the report from the schema, e.g. from a typed dataset. You may find the following article helpful.|||

Ok, let's get more detailed about this.

We have our SQL Server. let's call it SQL. We have our clients using our software. In our software we have a winform with a ReportViewer object.

Ok. We want that winform to load dynamically the reports stored in our SQL Server. We want to pass a parameter to the winform, which is the report name, then according to that parameter we are going to load a report. OK. Then, Once we know what report we want to load, we want to be able to pass a "runtime dataset" (a dataset created at runtime) Why? because of the complexity of our calculations we prefer to retrieve the data raw from the SQL, then process it in our client software and then pass it to our report (which we have already selected and it's waiting to be shown in the Report Viewer).

We thought the steps were something like create an instance of a LocalReport, then get the Definition from the report (stored in the SQL Server) and pass it to this LocalReport, then create a ReportDataset instance and pass the data the way we want it, then pass that ReportDataset to the LocalReport and then tell the ReportViewer to show that instance of the LocalReport.

That's in detail what we want. So far we haven't been able to achieve it, but we keep on trying. Any suggestion is welcome.

By the way, does anyone can address me to this new feature of SQL2005 for writing code in C#. Maybe that's a solution for our "Processin Stage".

Regards

|||

We want that winform to load dynamically the reports stored in our SQL Server.

I interpret this as you keep the report definitions in a SQL Server database. You don't have/need a Report Server, correct? You need to:

1. Save the report definition to a local file.

2. Generate the dataset.

3. Configure the report viewer in local mode and bind the dataset to it.

Designing a database within a database... design question storing data...

I have a system that basically stores a database within a database (I'm
sure lots have you have done this before in some form or another).

At the end of the day, I'm storing the actual data generically in a
column of type nvarchar(4000), but I want to add support for unlimited
text. I want to do this in a smart fashion. Right now I am leaning
towards putting 2 nullable Value fields:

ValueLong ntext nullable
ValueShort nvarchar(4000) nullable

and dynamically storing the info in one or the other depending on the
size. ASP.NET does this exact very thing in it's Session State model;
look at the ASPStateTempSessions table. This table has both a
SessionItemShort of type varbinary (7000) and a SessionItemLong of type
Image.

My question is, is it better to user varbinary (7000) and Image? I'm
thinking maybe I should go down this path, simply because ASP.NET does,
but I don't really know why. Does anyone know what would be the benifit
of using varbinary and Image datatypes? If it's just to allow saving of
binary data, then I don't really need that right now (and I don't think
ASP.NET does either). Are there any other reasons?

thanks,
dave>I have a system that basically stores a database within a database (I'm
> sure lots have you have done this before in some form or another).

Please explain. What form is the data you are storing? If it isn't
represented relationally then why use SQL Server?

--
David Portas
SQL Server MVP
--|||Dave (chakachimp@.yahoo.com) writes:
> My question is, is it better to user varbinary (7000) and Image? I'm
> thinking maybe I should go down this path, simply because ASP.NET does,
> but I don't really know why. Does anyone know what would be the benifit
> of using varbinary and Image datatypes? If it's just to allow saving of
> binary data, then I don't really need that right now (and I don't think
> ASP.NET does either). Are there any other reasons?

Depends on the data you are storing. Since you talk about a "database with
a database", my initial reaction was you would use image, since I assumed
that the database is a binary file, complete with indexes, integer numbers,
and whatever.

But if the "database" is represented in text, for instance an XML document,
then there is no reason to use binary datatypes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm sorry, I need to elaborate. When I say database within a database,
I don't meen storing the actual database in a binary column or storing
XML in a column, instead I mean defining the structure of data within a
set of tables.

Instead of a concrete table such as Member with 3 columns: MemberID
int, FirstName varchar(25), LastName varchar(25), it is defined as an
abstract table that's defined across a series of tables. One row of any
of my abstract table actually lives (potentially) in several rows of a
sort of "Value" table. This "Value" table contains one column
(Varchar(4000)) that actually stores the value of the data item.

In our system we have over 15 abstract objects (Member being one of
them), so I know people will begin to question the architecture, but
that is not my point here... We do this for many reasons

1) We must store history on all changes (we write medical software)
2) We must encrypt the data and this allows a generic way to do this
(just flip a bit)
3) Our application will soon allow it's users to create user-defined
table and this is set up perfectly for that since it would only require
DML to achieve this (not DDL)
4) Speed isn't that important, right now our product has 10 users max.
Even if it became an issue we could solve this easily...

thanks,
dave|||Dave (chakachimp@.yahoo.com) writes:
> I'm sorry, I need to elaborate. When I say database within a database,
> I don't meen storing the actual database in a binary column or storing
> XML in a column, instead I mean defining the structure of data within a
> set of tables.
> Instead of a concrete table such as Member with 3 columns: MemberID
> int, FirstName varchar(25), LastName varchar(25), it is defined as an
> abstract table that's defined across a series of tables. One row of any
> of my abstract table actually lives (potentially) in several rows of a
> sort of "Value" table. This "Value" table contains one column
> (Varchar(4000)) that actually stores the value of the data item.
> In our system we have over 15 abstract objects (Member being one of
> them), so I know people will begin to question the architecture, but
> that is not my point here... We do this for many reasons
> 1) We must store history on all changes (we write medical software)
> 2) We must encrypt the data and this allows a generic way to do this
> (just flip a bit)
> 3) Our application will soon allow it's users to create user-defined
> table and this is set up perfectly for that since it would only require
> DML to achieve this (not DDL)
> 4) Speed isn't that important, right now our product has 10 users max.
> Even if it became an issue we could solve this easily...

Thanks for the elaboration, but I am not sure that this really provided
any more actual useful information to answer the question. "The database
within in a database", is thuse some sort of object that cannot be described
in a single table - nothing strange with that Order + OrderDetails is a
classic example.

But if I remove the veil about databases within database, and just take
the core question of yours: what datatype should use to save text data,
the answer is (n)varchar or (n)text, depening on your need to support
Unicode and the size limits of the data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Let me completely rephrase my approach...

If you've ever installed the sql data model for ASP.NET that resides in
sql server, you'll notice that Microsoft has a table called
ASPStateTempSessions. There are two columns that hold the encrypted
session data of the user. These two columns are:

varbinary(7000)
Image

and they are each nullable. Depending on the size of the Session data,
one or the other column is used since Blob columns (such as Image,
Text, etc...) are inefficient. Using the Session in ASP.NET you'll
notice that it consists of strings only, so why did Microsoft decide to
use these types? Is there some effieciency thing? Or were they planning
on simply supporting possible binary data in the future.

-dave|||Dave (chakachimp@.yahoo.com) writes:
> If you've ever installed the sql data model for ASP.NET that resides in
> sql server, you'll notice that Microsoft has a table called
> ASPStateTempSessions. There are two columns that hold the encrypted
> session data of the user. These two columns are:
> varbinary(7000)
> Image
> and they are each nullable. Depending on the size of the Session data,
> one or the other column is used since Blob columns (such as Image,
> Text, etc...) are inefficient. Using the Session in ASP.NET you'll
> notice that it consists of strings only, so why did Microsoft decide to
> use these types? Is there some effieciency thing? Or were they planning
> on simply supporting possible binary data in the future.

Sorry, I have zero knowledge about ASP .Net, so I cannot answer any
question about its design.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Been Working on the AspState database, some information indicates that
the transfer of the string data is being done as a binary stream for
efficiency, thus requiring a binary db datatype to store it.

*** Sent via Developersdex http://www.developersdex.com ***

Design View

I have a database created by another developer that I purchased and was
recently instructed to change some values in some tables form one kind to
another like "NOT NULL" to "NULL" or change something to a "FLOAT" I am not
that good at SQL but have always done this stuff with the Query Anylizer.. I
kept getting errors due to the relations in the tables and was instructed to
do the following?
"You should just make these changes in the design view of Enterprise
Manager, not through SQL commands. It should automatically update any
relationships for you."
I created a new view but still do not see where I can change these values,
all 3 of my SQL books seem to not cover this or the Enterprise Manager very
much.. And the hlp file is not clear on this either.
Thanks for any help.
Don
Hi
If you have purchased this database then I would expect it is the
responsibility of the developer that sold it you to create the appropriate
upgrade scripts!
There is nothing wrong with doing this in Query Analyser and most
experienced DBAs will do it that way. Enterprise manager will do some of the
harder work required to change columns when they are part of a PK or FK. You
can use profiler to see what EM does when you save changes made.
Using EM to get into design mode for a table, open up the tables branch in
EM and right click the table, you will then have a design table option on
the menu.
John
"Don Stull" <dstull1@.msn.com> wrote in message
news:eW7AeQuUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I have a database created by another developer that I purchased and was
> recently instructed to change some values in some tables form one kind to
> another like "NOT NULL" to "NULL" or change something to a "FLOAT" I am
not
> that good at SQL but have always done this stuff with the Query Anylizer..
I
> kept getting errors due to the relations in the tables and was instructed
to
> do the following?
> "You should just make these changes in the design view of Enterprise
> Manager, not through SQL commands. It should automatically update any
> relationships for you."
> I created a new view but still do not see where I can change these values,
> all 3 of my SQL books seem to not cover this or the Enterprise Manager
very
> much.. And the hlp file is not clear on this either.
> Thanks for any help.
> Don
>
>