Help with connectivity - SQL Server does not exist or access denied

am using VS2003 asp.net 1.1 on Windows 2003 sever to access another network server SQL Db.

This is the error message...

SQL Server does not exist or access denied.

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: SQL Server does not exist or access denied.

Source Error:

Line 34: 'execute query and return data readerLine 35: TryLine 36: objHelperConnection.Open()Line 37: RunSPReturnDR = objHelperCommand.ExecuteReader(CommandBehavior.CloseConnection)Line 38: 'clean up

Source File:c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb Line:36

Stack Trace:

[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.CreateConnection() System.Data.SqlClient.ConnectionPool.UserCreateRequest() System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() chef.dbhelper.DBTools.RunSPReturnDR(String strSP, SqlParameter[] params) in c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb:36 chef.Login.Authenticate(String sUserName, String sPassWord) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:168 chef.Login.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:90 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

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

Ps : This app works on Windows XP and W2k

Thanks for any suggestions.

This can be caused by a couple of things. Microsoft has a comprehensive article on their site descibing the causes and fixes of your error:http://url123.com/nwsp4

sql

HELP with connection string PLEASEEEE!

hi, I'm kind of really new to this. Trying to learn asp.net 2.0. Have been designing a website, and, so far so good, I can deploy the files, but I can't get the synthax right for the connection string. in my web.config file the connection string works on the local computer, but I can't figure out how to change the data source part:
this is what I have in my web convig file
<add name="Database2ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Alexi\My Documents\Delivery\web\App_Data\Database2.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" /
the the wrox book says I have to have the|datadirectory|
does it mean I have to write the full http address on the server?
and I can't figure out what to substitute in for the ".\sqlexpress" part...

my host has ms sql server 2000 and my sql. I asked for their support and the gave me a weird looking connection string and claimed that it worked...

<%@. LANGUAGE = JScript %>
<% var oConn;
oConn = Server.CreateObject("ADODB.Connection");
oConn.Mode = 3
oConn.Open("Provider=SQLOLEDB;Server=203.89.181.78;Database=alexeyka_yah_1951com_;UID=support;PWD=test123;");
%>
'

How can I, and where, copy my existing databse, and change the connection string?

Would really appreciate any help, perhaps buy a new book from the programmers in gratitude.

there is nothing wrong with connection string - the problem is that you are using database file with SQL express and you host SQL 2000 - so they will not be able to attach database file the way SQL Express does...

few days ago i posted why not to use database file with project that will go to shared hosting here:
http://forums.asp.net/thread/1375347.aspx - in the future login to SQL (express is cool) and create database then backup it and restore on your web hosting's SQL server

More info about connection strings you will find here:
http://www.connectionstrings.com/

If the database you have is empty no real data, then copy databse structure to new server change database2ConnectionString and you are good to go

your connections strin g will look like this

<add name="Database2ConnectionString" connectionString="Data Source=203.89.181.78;Initial Catalog=alexeyka_yah_1951com_;User ID=support;Password=test123;"
providerName="System.Data.SqlClient" />

BTW if that is real user pwd - CHANGE IT

|||

Hey, thank you so much for you help. Sorry for the late reply - didn't

The database does connect now, so all I have to do now is to figure out how to actually copy my database file to server.

Alexi;)

|||

if they offer SQL 2005 you should not have any problems. If not and you have to use SQL 2000 read this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=71448&SiteID=1

|||

Hey, thanks again.

I was able to create an identical blank database table on the server in their asp.net enterprise manager. Yes, the do only have 2000 version.

Then, in VWD express I changed datasource control to point to the connection string that connects to the db on the server - it worked.

I'm struggling to contect to the database remotely, will hopefully figure something out.

Cheers again,

Alexi

|||

" I'm struggling to contect to the database remotely " ? - IF the hosting company accepts remote connections you can use free tool like SQL Server Managment Studio Express Edition to connect and work on you database. Keep in mind that many hoster block remote connections so you can only make changes to database design via online Enterprise manager or diffrent tool. If that is the case then simply change connection string in web config when you work on this application to you local machine, and before you do upload to server change it back to real SQL (the 2000 one)

