Normalisation vs If it works just do it!

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)I'm a bit confused... When I use the term "place" to describe a horse racing bet, I mean that I expect (and am wagering that) the horse will finish second. While it is unusual to do, you can "box" a single horse, meaning that you expect them to place in the top 3, but you retrieve your original wager plus twenty percent of what the return would have been for a correct bet (win, place, or show), which sounds something like your description of a "place" bet.

If you can describe what you really want, I'm sure that someone here on the forum can show you how to code it!

-PatP|||Hi,

Thanks for the response, the actual calculations is not the issue e.g. the odds for a place bet, that is already known.

The problem concerns how to read through the table of placed bets while calculating the profit/loss as we iterate through each record.

The arithmetic is not the issue here, its the method of retrieval so that each record will have a field containing a profit or loss amount, put there by SQL as it reads through each item.

Thanks.|||Performance is the key in my work. If running the cursor is going to slow down the operation then having a little redundency isn't bad.|||My thought would be to do an UPDATE using the CASE to control which computation you use (main or place). Clean, fast, no cursor... What more could you ask?

-PatP

NooB Scripting Question How to use mailto

I am a noob to sql and asp programming. I am working on a db for the
gaming squad I am a member of. One of the pages I created is a roster
list of all the squad members. Part of this roster is listing each
member's email address. What several people have asked of me is to
make it so the email addresses can be clicked on to open their email
programs, just as html allows the mailto function to work.

Here is a copy of the coding I am currently using:
<td align="center"><font face="Arial"
color="#C0C0C0"><%=rst("email")%></font></td>
This will list the email address of the person, but how do I make it
so the line will function as a mailto command?
The page is saved as an .asp.

Thanks for any advice!

Davedavestrike (davestrike@.nventure.com) writes:
> Here is a copy of the coding I am currently using:
><td align="center"><font face="Arial"
> color="#C0C0C0"><%=rst("email")%></font></td>
> This will list the email address of the person, but how do I make it
> so the line will function as a mailto command?
> The page is saved as an .asp.

Actually, I don't have the slightest idea, nor do I feel to compelled
to have one, since this is an SQL Server forum, and not related to ASP.

But they say ASP has something to do with HTML, in which case I would
try with:

<td align="center"><font face="Arial" color="#C0C0C0">
<A HEF="mailto:%=rst("email")%"><%=rst("email")%></A>
</font></td
But if that does not work (and it probably doesn't) you should look
for an ASP forum.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Noob Query Problem -This will be easy for you

This is driving me crazy. I am using a simple ASP table editor to make changes to an Access table. The following command is causing me to get an error:

UPDATE rates Set IRItype='A2', IRIProductName='Stylish', SQfeet='803', Bathrooms=1, IRIunitprice='600',

This is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The SQL command is automaticly generated by the ASP script. I do not know if that extra comma is the cause of the error.is that all there was to the UPDATE statement? if so, it produces an error because it ends with a dangling comma

typically, you would have a WHERE clause, unless your intention is to update all rows to those values

so yeah, look into the ASP script|||The extra comma is certainly a problem. Also, make sure SQfeet and IRIunitprice are text values in the table. If they are numeric, then you wouldn't want the single quote surrounding the values.

Non-Standard Port for SQL2K

If SQL2K is not listening to the standard ports because
1433 has been changed to 14000, then what should the
connectionstring in as ASP page look like so that it uses
the correct port in order to make the connection?
object.ConnectionString(
"Provider=SQLOLEDB.1;
Data Source=MyServer;
Catalog=MyDB;
Persist Security Info=False;
UID=MyUID;
PWD=MyPWD;"
)Create an alias in the client network utility and specify the port there.
This approach is better because if the port/server changes then you don't
have to change your code.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Michael" <anonymous@.discussions.microsoft.com> wrote in message
news:577801c3e502$311a2c40$a601280a@.phx.gbl...

quote:

---

> If SQL2K is not listening to the standard ports because
> 1433 has been changed to 14000, then what should the
> connectionstring in as ASP page look like so that it uses
> the correct port in order to make the connection?
> object.ConnectionString(
> "Provider=SQLOLEDB.1;
> Data Source=MyServer;
> Catalog=MyDB;
> Persist Security Info=False;
> UID=MyUID;
> PWD=MyPWD;"
> )
>

---

|||Thank you Carlos. I'll give it a shot.

quote:

---

>--Original Message--
>Create an alias in the client network utility and specify

---

the port there.

quote:

---

>This approach is better because if the port/server

---

changes then you don't

quote:

---

>have to change your code.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 programming by Example
>
>"Michael" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:577801c3e502$311a2c40$a601280a@.phx.gbl...
uses[QUOTE]
>
>.
>

---

No. of connections available for Per Seat License

We have a MSSQL with per seat license of 20. The MSSQL is sitting in the same machine as the asp.net. Does that mean that the the no. of connections and Max Pool Size are limited up to 20 only?

Thanks,Ben

I don't know that any of us here are going to be willing to venture a guess on the often confusing legalities of SQL Server licensing. I can recommend this link as a starting point for your research:SQL Server 2000 Pricing and Licensing. Be sure to look at theSQL2KLic.doc in the upper righthand corner.