Install RS for multiple websites on single computer

My understanding is that RS installs ONLY on the default website. I'm hosting
four websites on a single IIS computer. I want to use Reporting Services
separately on each of the websites. That is, each will have its own database
and its own set of reports. Is this possible? How?
Thanks.
DilipDilip,
I am not sure if you can host multiple instances of RS on single server,
but I have achieved the same requirement by creating a separate directory for
each application. Each corresponding application can have separate directory
with its Reports and required Dataset.
Hope this helps.
Mahesh
"Dilip Kumar" wrote:
> My understanding is that RS installs ONLY on the default website. I'm hosting
> four websites on a single IIS computer. I want to use Reporting Services
> separately on each of the websites. That is, each will have its own database
> and its own set of reports. Is this possible? How?
> Thanks.
> Dilip|||Thanks for your reply, Mahesh.
I want to keep all the domains separate. I want to access each set of
reports as follows:
http://www.companyA.com/Reports
http://www.companyB.com/Reports
http://www.companyC.com/Reports
http://www.companyD.com/Reports
Dilip
"Mahesh Gaware" wrote:
> Dilip,
> I am not sure if you can host multiple instances of RS on single server,
> but I have achieved the same requirement by creating a separate directory for
> each application. Each corresponding application can have separate directory
> with its Reports and required Dataset.
> Hope this helps.
> Mahesh
> "Dilip Kumar" wrote:
> > My understanding is that RS installs ONLY on the default website. I'm hosting
> > four websites on a single IIS computer. I want to use Reporting Services
> > separately on each of the websites. That is, each will have its own database
> > and its own set of reports. Is this possible? How?
> >
> > Thanks.
> > Dilip|||hi Dilip,
hmm.. I am sorry, I have no idea about how this can be acheived using
single IIS server for single RS instance.
Thanks,
Mahesh
"Dilip Kumar" wrote:
> Thanks for your reply, Mahesh.
> I want to keep all the domains separate. I want to access each set of
> reports as follows:
> http://www.companyA.com/Reports
> http://www.companyB.com/Reports
> http://www.companyC.com/Reports
> http://www.companyD.com/Reports
> Dilip
> "Mahesh Gaware" wrote:
> > Dilip,
> > I am not sure if you can host multiple instances of RS on single server,
> > but I have achieved the same requirement by creating a separate directory for
> > each application. Each corresponding application can have separate directory
> > with its Reports and required Dataset.
> >
> > Hope this helps.
> >
> > Mahesh
> >
> > "Dilip Kumar" wrote:
> >
> > > My understanding is that RS installs ONLY on the default website. I'm hosting
> > > four websites on a single IIS computer. I want to use Reporting Services
> > > separately on each of the websites. That is, each will have its own database
> > > and its own set of reports. Is this possible? How?
> > >
> > > Thanks.
> > > Dilip|||Hi Dilip,
I have been thinking about how to do this as I have been thinking of
doing the same...though I am coming to the conclusion it is probably
best to have one server per customer....
However, my thoughts on the subject were as follows...
1. Set up links or domains for each customer.
2. repoint the domains into a single dot net nuke portal (I am
considerign using dot net nuke www.dotnetnuke.com) as the 'BI portal'
for clients....probably a release 2.0 thing for me now.
3. User signs in or can now use integrated security for DNN but if you
have multiple companies on one server you are probably going to host
the server outside their security domains so you need a way to securely
log them into DNN.
4. Present reports inside the DNN environment which will allow you to
have the users looking at the one IIS server and one database without
each customer knowing about the other.....you control where the users
point to for each report or directory so you can just set up different
directories for different customers.
5. You could even set graphics etc from the userid or portal you were
looking at so the clients saw different formats etc inside their BI
portal...
Having played with all this...I thought it was easier just to get the
clients to pay for one machine each...
Peter

Install multiple instance of database to one computer question