|||

Hi Tom,

Thanks for you reply. I did download the sql server management studio, and asked whether my hosts allowed the remote connection - they don't. so I guess I have to do everything from scratch on the enterprise manager online. I've figured out normal tables, my main concern now will be to generate tables to store username/password info.

Cheers once again,

Alex

|||

one solution is to script everything

or the easy way will be :) :

http://forums.commercestarterkit.org/files/folders/sql_2000_upsize_scripts/entry471.aspx

|||

you can use this tool to add/edit/ delete users, create roles

http://peterkellner.net/2006/07/17/atlasjunectpsource/

make sure you secure it on the server, so u are the only one who can access it

|||

Hi Tom,

Thanks again for you input, have been real busy populating my datatables, so haven't had time to work on logins/users. If you like, you can check out the results of your efforts atwww.takeawaydelivery.co.nz

Will now be working my way into creating loging pages. By the way, the link that you gave me for some reason, it says there was a fata error and it couldn't be viewed? Anyway, thanks for all your help, and I'll let you know if I get around to creating users/etc

|||

the one with script or the one with tool to manage users ? i chcecked both links and are working just fine for me.

|||

Hi,

Seems to be working fine now, that was the tool to manage script. Downloaded it, and ran it. Works fine on my computer, though I can't understand though where are the usernames/passwords are stored. Also, with the tables, do I just run the whole script in the enterprise manager? and how do I link the two? Thanks again,

Alexi

|||

Hi,

an update here - the admin tool works fine on the local computer. I also reconfigured the weg.config file to point towards a remote database for users/passwords:

<membershipdefaultProvider="CustomizedProvider">

<providers>

<addname="CustomizedProvider"

type="System.Web.Security.SqlMembershipProvider"

connectionStringName="MyDB"

minRequiredPasswordLength="5"

minRequiredNonalphanumericCharacters="0" />

</providers>

</membership>

(and added a corresponding connection string).

For some reason though, when I run the table building script it doesn't show that anything is happening? like tables do not appear? any ideas-

thanks

Alexi

|||

Server Error in '/' Application.

---

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

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: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Source Error:

Line 50: private void FindFirstUserName()Line 51: {Line 52: MembershipUserCollection muc = Membership.GetAllUsers();Line 53: foreach (MembershipUser mu in muc)Line 54: {

Source File:c:\Inetpub\vhosts\takeawaydelivery.co.nz\httpdocs\Default.aspx.cs Line:52

Stack Trace:

this is the erro when i go to the tool|||the database script (link i sent u) i used it few times and never had any problems with it - make sure that u can see all tables needed and stored proc.

Help with cascading SELECT

Hello
I have the following scenario that I need to address with which I would
appreciate some help if anyone has the time:
(Simply) I have an asp.net application:
I have 2 tables:
Table 1 is called Categories and is used to hold hierarchical data (Tiers) -
these Tiers can be altered by the users. There may be 10 Categories in Tier
1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will have
sub-categories of Tier 2 etc. etc. through to Tier 5
Table 2 is called Jobs and holds information pertaining to a specific task
that is categorised by Table 1, so this would have single entries that you
should be able to back-track through to Tier 1 of Table 1
This structure is to simply organise data in a highly retrievable structure.
So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs
that are under that category regardless of the sub-category structure
underneath, a click on a category in Tier 2 will filter these, a click on a
sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you ge
t
the picture)
When a category or a job is defined and entered by the user a unique numeric
reference is automatically assigned to that record - it also records the
unique reference of the category above in a field called "Parent_Ref". I
should therefore be able to take the parent record from a Job (which is the
end of the line) and track this back to a sub-category in Tier 3, 4, or 5,
this category record will also have a parent which goes back to Tier 2 and
this will have a recod that tracks back to Tier 1
When the user initially clicks on a Tier 1 Category I need to do a search
for all Table 1 records that have the unique reference of that Tier 1
category in their "Parent_Ref" field.... for all returned records I need to
then do a search through Table 2 for all Jobs that are associated directly
with that category. This will give me 0 to x returns from Jobs. I then need
to requery and find all records from Tier 3 in Table 1 that have one of the
unique references from the Tier 2 records that were just returned in their
"Parent_Ref" field then retrieve all Jobs that relate to these retrieved
records and loop through all the Categories in Table 1 until no more returns
are possible
I hope this is clear !?! I'm sure this is a standard scenario, I have
researched the Select options in transact SQL but can not see the light. I
appreciate that this will probably need to be a stored procedure that will b
e
triggered by my asp.net application where I simply pass the unique ref in to
SQL and let it do the search, but I do not know how to cascade the queries i
n
the above fashion
Thanks
StuartStuart
Can you post DDL+ sample data + expected result?
Like
CREATE TABLE jobs
(
...
...
)
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:133CA7C6-2A90-46C9-94A3-07C51F277741@.microsoft.com...
> Hello
> I have the following scenario that I need to address with which I would
> appreciate some help if anyone has the time:
> (Simply) I have an asp.net application:
> I have 2 tables:
> Table 1 is called Categories and is used to hold hierarchical data
> (Tiers) -
> these Tiers can be altered by the users. There may be 10 Categories in
> Tier
> 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will
> have
> sub-categories of Tier 2 etc. etc. through to Tier 5
> Table 2 is called Jobs and holds information pertaining to a specific task
> that is categorised by Table 1, so this would have single entries that you
> should be able to back-track through to Tier 1 of Table 1
> This structure is to simply organise data in a highly retrievable
> structure.
> So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs
> that are under that category regardless of the sub-category structure
> underneath, a click on a category in Tier 2 will filter these, a click on
> a
> sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you
> get
> the picture)
> When a category or a job is defined and entered by the user a unique
> numeric
> reference is automatically assigned to that record - it also records the
> unique reference of the category above in a field called "Parent_Ref". I
> should therefore be able to take the parent record from a Job (which is
> the
> end of the line) and track this back to a sub-category in Tier 3, 4, or 5,
> this category record will also have a parent which goes back to Tier 2 and
> this will have a recod that tracks back to Tier 1
> When the user initially clicks on a Tier 1 Category I need to do a search
> for all Table 1 records that have the unique reference of that Tier 1
> category in their "Parent_Ref" field.... for all returned records I need
> to
> then do a search through Table 2 for all Jobs that are associated directly
> with that category. This will give me 0 to x returns from Jobs. I then
> need
> to requery and find all records from Tier 3 in Table 1 that have one of
> the
> unique references from the Tier 2 records that were just returned in their
> "Parent_Ref" field then retrieve all Jobs that relate to these retrieved
> records and loop through all the Categories in Table 1 until no more
> returns
> are possible
> I hope this is clear !?! I'm sure this is a standard scenario, I have
> researched the Select options in transact SQL but can not see the light. I
> appreciate that this will probably need to be a stored procedure that will
> be
> triggered by my asp.net application where I simply pass the unique ref in
> to
> SQL and let it do the search, but I do not know how to cascade the queries
> in
> the above fashion
> Thanks
> Stuart|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||
"Uri Dimant" wrote:

> Stuart
> Can you post DDL+ sample data + expected result?
> Like
> CREATE TABLE jobs
> (
> ...
> ....
> )
>
>
> "Stuart" <Stuart@.discussions.microsoft.com> wrote in message
> news:133CA7C6-2A90-46C9-94A3-07C51F277741@.microsoft.com...
>
>|||Stuart
'?
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:147912F5-39C6-4868-B368-ED57D26CC465@.microsoft.com...
>
> "Uri Dimant" wrote:
>|||Thanks very much - seems to be just the thing ! - but in your experience
would this function in an acceptable way with a max of 1000 entries in the
table ?
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>
> ML
> --
> http://milambda.blogspot.com/|||Sorry Uri - I spent some time composing a complete response to your request
only to have this interface bomb out when I posted it ! I appreciate your
time, but the answer from ML actually answers my question...
"Uri Dimant" wrote:

