DB access works in debug but fails when hosted

I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.

When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.

But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails.

What could I doing wrong could I be missing a setting in IIS? Any ideas??

Here's my webconfig if that helps at all:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="studentprofilesConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<roleManager defaultProvider="AspNetWindowsTokenRoleProvider" />
<compilation debug="true" defaultLanguage="c#" /></system.web></configuration

There is a posibility that if SQL server is not configured to accept remote connection it may fail. To enable remote connection follow this steps

Under SQL2005 program menu follow configuration Tools =>SQL Server Surface Area Configuration => Select Server Surface Area Configuration for Services and Connections.

It will lead to a window with a treeview select Remote connections.Then Select Local and Remote Connections.

Now if the error is due to Remote configuration issue it will be resolved

DB access from master page

I am using content and master pages. The content page has to query the database to get the master page name (among other things), which is done in 'Page_PreInit', and then the master page has to query the database to get some layout options, done in 'Page_Load'.

Is it possible for the master page to use the existing open connection, or am I forced to close the connection in the content page and open up a new connection in the master page? I've tried various things without success.

The second question is, does it matter? Will opening and closing two connections be much slower than opening and closing one?

In my opinion you should open a connection as late as possible and close it as early as possible. So I think you should not use the same connection, instead close the one as soon as its work gets done (dispose it or use theusing block) and then create a new one for the other work.

HTH,

Vivek

|||

Thanks.

I've since done some reading up on connection pools, and I agree with what you are saying. I assumed that opening and closing a connection twice would require twice as much work, but this is not the case.

We live and learn.

|||Yes, connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. Every time a connection is open/closed, the pooler does not actually open/close a connection, thus improves performance. For details you can take a look at this article:Using Connection Pooling
And this article gives some tips on connection pooling performance:
Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

DateTime Problem

i'm having a web forum, i wanna make a page to show all today's posts, so for instance i tried to do this in the SQL statment:

Where Posts.DatePosted Between #09/20/2005# AND #09/22/2005#

but this erroe appeared to me:
Incorrect syntax near '#'
so can you please tell me better way and exact way to do this, thanx too much

That syntax is for Access. Your best bet is something like this:

Where Posts.DatePosted >= '20050920' ANDPosts.DatePosted < '20050923'

|||thanx too much it worked pretty fine, but i made it like this and its perfect:

HAVING MAX(Posts.DatePosted) >= '09/22/2005' AND MAX(Posts.DatePosted) < '09/23/2005'

DateTime Now Problem

Hey, basically i have a database of users and on a certain page load i want to register when they logged onto that page, so i created this snippet of code :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

but that give me the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

So I changed it to :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

as when I looked at previous times in my database there format was "27/08/2007 15:25:45" for example. but that gives me the exact same error can anyone tell me why this doesnt work? / how to get it to work? My datatype for this feild is "datetime". Thanks John

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated= '" + DateTime.Now.ToString() + "' WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

OR , best way is

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

|||

Awesome this :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

way did work the best, for anyone else who is having this problem, thanks loads! John

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>

dates on Page headers

Dates in database are

Ist july 2006
2 nd july 2006
1 st sep 2006

Group by state1

I need to get count of Application nos for Group by state1 and date should be in header for a week. we have to pass date range values from date_range parameter

How do get it. The format is given below:

Sat Sunday Monday .. Friday
Statename 1/7/2006 2/7/2006 3/7/2006 .. 7/7/2006
State1 2 5 7 6



If I do group by date, then dates will not appear in a horizontal manner, Please help me how to proceed furtherTry the cross-tab format for the report.

Rashmi|||cross tab shoul not be used as per the clients requirement

dateadd update works but adds 2 or 3 yrs-not 1

I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead.
Here is the page_load event:

Sub page_load(sender as object, e as eventargs)
 Try
 Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection)
 command.CommandType = CommandType.StoredProcedure
 Dim param0 As SqlParameter = new SqlParameter("@.memberid",SqlDbType.Int)
 param0.Direction = ParameterDirection.Input
 param0.Value = memberid
 command.Parameters.Add(param0)
 connection.Open()
command.ExecuteNonQuery()
 connection.Close()
 myerror.Text = "Thank You! Your account was updated"
 Catch ex As Exception
 myerror.Text = ex.Message
 End Try
End Sub

And here is the SPROC:

CREATE PROCEDURE Updateexpiredate
 (
 @.memberid int
 )
 AS
 UPDATE
 members
 SET
 expiredate=(dateadd(year,1,expiredate))<--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results
 WHERE
 memberID = @.memberID
GO

... I'm assuming you're calling that method or procedure 2 or three time. :)

edited: Step through your app. It could be that it's called multiple times.|||How do I do that?
I don't even know where to begin with that.
Thanks|||The first thing I would do is turn tracing on for the page, and do a trace.write as the command right before the ExecuteNonQuery. This should give you more information on how many times that part of your code is being executed.

If you are not familiar with trace.write, look here:Trace Logging to Page Output. That is a good debugging tool to have in your pocket.

Terri|||What do you use to develop in? Is it Visual Studio, DreamWeaver, WebMatrix, notepad?