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 To transfer SQL server Express to web hosting Provider

Hello,

FYI, I'm using Visual Studion 2005 with SQL server Ex Edition in my developer PC. So the problem now is how i can migrate the database into my web hostin provider?. The provider also use the same version of SQl server. What i noticed the express editon dont have function to import or export.

Anybody iin here know how to solve? Right now, i'm create the databse using my controll panel in my web hosting then i create tables using management studio express, this way i must do twice works, 1st in my developer PC then into my web hosting..please anybody give a hand please

I already copy web site include the app_data into my web hosting, but unfortunately i dont know the connections strings? when i ask the provider they told me to use the management studio for the databse for connection into their server.

i also noticed if i install the application which use sql server dataabse it can be install the database including the tables and the data, so how is it i want to make my web site including the database be like installer. Like CommunityServer Forum? i just install into web hosting..is it possible? How the way? Thank you

note: I'm newbie in VS and Sql server

Please some one in here...I'm really needs this solution..i must create new application using VWD and SQL ex..i have a dateline..please the expert on in here..|||

Hosters each have their own way to allow you to move your database from the development environment onto the hosting platform. I can't tell how your hoster expects this to be done from your post.

Many Hosters will create a database for you and then let you create objects in the database they give you. Typically they provide you with some kind of web interface to accomplish this. It is common for Hosters to provide you with a way to run scripts to create objects in your hosted databases. If that is the case, you might be interested in the Database Publishing Wizard which we have just made available as a Community Technology Preview. The DPW is a new tool we are working on to make it easier to move databases from development to Hosting site using scripts. This is a CTP, so it's an earlier release. There is more information on the site about how to use the tool.

It sounds like your Hoster might allow you to connect Management Studio to thier site. If that is the case, you can still use this tool to generate your entire database as a script file and then use Management Studio to run the script when you're attached to the Hosters server.

You're right that Import/Export isn't part of SQL Express, but this tools should meet the need for uploading databases to a web hoster.

Mike

|||Thank you for your reply, I'm using Sql server 2005 Express Edition. Is it Database Publishing Wizard valid with this Sql Server Version.|||Sorry one more things,,is ti possible if we doing a mirroring databse using sql server express edition. If it can how the way? For example, i want mirror my databse from my web hosting provider with my PC in mmy office. is it possible? Thank you|||

I alrealdy install the database Publishing wizard but the problem is i'm a newbie and i do not know how to use it. When i open it just only Ms dos appear. Is it normal?

For Information, my web hosting provider use Plesk control..so the database is store through Plesk system. So with this scenario is it some body in here have an experience like me..Please tell me how the way?

|||

The Database Publishing Wizard is compatible with all Editions of SQL Server 2005. It may also be compatible with SQL 2000, I'm not sure. We're in CTP right now for the DPW, so it's not complete; this first release only supports a command line interface so it is normal that it just opens the DOS window when you run it. You will need to type your commands in at the drive> prompt per the instructions listed in the window. Some basic usage information is available on the tool site and there is a forum there also where you can post questions specific to the wizard.

I'm not familiar with Plesk so I'll have to let someone else advise you there. You will probably need to get more information directly from the technical support team at your web hoster site if you have problems working with thier interface.

Mike

How to To transfer SQL server Express to web hosting Provider

Hello,

FYI, I'm using Visual Studion 2005 with SQL server Ex Edition in my developer PC. So the problem now is how i can migrate the database into my web hostin provider??. The provider also use the same version of SQl server. What i noticed the expree editon dont have function to import or export.

Anybody iin here know how to solve? Right now, i'm create the databse using my controll panel in my web hosting then i create tables usin management studio express, this way i must do twice works, 1st in my developer PC then into my web hosting..please anybody give a hand please

You can move the .mdf file (the database file) to the server, so you don't need to import or export data.

|||

Tq for the reply, I already copy web site include the app_data into my web hosting, but unfortunately i dont know the connections strings?? when i ask the provider they told me to use the management studio for the databse for connection into their server. But i also noticed if i install the application which use sql server dataabse it can run, so how is it i wanto make my web site including the database be like installer..so i just install into web hosting..is it possible??

