more on parameters...

We have a custom .net webb app front end that uses the SOAP methods for rs.
For reasons that are not important to explain, we have several different
types of parameters..we have a couple that we would like to have the user do
an external search (there are too many values to set the parameter up to run
an sp to display allowable values) so we want to create a 'search' page for
them to narrow down / search and pick an appropriate value. The SP that
supplies the report(s) needs to have a value passed to run the report (we
can't filter after the fact). My question is that no 2 databases (out of 60
+ databases) are going to have the same value(s) for these particular
parameters - but I need to set the parm up to have a default value...I
believe... . Does the default value have to be an allowable value? Or can
you put something in as a place holder or a phrase to look for in code so we
can take the neccessary next step (open search page)?
I hope I am explaining this ok...
Thanks,Its hard to unserstand. In generay you can define any default values for RS
Reports if you like. Same for SP, but I think you should be careful wit
datatype there.
Markus Pöhler
netpoint-edv gmbh
Germany
"Myles" wrote:
> We have a custom .net webb app front end that uses the SOAP methods for rs.
> For reasons that are not important to explain, we have several different
> types of parameters..we have a couple that we would like to have the user do
> an external search (there are too many values to set the parameter up to run
> an sp to display allowable values) so we want to create a 'search' page for
> them to narrow down / search and pick an appropriate value. The SP that
> supplies the report(s) needs to have a value passed to run the report (we
> can't filter after the fact). My question is that no 2 databases (out of 60
> + databases) are going to have the same value(s) for these particular
> parameters - but I need to set the parm up to have a default value...I
> believe... . Does the default value have to be an allowable value? Or can
> you put something in as a place holder or a phrase to look for in code so we
> can take the neccessary next step (open search page)?
> I hope I am explaining this ok...
>
> Thanks,

More MSDE Installation Problems.

I too am having problems with MSDE with VS.NET. I am trying to install the Dunwamish Enterprise Example App and I keep getting the following error: "[DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied." Also, most of the exa
mples refer to the PUBS database which I can't find anywhere. Did it not come wrapped up in MSDE?
I have W2K Pro, Visual Studio .NET 2003, MSDE loaded v8.00.760. Is there anybody out there that can help a .NET newbie out. The guy the company had hired to come in here and start our .NET efforts has quit and dumped all this in my lap. I am familiar with
Visual Studio 6; however, we hav always used Sybase Anywhere before. Any help will be appreciated.
Kenneth A. Jinks, Jr.
Lead Project Software Engineer
LabCorp CPG - LCM Development
Huntsville, AL
> examples refer to the PUBS database which I can't find anywhere.
http://www.microsoft.com/sql/downloads/default.asp#code
http://tinyurl.com/2oyvh
Maybe you didn't permit network access, set a sa-password, didn't disable
antivirus or anything like that?
Regards,
Lars-Inge Tnnessen
www.larsinge.com

monthtodate and yeartodate

I am creating an application with a report in VB .NET. I have all the data showing up and now I need to create a summary at the bottom of the report to show current, Month To Date, and Year To Date information for formulas and database entries. I have it working to a point. My question is what is the format to write a formula for these two fields to keep the data current even if I am not working in the current month. If I am doing November data it seems to work, but if my date is in October I am not getting the data for October, I am still getting November data. I looked in the Crystal Help file and they suggest to change the date on the computer in the control panel. hmmm.... Not very professional if you ask me. Any help would be great.Look under Report menu in Crystal - you can change the date (last option is "Set Report Print Date/Time").
Change that date and MonthToDate etc will use it for their dependent calculations.
Check the object model under .NET and hopefully that property is exposed and is able to be modified by your .NET code.

dave|||Dave,

Thank you for the reply. I have tried that, but I am still having problems. I can get the current data to show up in the MTD formula but it is not adding it the the previous data for the month. I am sure it is a placement problem but I could be worng there. I am also not getting an accurate sum for a formula that is a division problem. I have ordered a new book to help, but any input you may have would be great. Thank you.

Julie|||Hi Julie,

Here's what I would do:
You know you can create more than one group based on the same report field? Well, you can.
So, say you already have a group to show details date by date, create a group above this based on the same field, but in the "Change Group" options, set the "for each section" option to select 'for each year'.
That will give you a section that will show summary data based on the whole year.

Add another group based on the date, and in the "Change Group" options, set the "for each section" option to select 'for each month'.

The groups may not be in the order you want, so use Report menu, and then Change Group Expert, to define the order:
Should be Group 1 : year
Group 2 : month
Group 3 : day

then create your summaries.

