Showing posts with label view. Show all posts

Thursday, March 29, 2012

Formula to view records

Hi,
I am having a report which takes a parameter to view records based on the parameter value passed to it.

I need to check the length of the parameter, if the length of the parameter value is "4", it should compare the parameter value with one field value, if the length is more than 4, then it should compare the value with another field and shoe me the records.

I have made the formula, but it doesn't show records, where as there are records in the table.

how could i do that ? Is anybody there, who could help me in that?

Thanks in advance.

Regards

DeepakIf len(parameter)=4 then
--do some work
else
--do some other work

Do you want to do this from Front end application?

Formula problem

env: sql server 2000

objective:
add a formula for an INT column to the FORMULA field or the DEFAULT
VALUE field in DESIGN VIEW.

all of the following attempts failed
IIF (columnName = 0, "1", columnName + 1)
IIF (columnName = 0, 1, columnName + 1)
IIF (0, "1", columnName + 1)

caveat: cannot use IDENTITY
REASON: I'd like have sets of repeatable values for this row, e.g.
set a
1 -- row 1
2 -- row 2
3
4
5
set b
1 -- row 6
2
3
set c
1 -- row 9
2
3
4

Underlying rationale is to support OO design I understand probably it's
going to be huge headache for lots of people down the road but ...

TIA.NickName (dadada@.rock.com) writes:
> env: sql server 2000
> objective:
> add a formula for an INT column to the FORMULA field or the DEFAULT
> VALUE field in DESIGN VIEW.
> all of the following attempts failed
> IIF (columnName = 0, "1", columnName + 1)
> IIF (columnName = 0, 1, columnName + 1)
> IIF (0, "1", columnName + 1)

I will have to admit that I don't understand much of your post.
IIF is Access/VB, but I guess you know that.

In case not, the syntax in SQL server is

CASE WHEN columnName = 0 THEN 1 ELSE columnName + 1 END

Then again, this can be simiplied to:

columnName + 1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Oops, haven't touched db for a couple of months, now it seems that I
totally forgot everything or mix everything. Ok, joke aside, case stmt
does not seem to work, strange.|||Ahe, because of the default NULL value.

Formula in a View

I'm trying to perform the following within a view. I'm not getting the
values which I would expect. I'm assuming I have a data type problem where
some precision is being dropped. Could someone give me a heads-up on what
I'm doing wrong? I need full precision.
SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*((1
00/10000)^2))/100)
* MarketValue)/ SUM(MarketValue) as [Convexity],
Thanks,
Tom Woods
The Baker Group, LP"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
>
What are the types of those columns?
Integral types use integer division.
David|||All columns in the formula are 9(12,8).
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uyQL80TvFHA.1560@.TK2MSFTNGP09.phx.gbl...
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> What are the types of those columns?
> Integral types use integer division.
> David
>|||Hi Tom
This piece of the expression (100/10000) will be carried out as integer
division, and yield 0. If you want decimal results, make at least one of the
operands a decimal (100.0/10000)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
> Thanks,
> Tom Woods
> The Baker Group, LP
>|||When I change it to the following I get an error. Could the problem be with
the exponent? Is there a different way of performing the exponential?
((100.0/10000)^2)
BTW, I get the following error when changing 100 to 100.0
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 403: Invalid operator for data type. Operator equals boolean XOR, type
equals numeric.
--
OK
--
Thanks,
Tom
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi Tom
> This piece of the expression (100/10000) will be carried out as integer
> division, and yield 0. If you want decimal results, make at least one of
> the operands a decimal (100.0/10000)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
>
>|||^ operator is bitwise exclusive OR operation for integers. For getting
exponential values as results you might want to try POWER function like:
SELECT POWER( 100.0/10000, 2 )
Anith|||Can you use (1.0/10000) instead of ((100.0/10000)^2)?
Perayu
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
> When I change it to the following I get an error. Could the problem be
> with the exponent? Is there a different way of performing the
> exponential?
> ((100.0/10000)^2)
>
> BTW, I get the following error when changing 100 to 100.0
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 403: Invalid operator for data type. Operator equals boolean XOR,
> type equals numeric.
> --
> OK
> --
> Thanks,
> Tom
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||I was able to get it to work by using the POWER function and also changing
the integer values to have precision.
Thanks,
Tom
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uPKrC0UvFHA.908@.tk2msftngp13.phx.gbl...
> Can you use (1.0/10000) instead of ((100.0/10000)^2)?
> Perayu
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
>