note: I'm newbie in VS and Sql server

|||I've heard that creating database can be included in application installer. However you still need to change your connection string to connect to the new SQL instance, so that the database created by the installer can be attached to the new SQL instance.|||So anybody in here can help me.. please..i need a way..i have a dateline..huhuh|||Hello friends,, I really need this solutions, please somebody in here..can anyone give any suggestion?? Thank you|||

Check out the new Database Publishing Wizard on Codeplex athttp://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard

We've just released out first CTP and we've still got a ways to go on perfecting it, but there's a good chance it'll help you.

Thanks,

Dan Winn

Senior Program Manager, Microsoft SQL Server

|||Tq for the reply..i already install it..but unfortunately i'm do not know how to use it..i prefer use with GUI...can u help me the steps how to use it? anyway thanks again for the reply

How to tie ? to parameter name?

In the Visual Studio (2005) designer, I created a report, data source,
and dataset. The query is essentially Select * from Table1 where
ClientID = ?
Under Report Parameters I changed the name Parameter1 to CIDParam, so
that it is somewhat meaningful when I call it from code. But when I
try to preview the report, Studio complains that the query can't find
Parameter1. So how do I let Studio know that ? should look at
CIDParam?I go against Sybase and have to do this all the time (unfortunately). Go
back to the dataset tab, click on ..., parameters tab. Remap the query
paramters. Keep in mind that although RS creates the report parameters
automatically for you they are not the same thing. This is where they are
mapped.
Another time you will want to do this is if you want to use the same report
parameter multiple times. For instance you have a from and end date that is
used multiple times in your query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:l2jni255jalsgohek00ih5ac2h18vbh33a@.4ax.com...
> In the Visual Studio (2005) designer, I created a report, data source,
> and dataset. The query is essentially Select * from Table1 where
> ClientID = ?
> Under Report Parameters I changed the name Parameter1 to CIDParam, so
> that it is somewhat meaningful when I call it from code. But when I
> try to preview the report, Studio complains that the query can't find
> Parameter1. So how do I let Studio know that ? should look at
> CIDParam?|||On Tue, 10 Oct 2006 12:24:48 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>I go against Sybase and have to do this all the time (unfortunately). Go
>back to the dataset tab, click on ..., parameters tab. Remap the query
>paramters. Keep in mind that although RS creates the report parameters
>automatically for you they are not the same thing. This is where they are
>mapped.
>Another time you will want to do this is if you want to use the same report
>parameter multiple times. For instance you have a from and end date that is
>used multiple times in your query.
Yes, that seems to have done it. Thanks.

How to tell if a function is called within a trigger

How can I tell whether a function has been called, along with the values that were passed into it? My SQL is generated by Visual Basic, and on an insert, a trigger fires, which then *might* call the function depending on a condition that's difficult to monitor. Ideally, I'd be looking for a simple function that would write to a log, or print a message somewhere. "Print" does not work within functions. I'm running SQL Server 2000.You could create a stored procedure that writes to a log, and then call that stored procedure from the trigger. Kinda messy, but will work.|||

1. Profiler

2. Put code in the trigger that inserts relevant values (funtion input parameters, record IDs) into a table at the point the function would be called. Comment out or remove this code after testing. I routinely do this in Try Catch blocks (or 2000 error handling) of sprocs during dev.

|||You also cannot perform an INSERT to a permanent table in a function. If this is important you might consider upgrading to SQL Server 2005 and using a SET CONTEXT_INFO to save some information. I guess you could call a procedure from the trigger but I am really not sure how much this will buy you -- you are sort-of already in a psedudo procedure since you are in a trigger.

How to stop reporting services deploy