> Stuart
> '?
>
>
> "Stuart" <Stuart@.discussions.microsoft.com> wrote in message
> news:147912F5-39C6-4868-B368-ED57D26CC465@.microsoft.com...
>
>|||The performance of the function depends on two facts:
1) the number of rows in the table; and
2) the average depth of hierarchies.
The best way to know would be to test it on your system. It should peform
adequately with several thousands of rows with an average depth of about ten
levels. When I say "adequatly" that does not mean "perfectly". In SQL 2005
the function should be redesigned using a recursive common table expression
(rCTE), which might improve performance, since it's built into the SQL Serve
r
engine and requires less CPU time to execute.
ML
http://milambda.blogspot.com/

Help with an SQL SELECT statement in ASP page

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuireThe length parameter in your LEFT(String, Length) function is

PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1

PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.

Consider to use a view to put your conditon
[INVENTRY MASTER].BOX_NO LIKE '%821%'|||Let me expand on what I am trying to achieve to see if you can help further.

imagine a table collumn

BOX_NO
--------
JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821
JHIS 00821a
JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821

The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

Any ideas on the best way to do this? Am i on the right lines?|||Why not just

WHERE BOX_NO LIKE '%0821%'

?|||erm I am not sure :) I will put this in now... it seems like this may work if I just add a proceeding '0' to what has been inputted and do a LIKE

If this works I'm going to be very embarrised but pleased to because its been bugging me for hours!

I will let you know if this solves the issue|||Thank You very much! I obviously could not see the wood for the trees!

I changed it slightly because as it stood if 821 was entered 0008210 returned aswell which was not wanted.

by changing the last % to a [a-z] and adding an OR and just looking for %0821 I get back exactly what I need. And its a nice simple select statement!

So I offer you a big thanks and a :) this should keep the managment happy!|||You are welcome.sql

HELP to write stored procedure whose values are calculated automatically in database

Hi frdz,

I m creating my web-application in asp.net with C# 2005 and using sql server 2005.

I have created the stored procedure for the insert,update.

I want to know how to write the mathematical calculations in the stored procedure..

Pls tell me from the below stored procedure were i m making the mistake ??

As the discount and the total amount are not calculated by itself...and stored in the database

How to convert the

@.discpercentnumeric(5,2) to

@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...

ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend

Thanxs in adv...

Hello my friend,

It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.

Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.

Kind regards

Scotty

|||

thanxs for the reply...

can u pls help to calculate this for my above stored procedure its not working..

select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster

|||

Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?

select @.discamt=(@.opbal - @.clbal )* @.discpercent/100

should do.

|||

select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only

I tried with this also but don't work pls help me...

if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end

thanxs for ur replies....

|||

Could it be because either @.opbal or @.clbal is null?

I would use the following: -

IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END

SELECT @.Total

Kind regards

Scotty

|||

thanxs very much again...

Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??

What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution

|||

Hello again Sheenaa,

You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.

Kind regards

Scotty

Help talking to stored proc

Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.

try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}

Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."

Can someone point me to what I’m doing wrong?

Hmm.. You mixed up every thing here..

First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one

And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.

Anyway, try this..

ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here

 @.sessionUservarchar(25),

 @.idOUT varchar(25) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)

return @.idOUT
END

and your code to

cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;

command.Parameters["@.sessionUser"].Value =  Session["UserName"]

 cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;

SqlDataReader reader = cmdReturnID.ExecuteReader();

 reader.close();

 Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;

Even though, the code looks real, its not... I typed by hand, so beware of small errors... 

|||

Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

And here is the modified code:

try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; }

Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!

Help switching to SQL Authentication