Monday, March 26, 2012

Formatting With Multiple Columns

Hello,

Is it possible to view the contents of a report in 2 columns?

for

example, if i had a report contained a table with 1 column, could I,

rather than epanding that table column down, use some of the room to

the right of the table to "repeat" the columns in the table to show

more of the data?

In crystal reports you could achieve this effect by using the "format with multiple columns" option.

If you need a better explanation please let me know.

Thanks in advance.All I want to know is confirmation that this can't be done.

thanks

Friday, March 23, 2012

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?
Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

Wednesday, March 21, 2012

formatting numbers

I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53

Any help is appreciated - ABSQL Server does not format output. That is the job of your interface (Access ADP, in this case).

Set the format of your form control or report field to display as currency.|||you could something like SELECT CAST(3.5553 as decimal(10,2)) but this rounds up (3.56). I would handle this in your VBA with a formatcurrency('your variable here',2).

This might round too. Or it might just cut off your percision. I forget. Test.

Monday, March 19, 2012

formatting columns in a view

I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.

The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.

Is this even possible? Any help would be appreciated.

You should be able to cast them in the select clause of your view (Assuming ALL of the data in those columns is numeric)

CREATE VIEW v1

SELECT CAST(charcol1 AS Decimal(18,2)) AS numcol1, ...

|||Perfect. Thank you!

Monday, March 12, 2012

FormatPercent Crashes

Hello,
When I try to view my result as a percent (using FormatPercent) and then
preview the report, it crashes.
Any ideas?
I am running SQL Server 2005 (no Service Packs) on XP Pro.
Thanks.Are you setting the FormatPercent in the textbox expression?
If so, have you tried setting it on the properties of the textbox
instead? The format code is P|||I made the change like you suggested, and it worked!
Thank you.
But should changing it in the Expression make it crash?
"Mal" <mitch.collingwood@.gmail.com> wrote in message
news:1148328674.025790.103000@.y43g2000cwc.googlegroups.com...
> Are you setting the FormatPercent in the textbox expression?
> If so, have you tried setting it on the properties of the textbox
> instead? The format code is P
>

Wednesday, March 7, 2012

Format parameter date

Hello

My problem is with parmater format date, when i input for example the date format yyyy/mm/dd and then press in boton view report this foramt change to dd/mm/yyyy, this succed in all machines with all configuration in the regional configuration.

When i change this format in th configuration of reporting services.

Thank you

In your query add a convert to your parameter field.

convert(datetime, @.parameter, 111)

|||

Thank you for your request, but the problem isn't the database, i thnik the problem is the configuration of Reporting Services, because this software function with date format mm/dd/yyyy and i don't know when change this format. For example when i type in the paramater of the report 2006/08/06 (6 august 2006) and press the boton "show report" the screen refresh and then show 08/06/2006 (8 june 2006) and this i don't know why show. This is y problem.

Thank you

|||

The interpretation of the value typed in for a date in the report parameters area is based on the client browser locale. The display of the date within the body of the report is based on the language property of the report (which can be bound to the client language if the report developer wants it to).

|||Are you setting the various lanuage settings for Reporting Services? These settings are very configurable. See the following link for more information.

International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

Format numbers in a view

