How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

Hi all,

Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000.

In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code?

Thanks

Tomy

Your best bet would be to have layered constraints. Your business logic should also make sure that the data being entered is between 1000 and 10000, so that the violation would never reach the database. Database constraints should be left in place for people who like to edit the database directly.

While you can get a violated constraint error from the database, I don't believe it would be phrased properly to display to your users, which would mean lots of parsing in order to make the error presentable and user-friendly. It would be better to use a range validator to prevent the user from entering incorrect information.

As for trapping the error, use Try/Catch blocks

try{}catch (System.Data.SqlClient.SqlException ex){// Handle SQL Exceptions here; // all sql exceptions fall under this exception type, // but the errorcode/errors will be different.}catch (Exception ex){// handle all other errors here.}

|||

Thanks a lot.

how to total the values in a numeric column?

I'm building a report using the matrix control. I have financial data in
different line items (Salary, Rental Expense etc) that needs to be shown by
month (so the columns have months). I have it pretty much working but can't
figure out how to sum up the numbers in each column at the bottom so I can
have a total for every month. Could anyone help?
Thanks a lot.
BobRight-click on the row field that you wish to total, in your case it
would be the Salary field. Choose Sub-total from the menu.
To format the subtotals you need to right-click->properties on the tiny
green triangle that appears on the Total cell.|||ahhh. I was thinking this must be something really simple since it's such a
common function. You can't believe how much time I had spent trying to
figure this out. Thanks a lot.
Bob
"grahamiec" <grahamrichter@.gmail.com> wrote in message
news:1123769689.664448.10490@.g14g2000cwa.googlegroups.com...
> Right-click on the row field that you wish to total, in your case it
> would be the Salary field. Choose Sub-total from the menu.
> To format the subtotals you need to right-click->properties on the tiny
> green triangle that appears on the Total cell.
>

How to sum Salary column with a condition

Hi,
I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
statement to get it? Please show me the SQL statement. Thank a lot.
EmployeeNo PayDate Salary
1 1/31/1985 $1,000.00
1 2/28/1985 $1,000.00
1 3/31/1985 $1,000.00
1 4/30/1985 $1,000.00
1 5/31/1985 $1,000.00
1 6/30/1985 $1,000.00"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>
I assume that you want to do some grouping, or else you could simply use a
WHERE clause.
select EmployeeNo,
sum(case when PayDate<'5/1/1985' then Salary else 0 end) Salary
from MyTable
group by EmployeeNo
David|||Chris,
Try....
Select Sum(Salary) as 'Total Salary', EmployeeNo
from YourTable
where PayDate < '5/1/1985'
Group By EmployeeNo
Thanks
Barry|||Thank you both for the quick help.
"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one
SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>