At the moment my asp.net app is working ok and I can connect to the database using windows authentication, however i'm trying to use sql authentication on my local computer.

i'm using sql server studio at the moment to manage the database. And tried numerious combinations of things to try and get it working and allow me to connect to the database using SQL authentication but still no luck :(

can anyone give me some rough step by step instructions to setting up the sql username and password for forms authentication and activating it for a certain database?

thanksWhen you say forms authentication, I assume you mean that in your web.config file you have something like this:

<authentication mode="Forms">
<forms loginUrl="Default.aspx" protection="Validation" timeout="300"/>
</authentication
If so, then a connection string that works connecting to sqlserver is this:

<add name="PolarIntegrationConnectionString2" connectionString="Data Source=INSP8600;Initial Catalog=PolarIntegration;Integrated Security=True"
providerName="System.Data.SqlClient" /
Let me know if that doesn't help.

Help Stored Procedures

Stored procedures called from asp pages or from a VB-batch program are not executed completely. There are no error-messages or warnings generated. The SP just stops executing and returns to the initial program as if it was executed normally...

No idea how this can be solved as those SP seem only to have problems when there's a 'lot' of data that has to be processed. When it's about few data the SP executes normally...

Any one who can help?

Thanks!Get return code from sp - it was post here about it before - try to find.|||Can't get these return codes as this problem is only happening on production servers, not on dev-servers...

Supose this must be something to do with settings or so... but no idea which ones...

Thanks for help!|||Originally posted by carlbraems
The SP just stops executing and returns to the initial program as if it was executed normally...
Thanks!

It's that damn miracle thing again...

Can you repeat the problem?

Fire up profiler and see what's going on...

I bet bug in sproc...|||Found solution:
These are queries that don't return a recordset and need to have the following setting switched off: SET NOCOUNT.

Thanks anyway!

Help stored procedures

Hello ,

I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

Procedure looks like so:

ALTER Procedure newUser (
/* Param List */

@.fname varchar(20),
@.lname varchar(20),
@.address1 varchar(20),
@.address2 varchar(20),
@.city varchar(20),
@.province varchar(20),
@.postalCode varchar(7),
@.country varchar(20),
@.phone varchar(10),
@.email varchar(30),
@.receiveNews bit,
@.archive bit,
@.business varchar(10),
@.fax varchar(10)
)
AS

BEGIN TRANSACTION
INSERT INTO customer (
fname,
lname,
address1,
address2,
city,
province,
postalCode,
country,
phone,
email,
receiveNews,
archive,
business,
fax
)
VALUES (
@.fname,
@.lname,
@.address1,
@.address2,
@.city,
@.province,
@.postalCode,
@.country,
@.phone,
@.email,
@.receiveNews,
@.archive,
@.business,
@.fax
)
COMMIT
RETURN

The syntax has been verified correct and does populate my customers table with valid data.

the column that i wish to return is called "pk_customerId".
I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

Any ideas.

Thanks in advancein short, you can do this:

return (scope_identity())

but i'd declare a @.variable and an @.error before your begin tran, then right before your commit do this:

select @.error=@.@.error, @.variable=scope_identity()
if @.error != 0 begin
raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
rollback tran
end
commit
return @.variable

Help Stored procedure working but not doing anything

Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.

I am calling a stored procedure from asp.net and there is acursor in the stored procedure that does some processing on servaltables.

if i run the stored procedure on Query Analyzer it works and does whatit is suppose to do but if i run it from my asp.net/module control itgoes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where iscalled programmatically from the asp.net/module control page.plus itdoes not throw any errors

This is the code from my control
System.Data.SqlClient.SqlParameter [] param={newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(startDate)),newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(endDate)),newSystem.Data.SqlClient.SqlParameter("@.addedby",UserInfo.FullName+ ""+UserInfo.Username)};
stringstr=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=newSystem.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();

SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);

--------THis is the code for my storedprocedure----

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

AS

begin transaction generatePayList

