SQL Server 2000 Design question

excalibur1814

Hello all,

I was wondering if I could get some pointers with regards to a project I've just started..

I'm not exactly new to Access but I am with SQL server 2000 (Yes, I'm working with a machine that has SQL2000!!) but will eventually be moving the machine to 2008.

I'm to design a small data entry web page (vb .net) that collects basic data, name, business address and a rough outline of product costs, ie, how much paper do you use, how much does it cost?

At this point, there will be various calculations behind the scenes, with a few if(a6=0,a5,a9)(In excel terms etc etc, then present the data back to the person.

I can easily design an input page and output page, with the data stored on the server, it's just a few middle bits that I'm missing with regards to changing the data

1 - Create the table that contains the data
2 - Create a trigger that copies the inputted data to another table, with the trigger also having the calculations. Finished data provided by the new table, to be overwritten by the next data input.

or

1 - Create the table that contains the data
2 - Create a 'view' that also compeletes the data calculations

Any pointers? I'm surrounded by books but would simply require pointers to get me reading and on my way/testing.

Thanks all

RobertKaucher

I'm not really sure what you want to do. Could you provide a theoretical example of what the data you would be storing might be like and what sort of ops you want to do on the data?

Feel free to send me an email, if that might be easier.

Also, feel free to give some VB code if you think that might help.

excalibur1814

Cool, thanks for the reply. I'll get into work tomorrow morn and post up a few slices of data. I'm under the impression that a trigger will do what I want but heck, might as well ask the pro's

excalibur1814

Ok.

A user pops along and enters the following information.
Name, Company name, Address, Telephone.
Number of employees, Production days of year, Sales, Annual sandwich consumption, cost of sandwiches.

That data is submitted

Now, if the user fails to enter the Sandwich data, the database will probably input the missing sandwich data via pre-set figures. Using those figures or the user inputted figures, it will start to divide, times, subtract, work out if a figure is + or - etc. Also some Concatenate use in there as well.

I've already got a sample of a trigger that does simple sums etc.

In the end either the data in the table will be complete via a trigger or a view will be created. not sure. Sound reasonable? Sensible way to go? (I'll have to search around for some SQL/Transact calculation samples)

RobertKaucher

To be honest with you, I would have the application perform the calculations as much as possible. SQL Server *CAN* do these sort of operations but it's generally a good idea to let SQL Server do what it's best at, which is store and retrieve data.

My suggestion is this: Use VB.NET to perform the calculations and validate the fields and use a SQL trigger to ensure the data is in the format you expect. Never allow the client app to send raw data to the SQL Server. Always use the client side code to clean and validate the data first. Then, as a second layer of protection, use t-sql to ensure the data is in fact clean, error free, and in the expected format.

excalibur1814

Thanks Robert!

I'll get the vb page to validate the data (as I've never done calculations before, all characters have been submitted as ntext but I've noticed that text in number fields brings errors. Validation will be added)

I'm using a book from 2004!! that's never let me down so far with regards to data input and retrieval. Would love a new up to date book.

excalibur1814

Ok.

The data is coming directly from a vb.net web page .

Here's a quick Trigger that I've managed to create

CREATE TRIGGER TestTrigger1
on dbo.Energy_InputSheet1
AFTER INSERT
AS
UPDATE Energy_inputSheet1
SET answerID =
(TestCalc1 / (TestCalc2) * 12)

I'm having trouble creating (even after trying various samples) a Trigger that basically states, "If it's less than zero/null, replace with 0.8". Could anyone help? I canot use Default as the page will always enter '0' which will overwrite and default values

excalibur1814

CREATE TRIGGER Test2
ON dbo.Energy_InputSheet1
AFTER INSERT, UPDATE, DELETE
AS
UPDATE Energy_InputSheet1

SET InAnnualElecID = 24
WHERE InAnnualElecID <1

That does what I want for when someone fails to enter data into the InAnnualElecID field, BUT fails to function when the data is entered via the web form. Madness. Ends up placing <NULL> within the SQL database.

RobertKaucher

Please script the table so we can see it. It will help me to see what sort of data types we are dealing with.

I would also like to see your VB code related to sending the data from the web form.

excalibur1814

The VB.net web Code & SQL

Code1
Code2



I'm thinking of moving the database over to Excel as the if and then functions are within my knowledge base and would probably have it done by the end of this week..... Will start tinkering in moment.

Thanks Robert and all that replied so far... Will post again later

RobertKaucher

excalibur1814 wrote: »

The VB.net web Code & SQL

Code1
Code2



I'm thinking of moving the database over to Excel as the if and then functions are within my knowledge base and would probably have it done by the end of this week..... Will start tinkering in moment.

Thanks Robert and all that replied so far... Will post again later

EXCEL!!! Don't wimp out on me!

Give me till the afternoon to check out the table structure. Then I'll look at your trigger.

excalibur1814

Sorry, sorry, sorry!!! I meant Access!

Ok. I'm making progress and see an ending. The data is entered from a web page created by me, which slaps standard values into the database. I 'wouldn't call the stats 'null' as nothing appears in the data view.

If there's a figure in the required field, it works. If there's a lower figure than required, it works, BUT if there's no figure it simply leaves everything blank. Agghhh

I've made sure that it accounts for Null but I could be wrong. Any hints?

CostElecMAIN: IIf([InAnnualElecID]<1,[Cost_Elec],IIf([InAnnualElecID]=Nz([InAnnualElecID]),[Cost_Elec],[InAnnualElecID]))

It's amazing what we can remember. Not opened Access in YEARS

excalibur1814

Phew

CostElecMAIN: IIF(IsNull([InAnnualElecID]),[Cost_Elec],[InAnnualElecID])

That seems to do the job.

Now onto the complex ish calculations.

excalibur1814

Thank you for your time Robert..

Damian

RobertKaucher

Sorry I was of no use... I have never been a big access user.

I'm still going to look at the SQL trigger for you. I just have had little will to work on anything extra that actually requires that I think.