I need to show the following numbers as such in a view (in a 15.3 format)...
12.3 as 000000000000012.300
10 as 000000000000010.000
12.3367 as 000000000000012.337
Is there an easy way to do this using a Cast ?
Thanks !Not on 2000 AFAIK. Only thing I can think of is converting to string.
Offcourse, real question is why do you format values on SQL Server? You
should do it in presentation layer.
MC
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||print replicate( '0', 19 - len( cast( cast( round( 12.3367, 3 ) as
decimal( 25, 3 ) ) as varchar(30) ) ) ) +
cast( cast( round( 12.3367, 3 ) as decimal( 25, 3 ) ) as varchar(30) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||I've needed to do this type of thing a lot of times because of fixed length
data feeds, its less maintanence to just use SQL Server, otherwise you'd
need to write some code using something like C# or something which imho is
complete over kill and adds to life cycle burden (dev, support,
maintanence).
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23UTFc8GZGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Not on 2000 AFAIK. Only thing I can think of is converting to string.
> Offcourse, real question is why do you format values on SQL Server? You
> should do it in presentation layer.
>
> MC
>
> "Rob" <rwchome@.comcast.net> wrote in message
> news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>|||Rob
declare @.d decimal (25,3)
set @.d=12.3367
select right('0000000000000000000'+cast(@.d as varchar(19)),19)
Or you can use REPLICATE function as Tony suggested
"Rob" <rwchome@.comcast.net> wrote in message
news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>I need to show the following numbers as such in a view (in a 15.3
>format)...
> 12.3 as 000000000000012.300
> 10 as 000000000000010.000
> 12.3367 as 000000000000012.337
> Is there an easy way to do this using a Cast ?
> Thanks !
>|||Thanks Tony... but I get Invalid or Missing Expression when i place the
code into a column of a view... any ideas ?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u62Xp9GZGHA.4936@.TK2MSFTNGP05.phx.gbl...
> print replicate( '0', 19 - len( cast( cast( round( 12.3367, 3 ) as
> decimal( 25, 3 ) ) as varchar(30) ) ) ) +
> cast( cast( round( 12.3367, 3 ) as decimal( 25, 3 ) ) as varchar(30) )
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Rob" <rwchome@.comcast.net> wrote in message
> news:DeOdnY8kwNZD7drZRVn-qg@.comcast.com...
>|||Hmm, I'll have to think about that. I was concerned about performanse
issues, perhaps too concerned :).
MC
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23ep4e$GZGHA.4752@.TK2MSFTNGP02.phx.gbl...
> I've needed to do this type of thing a lot of times because of fixed
> length data feeds, its less maintanence to just use SQL Server, otherwise
> you'd need to write some code using something like C# or something which
> imho is complete over kill and adds to life cycle burden (dev, support,
> maintanence).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23UTFc8GZGHA.3936@.TK2MSFTNGP05.phx.gbl...
>|||Tony, never mind... I did not drop the Print statement ... My Bad !
Thanks, Rob
"Rob" <rwchome@.comcast.net> wrote in message
news:WvadnRdnPONf5trZRVn-gg@.comcast.com...
> Thanks Tony... but I get Invalid or Missing Expression when i place the
> code into a column of a view... any ideas ?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:u62Xp9GZGHA.4936@.TK2MSFTNGP05.phx.gbl...
>|||C'mon fess-up,I doubt you were really thinking 'performance':)
But if you really were then you are really opening pandora's box on your
client and closing it on the server:)
'why do you format values on SQL Server' - this sounds like a Celkoism,
sounds good but is silly:)
www.rac4sql.net
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:e53wxQHZGHA.5116@.TK2MSFTNGP03.phx.gbl...
> Hmm, I'll have to think about that. I was concerned about performanse
> issues, perhaps too concerned :).
> MC
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23ep4e$GZGHA.4752@.TK2MSFTNGP02.phx.gbl...
otherwise
>|||A) I DID think about performance and usage of indexes and execution plans
and ordering and stuff that goes to hell with converting. If you dont, well,
your choice.
B) client can and should handle formatting, not just because it can but
there are reasons such as localizing, string manipulation and similar
'small' stuff that client can handle better then sql server. If it is silly,
perhaps you would care to explain further? I feel a bit stupid at the
moment.
If you just said I worry too much about theoretical side and too little
about practical side I would certainly agree that it is possible.
MC
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:Oa8115OZGHA.4944@.TK2MSFTNGP02.phx.gbl...
> C'mon fess-up,I doubt you were really thinking 'performance':)
> But if you really were then you are really opening pandora's box on your
> client and closing it on the server:)
> 'why do you format values on SQL Server' - this sounds like a Celkoism,
> sounds good but is silly:)
> www.rac4sql.net
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:e53wxQHZGHA.5116@.TK2MSFTNGP03.phx.gbl...
> otherwise
>