DECLARE @.pensioner_id int, @.dateadded datetime,
@.amountpaid float,
@.currentMonthlypension float,@.actionType varchar(50),
@.isAlive bit,@.isActive bit,@.message varchar(80),@.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

set @.NoOfLoadedPensioners =0

set @.NoOfDeadPensioners=0
set @.NoOfEnrolledPensioners=0
set @.DeactivatedPensioners=0
set @.actionType ="PayList Generation"

DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId

where p.isActive = 1

OPEN paylist_cursor

FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension

WHILE @.@.FETCH_STATUS = 0
BEGIN

set @.NoOfLoadedPensioners=@.NoOfLoadedPensioners+1
if(@.isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@.pensioner_id
set @.DeactivatedPensioners =@.@.ROWCOUNT+@.DeactivatedPensioners
set @.NoOfDeadPensioners =@.@.ROWCOUNT+@.NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@.pensioner_id,getDate(),@.addedby, @.periodStart, @.periodEnd,@.currentMonthlypension)
set @.NoOfEnrolledPensioners =@.@.ROWCOUNT+ @.NoOfEnrolledPensioners
end

-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension
END

CLOSE paylist_cursor
DEALLOCATE paylist_cursor

set @.reportSummary ="The No. of Pensioners Loaded:"+Convert(varchar,@.NoOfLoadedPensioners)+"<BR>"+"The No. OfDeactivated Pensioners:"+Convert(varchar,@.DeactivatedPensioners)+"<BR>"+"The No. ofEnrolled Pensioners:"+Convert(varchar,@.NoOfEnrolledPensioners)+"<BR>"+"No Of DeadPensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')

if (@.@.ERROR <> 0)
BEGIN

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')

ROLLBACK TRANSACTION generatePayList

END

commit Transaction generatePayList
GOYou may need try to use"ExecuteNonQuery() ", not "ExecuteScalar()".|||I used ExecuteNonQuery and it still gave me the same result.it did nothing.
i really need ur help.thanks|||

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

AS

BEGIN TRANSACTION

DECLARE @.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

UPDATE Pensioner
SET isActive=0
WHERE isAlive=0 AND isActive=1

IF (@.@.ERROR=0)
BEGIN

SET @.DeactivatedPensioners=@.@.ROWCOUNT
SET @.NoOfDeadPensioners=@.@.ROWCOUNT

INSERT INTO pensionpaylist(pensionerId,dateAdded,addedBy,periodStart,periodEnd,amountPaid)
SELECT p.pensionerId,getDate(),@.addedby,@.periodStart,@.periodEnd,py.currentMonthlypension
FROM pensioner p
LEFT JOIN pensionpaypoint py on p.pensionerid=py.pensionerId
WHERE p.isActive = 1

IF (@.@.ERROR=0)
BEGIN

SET @.NoOfEnrolledPensioners=@.@.ROWCOUNT
SET @.NoOfLoadedPensioners=@.NoOfEnrolledPensioners+@.DeactivatedPensioners

SET @.reportSummary ="The No. of Pensioners Loaded: "+Convert(varchar,@.NoOfLoadedPensioners)+"<BR>"+"The No. Of Deactivated Pensioners: "+Convert(varchar,@.DeactivatedPensioners)+"<BR>"+"The No. of Enrolled Pensioners: "+Convert(varchar,@.NoOfEnrolledPensioners)+"<BR>"+"No Of Dead Pensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)

END
END

IF (@.@.ERROR<>0)
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')
COMMIT TRANSACTION
END
GO

Should do the same thing as what you had, although run much, much quicker depending on how many Pensioneers you process (The more you process, the faster this will run in comparision). Although, I question the error handling/checking/reporting that you have. I've also changed that part so that if there is an error, only 1 record is inserted into the reportSummary (Your code would have indicated no errors, then another record indicating there was an error, if that is what you really wanted, you'll have to change it)

