Composite Key?

I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tables

Table #1 - DanceTable
Columns: DanceID, Name, Description, Tags

Table #2 - StepsTable
Columns DanceID, StepID, longLocation, latLocation, Action, Description

Within my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step5, Step 6, Step7

What I really want is (or I think what I want is) is a composite primary key.

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step1, Step2, Step3

That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10).

I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.

A composite key is just a key made from multiple fields. In your case, it would be DanceID,StepID.

Your tables look fine, although if you make the StepID an identity field, you really don't need to store the "StepNumber". It's redundant. You can derive the "StepNumber" by the number of records that have the same DanceID and a lower StepID.

This would get you the @.StepNumber-th step:

SELECT TOP 1 *

FROM ( SELECT TOP (@.StepNumber) *

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID ASC) t1

ORDER BY t1.StepID DESC

Or get them all in order with "StepNumber":

SELECT *,ROW_NUMBER() OVER (ORDER BY StepID) As StepNumber

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID

But of course there is nothing prohibiting you from including a StepNumber field (Like in case you don't always want the steps to be renumbered, or they aren't contiguious, like step 1, 3, 5 with no step 2 or 4, etc). In this case, your primary key would be StepID, and I would create a unique index/constraint on DanceID,StepNumber.

component taking long time to load

hello all
we have an applicaion developed in ASP with SQL server as back end.
Our Application consists of many compoents (typically treated as hyperlinks in the application). now, all the components are responding fine except one component taking long time to load. I have checked my Queries in the backend and they were fine..
what could be the problem ?
I am getting an error OLEDB DRIVER TIMEOUT EXPIRED when i click on the component link.

Plese suggest me some ways...

Thanks and Regards
SAIHave you tried increasing the timeout ? What are you clicking on - what type of application ?|||Originally posted by rnealejr
Have you tried increasing the timeout ? What are you clicking on - what type of application ?

Hello,
the Application is a ASP application, and i am clicking on one of the Hyper link out of several..
Except this link all the other links are working fine..

sai|||But what type of application/database are you trying to connect to ? Have you tried to increase the timeout ? What are the connection strings like for this application that is having a problem - is it hitting a different sql server ?

Complex Order By Logic

I apologize if this is not the appropriate forum for this question.

I've

written a searchable database-driven application in classic ASP and

vbscript with a SQL Server backend. What I need to do is this: order

the results of a query so that if the first "order by" field is null to

order that entry based on the second "order by" field.

The

application I am writing is a database of books, and my client wants

the results to be ordered by Author, unless there is no Author, in

which case he wants that entry ordered by book title. Here is an

example of how he wants the books sorted:

Adamson, Jan - Book X
Bible, The
Wilson, Jonathan - Book Y

I

hope I've explained the scenario correctly. What's the best way to

write a SQL statement that will yield the ordering criteria described

above? Also, is there any way to get "Order by" to ignore articles like

"A" and "The?"

I personally hate this idea, as it would be annoying to scan through. I would put no authors at the end or beginning as 'No Author.'

You can use coalesce to do this:

create table authorTitle
(
author varchar(20),
title varchar(20)
)
insert into authorTitle
select 'Adamson, Jan','Book X'
union
select NULL, 'Bible, The'
union
select 'Wilson, Jonathan','Book Y'
go

select *
from authorTitle
order by coalesce(author,'') + coalesce(title,'')

author title
-- --
Adamson, Jan Book X
NULL Bible, The
Wilson, Jonathan Book Y

You will probably going to want to add this as a computed column, and likely index it to get this to get this to perform well if you have lots of books in the database.

|||Thank you! That did the trick.

compiler is not recognizing my using statement for SglConnection statement

I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.

Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:

Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

1 <asp:sqldatasource runat="server"></asp:sqldatasource>

2 <%@. Page Language="C#"%>

3 using System;

4 using System.Data;

5 using System.Collections;

6 using System.Data.SqlClient;

7

8 <script runat=server>

9

10 protected void Page_Load(object o, EventArgs e)

11 {

12 ObjectDataSource dsa; // This works no problems from the compiler here

13 SqlDataSource ds; // This works no problems from the compiler

14

15 if (IsPostBack)

16 {

17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))

18 {

19 instructions.Text = "Congratulations, your authenticated!";

20 instructions.ForeColor = System.Drawing.Color.Red;

21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";

23 }

24 else

25 {

26 instructions.Text = "Please try again!";

27 instructions.ForeColor = System.Drawing.Color.Red;

28 }

29 }

30

31 }

32

33 bool AuthenticateUser(string username, string password)

34 {

35 // Authentication code goes here

36

37 }

When using the in-line server code instead of code-behind, you have to use the following syntax instead:

<%@. Import Namespace="System.Data.SqlClient" %>

Compilation Error

I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()I imagine it is the way you are continuing the lines. Try:

DBConn= New OledbConnection("Provider=sqloledb;" & _
"server=localhost;" & _

and so on...|||I just tryed your suggestion and I'm still getting the same error message for all the codes listed.|||if you are connecting to sql database why are you using oledbconnection ? use the sqlconnection. check www.connectionstrings.com for some sample connection strings.

hth

compatibility question

just a question....

my web project is using SQL Express2005 and ASP.NET and C#.
my web hosting company only have MSsql2000. would there be any conflict with regards to my database? im sorry if i sound dumb. im a newbie to this.

thanks a lot!
There will not be a conflict but you may have to make code changes, there are a fair number of features in SQL Express that will not work with SQL 2000. I suggest downloading a copy of MSDE for yourself(which is free) so you can test before going live with the hoster.|||i am only using one mdf file and my asp.net program just deals with the common add, edit, delete transactions. would these transactions differ from version to version of MSSQL?

honestly i did not think there would be a problem coz these transactions are very common. pls correct me if i am wrong.
|||

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

|||

Euan Garden wrote:

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

this is my connection string:
<add name="conn_name" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbase_name.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

sql2000 doesnt support user instance, does it?

__
i just installed MSDE to make a test. i tried attaching my mdf file (created by SQL Express) to the MSDE but i got this error message:

"An error occured when attaching the database"

i attached that mdf file to the SQL Express and i encountered no problem at all. do you think i will encounter the same problem with my hosting company?

thanks for your time Euan.|||

2 issues here;

1/ MSDE does NOT support user instances so you need to change your conection string to a regular one.

2/ You are trying to attach a SQL2005 db to a SQL2000 machine, this will not work, you will need to move the data and schema onto a SQL Server 2000 implementation.

|||can I implement the membership control (provided by ASP.NET) on MSDE? i can try migrating from SQLEXxpress to MSDE but im not sure if membership control works on that MSDE. sorry if i really sound dumb. im a newbie.|||There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.|||

Euan Garden wrote:

There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.

i just tried it and it installed all the membership tables using this MSDE.
thanks for the help Euan Garden.

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!
Hi
"Kai" wrote:

> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John
|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.
|||Hi
"Kai" wrote:

> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:

> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data fr
om
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts a
ll
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of bot
h
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:

> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will t
ry
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John