Friday, February 24, 2012

Format data in a column

I need to format the data in 2 columns in a SQL view. Each column contains
numeric values. They both need to be formatted so that they are padded with
leading zeros if needed. Example: Column 1 is set to a length of 9, so if
the value of 999999 it needs to be formatted as 000999999. Column 2 is the
same way but has a lenght of 15. Can someone help me out with this? ThanksForgot to mention I am using a MS Access 2003 front end and a SQL DB.
"CD" wrote:
> I need to format the data in 2 columns in a SQL view. Each column contain
s
> numeric values. They both need to be formatted so that they are padded wi
th
> leading zeros if needed. Example: Column 1 is set to a length of 9, so i
f
> the value of 999999 it needs to be formatted as 000999999. Column 2 is th
e
> same way but has a lenght of 15. Can someone help me out with this? Thanks[/colo
r]|||Try,
update t1
set
c1 = right(replicate('0', 9) + c1, 9),
c2 = right(replicate('0', 15) + c2, 15)
AMB
"CD" wrote:
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||CD,
While we can write the T-SQL to do this, I'd recommend using Access 2003 to
handle the formatting of the data - more versatile.
HTH
Jerry
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||You can create a user degined function for this:
CREATE FUNCTION fn_0Int
(
@.input as int,
@.CharLength as int
)
returns varchar(255)
AS
begin
return
right(replicate('0',@.CharLength)+convert
(varchar(255),@.input),@.CharLength)
end
GO
print dbo.fn_0Int(999999,9)
000999999
print dbo.fn_0Int(999999,15)
000000000999999
"CD" <CD@.discussions.microsoft.com> wrote in message
news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
> Forgot to mention I am using a MS Access 2003 front end and a SQL DB.
> "CD" wrote:
>|||Jerry,
I had the same thoughts, but I couldn't find where I could do this.
"Jerry Spivey" wrote:

> CD,
> While we can write the T-SQL to do this, I'd recommend using Access 2003 t
o
> handle the formatting of the data - more versatile.
> HTH
> Jerry
> "CD" <CD@.discussions.microsoft.com> wrote in message
> news:7680B761-2580-42A1-B555-B78E776A5B10@.microsoft.com...
>
>|||On Wed, 28 Sep 2005 11:55:03 -0700, CD wrote:
>I need to format the data in 2 columns in a SQL view. Each column contains
>numeric values. They both need to be formatted so that they are padded wit
h
>leading zeros if needed. Example: Column 1 is set to a length of 9, so if
>the value of 999999 it needs to be formatted as 000999999. Column 2 is the
>same way but has a lenght of 15. Can someone help me out with this? Thanks[/color
]
CREATE VIEW FormattedData
AS
SELECT RIGHT(REPLICATE('0', 9) + CAST(Column1 AS varchar), 9) AS Column1
, RIGHT(REPLICATE('0',15) + CAST(Column2 AS varchar),15) AS Column2
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Sunday, February 19, 2012

Form view/sqlexpress/

i getting the following error message in runtime, i have no idea what my username and password is for my computer, does anyone know what this error message means, and how i can get my username and password.