In any case, I don't see anything in the stored procedure that would cause it to behave differently depending on which environment you are running it in. You've abstracted the sql guts into you sqlhelper class, so we really can't see what you are calling anyhow. I would recommend that you run a sql trace with the sql profiler and see what sql you are generating. Then copy and paste the code into query analyzer/management studio and see if it acts the same there, then play with it until it works. My guess is the problem lies within the SqlHelper class, and it's not generating sql that you would expect from the code you've shown.

|||well thanks but i have run the stored procedure many times in query analyzer and it works fine does what it suppose to do.
but the problem is running it from the application using SqlHelper.executeNonquery that is y i am so confused.

I just don't know what the problem is.
It works in query analyzer but does not do anything when called from the application.
thanks|||I found out the problem.
Iused profiler totrace the events when i call the stored procedure and it gave a sp:cachemiss
so i fixed its working now.
thanks forur help

Help required in ASP.NET code

I am writting a very simple ASP.NET code, I am simlply trying toestablish a connection with the database in SQL Server 2000 and thanclosing the connection but still I am getting an exception,the name ofthe database is mydatabase, SQL Server 2000 is running in the windowsauthenticationfiltered=SSPI. The name of SQL Server 2000 runningonfiltered=xyz,
I checked the name from SQL Server Service Manager.Also I would like tostate here that I also tryed to establish a connection using C# and theconnection was successfully establish which means that there is noproblem with SQL Server also I sucessfully established the connectionwith MS Access and my ASP.NET application which proves that there is noproblem with IIS also.Also please check that the connection stringwhich I am providing is correct or not.The exception which I am gettingis as follows:
////////////

///////////////////////////////////////////////////////////////////
An exception occured while establishing connection
Login failed for user 'xyz\ASPNET'.
////////////////////////////////////////////////////////////////////////////
Please also note that I have a user with the name xyz/Administrator inthe logins of Sql Server 2000. Also note one more thing that the nameof my PC is xyz, I think that there is some problem with the connectionstring which i am providing so please especially check the connectionstring that either I have missed some thing or some thing has gonewrong,also please tell that are there any security restructions forASP.NET to access SQL Server 2000 or some thing like that which I haveto remove.The code is as follows:
//////////////////////////////////////////////////////////////////////////////////////
<%@. Page Language="C#" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
void Page_Load(Object sender , EventArgs e)
{
try
{
SqlConfiltered=new SqlConnection("server=xyz;Integrated Security=SSPI;database=mydatabase;");
connection.Open();
Response.Write("<b>Connection is successfully established</b>");
connection.Close();
}
catch(Exception ex)
{
Response.Write("<b>An exception occured while establishing connection</b><br>"+" "+ex.Message.ToString());
}
}
</Script
The same above code is also present in the attachement file data.aspx ,please guide me where I am making mistake and please tell me that howcan I correct this mistake so that the code executes correctly.Alsoplease tell that what things should be included in the connectionstring and also please check the connection string of this code.

Connection string is fine if you intend to use Windows Authentication against SQl Server. User xyz/Administrator being in the logins has nothing to do with the error since you probably use that when connecting to SQL Server while working with Enterprise Manager interactively (e.g you use EM)

You have specified in connection string (Integrated Security=SSPI) that the Windows Identity ASP.NET runs under will also be used to log in to the given SQL Server instance. Your ASP.NEt application runs under "'xyz\ASPNET" identity which the error message

An exception occured while establishing connection
Login failed for user 'xyz\ASPNET'.

tries to indicate. Therefore this user should have its own login created to the SQL Server and again granted access to themydatabasedatabase given in the connection string.

What you need to do is to give this user the login and adequate permissions to the SQL Server instance by using SQL Server's management tools (Enterprise Manager)

Help regarding storing and retrieval of files in sql server

Hi,
Thanks in advance.

I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#.
Secondly,Is it possible to search file in BLOB using SQL server Full text search service.TryKB 309158 -- How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET.

And yes, SQL Server 2000 full text search offers BLOB filtering.

Terri