I am using visual studio 2003 with sql reporting services for sql
server 2000.
I have a solution with a web application project and a reporting
services project.
When I start the web application project in the IDE, the reports are
automatically deployed to the report server.
I would like to stop this for two reasons: it takes too long, and my
reports are actually not ready to deploy.
Does anyone know how I can stop my reporting services project from
deploying when I run another project in the same solution?On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready to deploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
Does anyone have an answer?|||On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready to deploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
I can't believe I'm the only person who has ever encountered this.
Does anyone know how to work around this?|||On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready todeploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
OK, I've found the answer, which I'll document here for prosperity.
Right click the solution and go properties.
go configuration properties/configuration
reporting services projects have a "deploy" check box. Uncheck to stop
deployment.

How to stick a chart into the middle of an external page

Hello
I am using Reporting Services within Visual Studio .Net 2003, connecting to
a SQL Server 2000 database.
I've been using RS for a while, but only for basic reports. Now I want to
do something a bit more advanced. I'm not sure exactly how to put the
question, so am going for the 'plain English' approach, hoping someone will
follow...
I've been asked by a user about embedding a RS chart into the middle of an
external (possibly web or .Net) page. He wants to be able to pass
parameters to RS, have it create a chart, then embed the chart in his page.
He only wants the chart, however, not the whole web page as you would view
it through a browser. So he would have text, followed by a chart, followed
by more text, like a standard report.
I'm assuming this would need to be coded, but don't really know where to
start. I'd appreciate some pointers, just to head me in the right
direction.
Thanks
DeniseYou might want to create a report that contains only the chart. Render the
report via the ReportServer web service as HTML, which will allow you to get
the images via the RenderStream method.
Save the image data to a temp file, and display it on your page.
If you take a look at the ReportViewer sample provided by MS with Reporting
Services 2000, it pretty much has all the code you will need.
"Denise" <me@.me.com> wrote in message
news:1vbyx9jxpcpa7$.1jcgop5kq2aji$.dlg@.40tude.net...
> Hello
> I am using Reporting Services within Visual Studio .Net 2003, connecting
> to
> a SQL Server 2000 database.
> I've been using RS for a while, but only for basic reports. Now I want to
> do something a bit more advanced. I'm not sure exactly how to put the
> question, so am going for the 'plain English' approach, hoping someone
> will
> follow...
> I've been asked by a user about embedding a RS chart into the middle of an
> external (possibly web or .Net) page. He wants to be able to pass
> parameters to RS, have it create a chart, then embed the chart in his
> page.
> He only wants the chart, however, not the whole web page as you would view
> it through a browser. So he would have text, followed by a chart,
> followed
> by more text, like a standard report.
> I'm assuming this would need to be coded, but don't really know where to
> start. I'd appreciate some pointers, just to head me in the right
> direction.
> Thanks
> Denise|||Hi Pedro
Thanks very much for this. I've gone through the sample and produced a
report, passing parameters via the code. My user is very happy!
Thanks again
Denise
On Sat, 19 Nov 2005 20:25:26 -0600, Pedro wrote:
> You might want to create a report that contains only the chart. Render the
> report via the ReportServer web service as HTML, which will allow you to get
> the images via the RenderStream method.
> Save the image data to a temp file, and display it on your page.
> If you take a look at the ReportViewer sample provided by MS with Reporting
> Services 2000, it pretty much has all the code you will need.
>
> "Denise" <me@.me.com> wrote in message
> news:1vbyx9jxpcpa7$.1jcgop5kq2aji$.dlg@.40tude.net...
>> Hello
>> I am using Reporting Services within Visual Studio .Net 2003, connecting
>> to
>> a SQL Server 2000 database.
>> I've been using RS for a while, but only for basic reports. Now I want to
>> do something a bit more advanced. I'm not sure exactly how to put the
>> question, so am going for the 'plain English' approach, hoping someone
>> will
>> follow...
>> I've been asked by a user about embedding a RS chart into the middle of an
>> external (possibly web or .Net) page. He wants to be able to pass
>> parameters to RS, have it create a chart, then embed the chart in his
>> page.
>> He only wants the chart, however, not the whole web page as you would view
>> it through a browser. So he would have text, followed by a chart,
>> followed
>> by more text, like a standard report.
>> I'm assuming this would need to be coded, but don't really know where to
>> start. I'd appreciate some pointers, just to head me in the right
>> direction.
>> Thanks
>> Denise