Server Error in '/' Application.

Cannot open user default database. Login failed.
Login failed for user 'ACER-0BCB4DEC3A\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'ACER-0BCB4DEC3A\ASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'ACER-0BCB4DEC3A\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735107 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

P.s Also does any one know how to enable cookies for the login control on website.

i have noticed within the objectdatasource wizard, that something called profile is there, and this is where you specify the cookies, sessions etc. But i dont know where to start with this. Can anyone help?

thanks

andy

Your SQL problem could be a number of things.

You can try things like right clicking on your connection to the database inside VS2005 express and choosing close connection to see if maybe you're taking up the only connection, or you can create a new user for your sql express file and then change your connection string to use that instead of (presumably) integrated security.

Do you have SQL server studio express, with it you can probably get a better handle on your SQL express installation, including setting up users/passwords.

http://www.microsoft.com/downloads/details.aspx?FamilyID=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&DisplayLang=en

In regards to your cookies, by default they are enabled, but the forms authentication timeout defaults to 0 seconds, meaning that as soon as you close your browser it will go away. If you add/change your timeout value like I've done below, you'll have cookies able to last for as long as you set it (I choose 20160 for 2 weeks).

formsname=".ASPXAUTH"loginUrl="login.aspx"defaultUrl="default.aspx"protection="All"timeout="20160"path="/"requireSSL="false"slidingExpiration="true"cookieless="UseDeviceProfile"domain=""enableCrossAppRedirects="false">

forms>|||Do I put this inside the web.config file or my login.aspx file|||

Here is an example web.config, hope this helps!

<!-- Note: As an alternative to hand editing this file you can use the web admin tool to configure settings for your application. Use the Website->Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in \Windows\Microsoft.Net\Framework\v2.x\Config --><configuration> <appSettings/> <connectionStrings> <add name="MainConnectionString" connectionString="Password={password};Persist Security Info=True;User ID={userid};Initial Catalog={databasename};Data Source={ipaddress of server or what have you}" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web><!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. Visual Basic options: Set strict="true" to disallow all data type conversions where data loss can occur. Set explicit="true" to force declaration of all variables. --> <compilation debug="false" strict="false" explicit="true" /> <pages> <namespaces> <clear /> <add namespace="System" /> <add namespace="System.Collections" /> <add namespace="System.Collections.Specialized" /> <add namespace="System.Configuration" /> <add namespace="System.Text" /> <add namespace="System.Text.RegularExpressions" /> <add namespace="System.Web" /> <add namespace="System.Web.Caching" /> <add namespace="System.Web.SessionState" /> <add namespace="System.Web.Security" /> <add namespace="System.Web.Profile" /> <add namespace="System.Web.UI" /> <add namespace="System.Web.UI.WebControls" /> <add namespace="System.Web.UI.WebControls.WebParts" /> <add namespace="System.Web.UI.HtmlControls" /> </namespaces> </pages><!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms" > <forms name=".ASPXAUTH" loginUrl="login.aspx" defaultUrl="default.aspx" protection="All" timeout="20160" path="/" requireSSL="false" slidingExpiration="true" cookieless="UseDeviceProfile" domain="" enableCrossAppRedirects="false"></forms> </authentication><!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors> --> </system.web></configuration>

|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

addname="ASPNETDBConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Inetpub\wwwroot\vwd test\web-css-take-it-further - modified in vwd\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

addname="ASPNETDBConnectionString2"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Inetpub\wwwroot\vwd test\web-css-take-it-further - modified in vwd\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

addname="ASPNETDBConnectionString3"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Inetpub\wwwroot\vwd test\web-css-take-it-further - modified in vwd\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

formname=".ASPXAUTH"loginUrl="Login.aspx"defaultUrl="threecolumn.aspx"protection="All"timeout="20160"path="/"requireSSL="false"slidingExpiration="true"cookieless="UseDeviceProfile"domain=""enableCrossAppRedirects="false">

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>