Dear all,
If i want to install multiple instance of database to one computer(XP) for a
developer edition. Do i need to buy two license of developer edition for two
instance to install to one computer? Moreover if i install two instance to
one computer, can the two instance of database can start together in
enterprise manager/ or just one instance can operate in one time? It is
because install two instance of database, one for on-going development and
one for use to backup the data from the production server(since it need to
test the backup data can restore)
I don't know about the license but you can run 2 instances ( 1 has to be
named) at the same time just register them both in enterprise Manager
http://sqlservercode.blogspot.com/
"Joe" wrote:

> Dear all,
> If i want to install multiple instance of database to one computer(XP) for a
> developer edition. Do i need to buy two license of developer edition for two
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)
|||"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
> Dear all,
> If i want to install multiple instance of database to one computer(XP) for
> a
> developer edition. Do i need to buy two license of developer edition for
> two
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)
My understanding is that you can install as many instances as you want on
the same machine. The licensing comes in to play either per processor or
per seat. With the developer edition, I don't think you need to purchase
CALs as you are not supposed to be using Dev edition for production. I
would double check that however.
There is no problem installing multiple instances on the same server and
both will be able to run concurrently. It will eat up more of your
resources however as each instance of SQL Server will have it's own services
executing and memory needs.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Yeah and the developer edition is only like $50 a copy.
See:
http://www.microsoft.com/sql/howtobuy/faq.mspx
and
http://www.microsoft.com/sql/howtobuy/default.mspx
and
http://www.microsoft.com/sql/howtobuy/development.mspx
HTH
Jerry
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23Dr8ldPxFHA.596@.TK2MSFTNGP12.phx.gbl...
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
> My understanding is that you can install as many instances as you want on
> the same machine. The licensing comes in to play either per processor or
> per seat. With the developer edition, I don't think you need to purchase
> CALs as you are not supposed to be using Dev edition for production. I
> would double check that however.
> There is no problem installing multiple instances on the same server and
> both will be able to run concurrently. It will eat up more of your
> resources however as each instance of SQL Server will have it's own
> services executing and memory needs.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Install multiple instance of database to one computer question

Dear all,
If i want to install multiple instance of database to one computer(XP) for a
developer edition. Do i need to buy two license of developer edition for two
instance to install to one computer? Moreover if i install two instance to
one computer, can the two instance of database can start together in
enterprise manager/ or just one instance can operate in one time? It is
because install two instance of database, one for on-going development and
one for use to backup the data from the production server(since it need to
test the backup data can restore)I don't know about the license but you can run 2 instances ( 1 has to be
named) at the same time just register them both in enterprise Manager
http://sqlservercode.blogspot.com/
"Joe" wrote:

> Dear all,
> If i want to install multiple instance of database to one computer(XP) for
a
> developer edition. Do i need to buy two license of developer edition for t
wo
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)|||"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
> Dear all,
> If i want to install multiple instance of database to one computer(XP) for
> a
> developer edition. Do i need to buy two license of developer edition for
> two
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)
My understanding is that you can install as many instances as you want on
the same machine. The licensing comes in to play either per processor or
per seat. With the developer edition, I don't think you need to purchase
CALs as you are not supposed to be using Dev edition for production. I
would double check that however.
There is no problem installing multiple instances on the same server and
both will be able to run concurrently. It will eat up more of your
resources however as each instance of SQL Server will have it's own services
executing and memory needs.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Yeah and the developer edition is only like $50 a copy.
See:
http://www.microsoft.com/sql/howtobuy/faq.mspx
and
http://www.microsoft.com/sql/howtobuy/default.mspx
and
http://www.microsoft.com/sql/howtobuy/development.mspx
HTH
Jerry
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23Dr8ldPxFHA.596@.TK2MSFTNGP12.phx.gbl...
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
> My understanding is that you can install as many instances as you want on
> the same machine. The licensing comes in to play either per processor or
> per seat. With the developer edition, I don't think you need to purchase
> CALs as you are not supposed to be using Dev edition for production. I
> would double check that however.
> There is no problem installing multiple instances on the same server and
> both will be able to run concurrently. It will eat up more of your
> resources however as each instance of SQL Server will have it's own
> services executing and memory needs.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Install multiple instance of database to one computer question