Now in the case of one of my reports, I want data from the current month, even though it's incomplete, so in my report formula for selection by line (Report menu, Edit Selection Formula, Record...)
I use something like this:
(({DATEFIELD} > DateAdd("m", -12, {?Pm-?ReportDate})
and
{DATEFIELD} <= {?Pm-?ReportDate}).
Just bear in mind that if date is 15/12/04, report then goes back to 16/12/03.

IF you want completed months then you need a formula field first, something like this (we'll call it EndDate):

' get first day of current month.
formula = DateValue(Year(CurrentDate), Month(CurrentDate), 1

Then in my record selection formula I'd do this:
(({DATEFIELD} >= DateAdd("m", -12, {@.EndDate})
and
{DATEFIELD} < {@.EndDate}). ' note less than, because must be less than first of current month.........

Hope this helps

dave

Month Days

How can you find the days in a particular month in crystal reports v10.
(e.g. as in vb.net MYDAYS_MONTH = DateTime.DaysInMonth({?Year}, {?Month}))Try Day(Datefield)

Monitoring service broker queues through a .NET process

Is there a way for a .NET application to receive a notification when a service broker queue has been updated with a new message? I tried using SqlDependency on an SB queue but I got an "invalid" error in my notification handler.

Such a notification would be much better than having to poll the queue every N seconds.

Thanks

Issue a WAITFOR(RECEIVE.. ) on the queue with no timeout? Make sure you set the CommandTimeout on the SqlCommand to infinite.|||I will try that. Thanks very much

Monitoring Database hits

In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?

JontyMC:

In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?

If you are using a data access layer, you can write some code to log when the data is connection/executed. You can log this to a text file, or a database, or any other external data source.

Monitoring blocking...

Is there a way to be notified automatically when blocking
occurs (either via net send or e-mail and maybe even have
that written to the NT event log)?
ThanksHi Rob
Blocking happens all the time, but it is usually for so short a period of
time that if you had a notification every time you thing there was another
spam virus. There is no automatic way to e notified about blocking, but here
are some avenues you might consider:
Set up a script to run a loop, that captures sysprocesses output and saves
it in a table. Compare the waittime column from one run to the next, if the
blocked column is >0. If the waittime is above some threshhold, send a
notification.
Set lock timeout to 0 for each connection, so that any time a block occurs,
the process will get an error 1222 and stop. You will have to add code to
retry if needed. You can then set up an alert to notify you when error 1222
occurs, or you could change the definition of error 1222 to include writing
it to the event log. Getting every session to set the lock timeout value may
require changing a lot of application code.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <rhchin@.hotmail.com> wrote in message
news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> Is there a way to be notified automatically when blocking
> occurs (either via net send or e-mail and maybe even have
> that written to the NT event log)?
> Thanks|||Oops, I also meant to suggest you take a look at these KB articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509&Product=sql2k
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;283725&Product=sql2k
INF: How to View SQL Server 2000 Blocking Data
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uOe6jK2jDHA.2964@.tk2msftngp13.phx.gbl...
> Hi Rob
> Blocking happens all the time, but it is usually for so short a period of
> time that if you had a notification every time you thing there was another
> spam virus. There is no automatic way to e notified about blocking, but
here
> are some avenues you might consider:
> Set up a script to run a loop, that captures sysprocesses output and saves
> it in a table. Compare the waittime column from one run to the next, if
the
> blocked column is >0. If the waittime is above some threshhold, send a
> notification.
> Set lock timeout to 0 for each connection, so that any time a block
occurs,
> the process will get an error 1222 and stop. You will have to add code to
> retry if needed. You can then set up an alert to notify you when error
1222
> occurs, or you could change the definition of error 1222 to include
writing
> it to the event log. Getting every session to set the lock timeout value
may
> require changing a lot of application code.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rob" <rhchin@.hotmail.com> wrote in message
> news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> > Is there a way to be notified automatically when blocking
> > occurs (either via net send or e-mail and maybe even have
> > that written to the NT event log)?
> >
> > Thanks
>

Monitoring a Read/Write access to a DB

Hello,
I am trying to write a VB.NET application to do this:
1. Count the number of Read/Write accesses to a DB on the server and,
2. Get the last access time of the DB since its creation.
Tried looking it over at MSDN...tried googling...but have failed. Any
inputs would be more than valuable.
Thanks.
_Sourabh
Thanks Geoff...
Re: Counting number of IO Accesses per DB
From: Geoff N. Hiten
Date Posted: 4/13/2004 3:02:00 PM
The basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
moharss@.auburn.edu (Sourabh) wrote in message news:<cff778d4.0404131053.dd7821b@.posting.google.c om>...
> Hello,
> I am trying to write a VB.NET application to do this:
> 1. Count the number of Read/Write accesses to a DB on the server and,
> 2. Get the last access time of the DB since its creation.
> Tried looking it over at MSDN...tried googling...but have failed. Any
> inputs would be more than valuable.
> Thanks.
> _Sourabh

Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

Or

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul

Money format error

I use asp.net 2.0 and sql server 2005 for a web site (and Microsoft enterprise library).When I run aplication at local there is no problem but at the server I take this error:

Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.shopProducts', column 'productPrice'. Use the CONVERT function to run this query.

productPrice coloumn format is money. It was working correctly my old server but now it crashed.How can I solve this problem? And why it isn't work same configuration?

My code:

decimal productPrice;

Database db =DatabaseFactory.CreateDatabase("connection");

string sqlCommand ="update shopProducts set .................,productPrice='" + productPrice.ToString().Replace(",",".") +"',................where productID=" + productID +" ";

db.ExecuteNonQuery(CommandType.Text, sqlCommand)

Decimal should be able to covert to SqlMoney implicitly, why you convert the price to string before update it to the table? However, it's better to useParameterized Queries as possible as you can.