am|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>

am getting this error message at run time:|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>

am|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>

am getting this error message at run time:

Server Error in '/' Application.

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Unrecognized element 'form'.

Source Error:

Line 12: <roleManager enabled="true" />Line 13: <authentication mode="Forms" >Line 14: <form name=".ASPXAUTH" loginUrl="Login.aspx" defaultUrl="threecolumn.aspx" protection="All" timeout="20160" path="/" requireSSL="false" slidingExpiration="true" cookieless="UseDeviceProfile" domain="" enableCrossAppRedirects="false">Line 15: </form>Line 16: </authentication>

|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>|||

This is my web config file now, what do i put in the form name and path fields?:

xmlversion="1.0"?>

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

connectionStrings>

system.web>

roleManagerenabled="true" />

authenticationmode="Forms" >

form>

authentication>

compilationdebug="true"/>

anonymousIdentificationenabled="true" />

profile>

properties>

addname="PostalCode"type="System.String"allowAnonymous="true" />

properties>

profile>

system.web>

system.net>

mailSettings>

smtpfrom="andy.hillyard@.ntlworld.com">

networkhost="smtp.ntlworld.com"password=""userName="" />

smtp>

mailSettings>

system.net>

configuration>

am getting this error message at run time:

Server Error in '/' Application.

Configuration Error

Parser Error Message:Unrecognized element 'form'.

Source Error:

Line 12: <roleManager enabled="true" />Line 13: <authentication mode="Forms" >Line 14: <form name=".ASPXAUTH" loginUrl="Login.aspx" defaultUrl="threecolumn.aspx" protection="All" timeout="20160" path="/" requireSSL="false" slidingExpiration="true" cookieless="UseDeviceProfile" domain="" enableCrossAppRedirects="false">Line 15: </form>Line 16: </authentication>

|||Very strange, are you using .net 2.0 or 1.1? Are you testing your project using the internal VS2005 web server or IIS?|||visual web developer express, iis|||

Ok,Its possible your web application in IIS isn't set to asp.net 2.0.

In IIS, if you right click on the site and choose properties, you should see an ASP.NET tab. You should make sure its set to ASP.NET version 2.0.50727

If you're using IIS on your local box then that should be it. If you're using IIS on a web server, then you might have multiple web applications. If you "do" have multiple web applications on an external web server inside of IIS, you have to make sure that your 2.0 apps aren't sharing the same Application_pool with a 1.1 application. I can give you more info once I get a better handle on what you're doing.

Thanks

|||

yes i am using the latest version of asp.net.

using iis web server on my local box - http//localhost.

On your last point, this may be the case, as i have created more than one virtual directory in vwd.

|||

ACK! I apologize, I didn't notice at first that you left off the S from forms. its meant to be.

credentialspasswordFormat="SHA1"/>

forms>

I think it was one of those things where it was "so" close to being right I couldn't immediately notice the problem. Hopefully that helps.

Thursday, March 29, 2012

Formula to view records

Formula problem

Formula in a View

Monday, March 26, 2012

Formatting With Multiple Columns

Friday, March 23, 2012

Formatting query/view in SQL Server Management Studio (2005)

Formatting query/view in SQL Server Management Studio (2005)

Formatting query/view in SQL Server Management Studio (2005)

Wednesday, March 21, 2012

formatting numbers

Monday, March 19, 2012

formatting columns in a view

Monday, March 12, 2012

FormatPercent Crashes

Wednesday, March 7, 2012

Format parameter date

Format numbers in a view

Friday, February 24, 2012

Format data in a column

Sunday, February 19, 2012

Form view/sqlexpress/

Server Error in '/' Application.

Cannot open user default database. Login failed.
Login failed for user 'ACER-0BCB4DEC3A\ASPNET'.

Server Error in '/' Application.

Configuration Error

Server Error in '/' Application.

Configuration Error

Generate sql

Blog Archive

About Me