Dear all,
If i want to install multiple instance of database to one computer(XP) for a
developer edition. Do i need to buy two license of developer edition for two
instance to install to one computer? Moreover if i install two instance to
one computer, can the two instance of database can start together in
enterprise manager/ or just one instance can operate in one time? It is
because install two instance of database, one for on-going development and
one for use to backup the data from the production server(since it need to
test the backup data can restore)I don't know about the license but you can run 2 instances ( 1 has to be
named) at the same time just register them both in enterprise Manager
http://sqlservercode.blogspot.com/
"Joe" wrote:
> Dear all,
> If i want to install multiple instance of database to one computer(XP) for a
> developer edition. Do i need to buy two license of developer edition for two
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)|||"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
> Dear all,
> If i want to install multiple instance of database to one computer(XP) for
> a
> developer edition. Do i need to buy two license of developer edition for
> two
> instance to install to one computer? Moreover if i install two instance to
> one computer, can the two instance of database can start together in
> enterprise manager/ or just one instance can operate in one time? It is
> because install two instance of database, one for on-going development and
> one for use to backup the data from the production server(since it need to
> test the backup data can restore)
My understanding is that you can install as many instances as you want on
the same machine. The licensing comes in to play either per processor or
per seat. With the developer edition, I don't think you need to purchase
CALs as you are not supposed to be using Dev edition for production. I
would double check that however.
There is no problem installing multiple instances on the same server and
both will be able to run concurrently. It will eat up more of your
resources however as each instance of SQL Server will have it's own services
executing and memory needs.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Yeah and the developer edition is only like $50 a copy.
See:
http://www.microsoft.com/sql/howtobuy/faq.mspx
and
http://www.microsoft.com/sql/howtobuy/default.mspx
and
http://www.microsoft.com/sql/howtobuy/development.mspx
HTH
Jerry
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23Dr8ldPxFHA.596@.TK2MSFTNGP12.phx.gbl...
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:4B562ABF-916B-4EDD-A145-E79D773C7B91@.microsoft.com...
>> Dear all,
>> If i want to install multiple instance of database to one computer(XP)
>> for a
>> developer edition. Do i need to buy two license of developer edition for
>> two
>> instance to install to one computer? Moreover if i install two instance
>> to
>> one computer, can the two instance of database can start together in
>> enterprise manager/ or just one instance can operate in one time? It is
>> because install two instance of database, one for on-going development
>> and
>> one for use to backup the data from the production server(since it need
>> to
>> test the backup data can restore)
> My understanding is that you can install as many instances as you want on
> the same machine. The licensing comes in to play either per processor or
> per seat. With the developer edition, I don't think you need to purchase
> CALs as you are not supposed to be using Dev edition for production. I
> would double check that however.
> There is no problem installing multiple instances on the same server and
> both will be able to run concurrently. It will eat up more of your
> resources however as each instance of SQL Server will have it's own
> services executing and memory needs.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

install multiple database on one sql server

Hi,
I am planning to have 3 (app) database intalled on one sql 2000 server, what
is the best way/s to do this?
Thanks.
You could either locate all databases on the one server or install multiple
instances with each DB in its own instance. I don't really see any need for
instances though as its 6 of one or half dozen of the other. I've ran with
30+ dbs on one instance in a production environment.
"anoni" wrote:

> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server, what
> is the best way/s to do this?
> Thanks.
|||Hi,
All depends up on the resource usage for your individual application and SQL
Server edition you are going to use.
If it is enterprise edition on a Windows Advanced server you could add more
RAM (> 4 GB) and enable AWE.
After that you can have 3 databases in the same database instance.
Incase if your resource usage is less and number of concurrent users are
less then you could go for SQL Server standard edition with 2 GB of RAM
in Server and have all the 3 databases in same database instance.
Note:-
For installing multiple instance:- For each instance you need to get
seperate SQL server License.
Thanks
Hari
SQL Server MVP
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.
|||As long as Server is capable of handling the work, put multiple DBs in a
single instance of SQL... Maintenance is easier..
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
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.
sql

install multiple database on one sql server

Hi,
I am planning to have 3 (app) database intalled on one sql 2000 server, what
is the best way/s to do this?
Thanks.You could either locate all databases on the one server or install multiple
instances with each DB in its own instance. I don't really see any need for
instances though as its 6 of one or half dozen of the other. I've ran with
30+ dbs on one instance in a production environment.
"anoni" wrote:
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server, what
> is the best way/s to do this?
> Thanks.|||Hi,
All depends up on the resource usage for your individual application and SQL
Server edition you are going to use.
If it is enterprise edition on a Windows Advanced server you could add more
RAM (> 4 GB) and enable AWE.
After that you can have 3 databases in the same database instance.
Incase if your resource usage is less and number of concurrent users are
less then you could go for SQL Server standard edition with 2 GB of RAM
in Server and have all the 3 databases in same database instance.
Note:-
For installing multiple instance:- For each instance you need to get
seperate SQL server License.
Thanks
Hari
SQL Server MVP
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.|||As long as Server is capable of handling the work, put multiple DBs in a
single instance of SQL... Maintenance is easier..
--
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
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.

install multiple database on one sql server

Hi,
I am planning to have 3 (app) database intalled on one sql 2000 server, what
is the best way/s to do this?
Thanks.You could either locate all databases on the one server or install multiple
instances with each DB in its own instance. I don't really see any need for
instances though as its 6 of one or half dozen of the other. I've ran with
30+ dbs on one instance in a production environment.
"anoni" wrote:

> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server, wh
at
> is the best way/s to do this?
> Thanks.|||Hi,
All depends up on the resource usage for your individual application and SQL
Server edition you are going to use.
If it is enterprise edition on a Windows Advanced server you could add more
RAM (> 4 GB) and enable AWE.
After that you can have 3 databases in the same database instance.
Incase if your resource usage is less and number of concurrent users are
less then you could go for SQL Server standard edition with 2 GB of RAM
in Server and have all the 3 databases in same database instance.
Note:-
For installing multiple instance:- For each instance you need to get
seperate SQL server License.
Thanks
Hari
SQL Server MVP
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.|||As long as Server is capable of handling the work, put multiple DBs in a
single instance of SQL... Maintenance is easier..
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
"anoni" <anoni@.discussions.microsoft.com> wrote in message
news:C4E730EA-3A96-45E5-B3F5-0197749958FE@.microsoft.com...
> Hi,
> I am planning to have 3 (app) database intalled on one sql 2000 server,
> what
> is the best way/s to do this?
> Thanks.

Inserting values into multiple tables

Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:

>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Inserting to multiple tables in SQL Server 2005 that use identity specification

Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an

Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

My problem is that I can insert data to each specific table by itself using seperate insert statements....eg...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}
//same code as above for the resistance table

However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @.@.IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
ScottyI think the problem is in your design. I'm thinking you should dump the ExerciseMaster table.

If you have a one-to-one relationship between attendance, Resistance, and Aerobics, then a single table will do. If you have a one-to-many relationship (for a given attendance, there may be zero or more resistance sessions and zero or more aerobics sessions) then this schema should work for you:

1. Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
2. Resistance Table (ResistId(PK Identity specific), AttendanceID(FK), Weight , Reps, Sets)
3. Aerobics Table(AerobicsID(PK Identity specific), AttendanceID(FK), MachineID, Intensity, Time)

You can insert the attendance record and then either recover the ID created using @.@.Identity or switch to GUID identifiers and let your interface create the ID. Then use that ID when you insert the Resistance and Aerobics records.|||Thanks Blindman, I see where you are coming from about the tables and I agree with what you have suggested. However, i am still a bit lost as to what the actual code would be to use the @.@.IDENTITY...
eg.
Suppose i have changed my database design to reflect your suggestions and want to enter code to the Aerobics table, how do i use the attendanceID that is used in the attendance table and enter it into the attendanceID in the Aerobics table..I have no idea how or where to use the @.@.IDENTITY in my code...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([AttendanceID], [/COLOR][MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}|||Well, the other thing you need to do is to stop issuing dynamic SQL code from your application. It is more complicated, less efficient, and insecure.

Write a store procedure called "InsertAttendance" that takes MembershipNo and Date as parameters. The stored procedure will insert the new record (after checking to make sure it does not already exist), and then call @.@.Identity (better yet, use @.@.SCOPEIDENTITY...) to get the ID that was just created. The sproc can pass the new ID back as an output parameter.

Or, as I said, you can switch to using GUIDs rather than identity integers as your primary key. Then you can actually create the new GUID within your application code, and you never have to query the database to find out what ID was assigned.|||is there any chance you could provide some sample code as to how i would call the @.@.IDENTITY, where i would call it from and how i would use it to actually write the information to another table ? im a bit lost as im very new to all this...
Thanks|||set nocount on
--Create a dummy table
create table SampleTable (IdentityColumn int identity, DummyData varchar(50))
go

--Create a dummy sproc
create procedure InsertSample (@.DummyValue varchar(50), @.NewIdentity int output)
as
begin
insert into SampleTable (DummyData) values(@.DummyValue)
set @.NewIdentity = Scope_Identity()
end
go

--Run some insert statements and check the ID values retrieved
declare @.NewID int
exec InsertSample 'Fubar1', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar2', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar3', @.NewIdentity = @.NewID output
select @.NewID

--Cleanup
drop procedure InsertSample
drop table SampleTable
go|||Thanks for that, Have that working but only when i execute the stored procedure from the database. I am still unable to actually use it unfortunatley within the application...

string userID;
const int macId = 3;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.SelectCommandType = SqlDataSourceCommandType.Text;

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
pgpDataSource.InsertCommand = "InsertAttendance";

pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
//I think this may be the problem, both here and again below where
// i have the line of code commented out again...
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("MachineID", macId.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text );
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("Reps", txtReps.Text);
pgpDataSource.InsertParameters.Add("Sets", txtSets.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

if (RowsAffected != 1)
{
Response.Redirect("~/Problem.aspx");
}
else
{
Response.Redirect("~/Log.aspx");
}
}

This is the storedProcedure i created to fill all three tables which works but not when i try to use it in the application...

ALTER PROCEDURE InsertAttendance

@.NewIdentity int output,
@.MembershipNo smallint,
@.Date datetime,
@.Reps smallint,
@.Sets smallint,
@.Weight smallint,
@.MachineID smallint,
@.Intensity smallint,
@.ExerciseTime int

AS

BEGIN

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END|||How you call this stored procedure from your application code depends upon your programming platform. I don't code interfaces, so I can't help you with that. But I'd have a hard time believing that any respectable programming language would not have a method of calling a stored procedure and retrieving an output parameter.
I suggest you ask about this on a forum specific to your development platform.|||Are you sure this works properly? I would have thought that you would have to set the value of @.NewIdentity before you used it in the Insert statements for the Aerobics and Resistance tables.

[/QUOTE]

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END[/QUOTE]

inserting to multiple fields using a button

I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.

You can just execute some SqlCommands to insert data to different tables in the Click event of a button, for example:

protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@."Data Source=labsh96223\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
string insertSql = @."Insert into myTbl_1 select @.id, @.name";
SqlCommand myCommand = new SqlCommand(insertSql, conn);
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.name", SqlDbType.VarChar, 100);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox1.Text);
myCommand.Parameters["@.name"].Value = TextBox2.Text;
int i = myCommand.ExecuteNonQuery();
myCommand.CommandText = @."Insert into myTbl_2 select @.id, @.Description";
myCommand.Parameters.Clear();
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.Description", SqlDbType.VarChar, 1000);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox3.Text);
myCommand.Parameters["@.name"].Value = TextBox4.Text;
i= myCommand.ExecuteNonQuery();
//execute other commands
}
}