Drill down slow: asp

Showing posts with label asp. Show all posts

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

I have a SQL 2000 Standard server setup and I have a table in one of my
databases to store files in. I am using asp code to retrieve the files from
the database. I can download txt, bmp files just fine but when it comes to
pdf documents it just hangs at the downloading the file screen and then gives
me a timeout error after awhile. It is not a problem with the file sizes. I
am testing it with a 20 and 60k pdf files and one of the txt files I used to
test with is a few mb. I am thinking it's either an IIS server config issue
or SQL Server config problem. The IIS server is for our intranet and is not
locked down. Both IIS and SQL are running on the same box which is a Windows
2003 Enterprise server. Any help would be great.
Thanks,
Brett Bishop
MCSE, MCP+I
US Bankruptcy Court"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file sizes.
I
> am testing it with a 20 and 60k pdf files and one of the txt files I used
to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
Can you access PDF files via URL from the file system?|||fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files from
> the database. I can download txt, bmp files just fine but when it comes to
> pdf documents it just hangs at the downloading the file screen and then gives
> me a timeout error after awhile. It is not a problem with the file sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I used to
> test with is a few mb. I am thinking it's either an IIS server config issue
> or SQL Server config problem. The IIS server is for our intranet and is not
> locked down. Both IIS and SQL are running on the same box which is a Windows
> 2003 Enterprise server. Any help would be great.
Perhaps it has to do with the content type when the file is being sent
down. You may also want to set the content-disposition to indicate to
download the file, "Content-Disposition: Attachment" in the response header.
You aren't saying if you are trying to open up the file directly or save
it to disk first.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Hi,
This is what i would do.
Try to download a PDF file from the file system (this rules out a problem
with IIS serving PDF-files).
Use WFetch[1] to request the PDF page (from the db), and see if IIS gives
a complete reply back (this will rule out a client-side problem).
Usually these kind of problems are caused by the programmers code. SQL
Server only stores the data, and IIS only "gives" the client the data.
Your code however do everything in between, and that is where it usually
goes wrong.
--
Regards,
Kristofer Gafvert
www.gafvert.info - My Articles and help
www.ilopia.com
fibreiv wrote:
> I have a SQL 2000 Standard server setup and I have a table in one of my
> databases to store files in. I am using asp code to retrieve the files
from
> the database. I can download txt, bmp files just fine but when it comes
to
> pdf documents it just hangs at the downloading the file screen and then
gives
> me a timeout error after awhile. It is not a problem with the file
sizes. I
> am testing it with a 20 and 60k pdf files and one of the txt files I
used to
> test with is a few mb. I am thinking it's either an IIS server config
issue
> or SQL Server config problem. The IIS server is for our intranet and is
not
> locked down. Both IIS and SQL are running on the same box which is a
Windows
> 2003 Enterprise server. Any help would be great.
> Thanks,
> Brett Bishop
> MCSE, MCP+I
> US Bankruptcy Court|||I have the Content type set to application/pdf and I can't open it or save
it. Just to clarify the data is stored in SQL Image field.
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files from
> > the database. I can download txt, bmp files just fine but when it comes to
> > pdf documents it just hangs at the downloading the file screen and then gives
> > me a timeout error after awhile. It is not a problem with the file sizes. I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used to
> > test with is a few mb. I am thinking it's either an IIS server config issue
> > or SQL Server config problem. The IIS server is for our intranet and is not
> > locked down. Both IIS and SQL are running on the same box which is a Windows
> > 2003 Enterprise server. Any help would be great.
> Perhaps it has to do with the content type when the file is being sent
> down. You may also want to set the content-disposition to indicate to
> download the file, "Content-Disposition: Attachment" in the response header.
> You aren't saying if you are trying to open up the file directly or save
> it to disk first.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||The file is stored in the Database in an Image field so there is no file
system access to the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:0880684A-A141-4D78-842A-041C501DBCF5@.microsoft.com...
> > I have a SQL 2000 Standard server setup and I have a table in one of my
> > databases to store files in. I am using asp code to retrieve the files
> from
> > the database. I can download txt, bmp files just fine but when it comes
> to
> > pdf documents it just hangs at the downloading the file screen and then
> gives
> > me a timeout error after awhile. It is not a problem with the file sizes.
> I
> > am testing it with a 20 and 60k pdf files and one of the txt files I used
> to
> > test with is a few mb. I am thinking it's either an IIS server config
> issue
> > or SQL Server config problem. The IIS server is for our intranet and is
> not
> > locked down. Both IIS and SQL are running on the same box which is a
> Windows
> > 2003 Enterprise server. Any help would be great.
> Can you access PDF files via URL from the file system?
>
>|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> The file is stored in the Database in an Image field so there is no file
> system access to the file.
Understood.
As part of the troubleshooting routine, I'm asking if you can access one
from the file system.|||I copied the same file thats in the database to the folder I have all the asp
files in on my server and put the url right to the pdf document and it opened
up acrobat and opened the file.
"Tom Kaminski [MVP]" wrote:
> "fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
> news:9BEC1745-770E-47BF-8749-06E8EBB48601@.microsoft.com...
> > The file is stored in the Database in an Image field so there is no file
> > system access to the file.
> Understood.
> As part of the troubleshooting routine, I'm asking if you can access one
> from the file system.
>
>|||fibreiv wrote:
> I have the Content type set to application/pdf and I can't open it or save
> it. Just to clarify the data is stored in SQL Image field.
> "Aaron Weiker" wrote:
>>Perhaps it has to do with the content type when the file is being sent
>>down. You may also want to set the content-disposition to indicate to
>>download the file, "Content-Disposition: Attachment" in the response header.
Are you setting "Content-Disposition" at all in the header?
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||"fibreiv" <fibreiv@.discussions.microsoft.com> wrote in message
news:AED1E8B2-C2EE-4E02-9A38-BA527F0C6B2B@.microsoft.com...
> I copied the same file thats in the database to the folder I have all the
asp
> files in on my server and put the url right to the pdf document and it
opened
> up acrobat and opened the file.
OK - at this point, we'll need to see code to get an idea of how you're
doing it. In that case, I would suggest adding the ASP newsgroup as well
...|||Here is what I am doing to test it. I have hardcoded the file I want to get
out of the database.
Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
Set db = Server.CreateObject("ADODB.Connection")
db.Open Connect
SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
id = 2"
Set rs = db.Execute( SQL )
If rs.EOF Then Response.End
Response.ContentType = "application/pdf"
Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
Response.AddHeader "Content-Length", "16863"
Response.BinaryWrite rs("filedata")
"Aaron Weiker" wrote:
> fibreiv wrote:
> > I have the Content type set to application/pdf and I can't open it or save
> > it. Just to clarify the data is stored in SQL Image field.
> >
> > "Aaron Weiker" wrote:
> >>Perhaps it has to do with the content type when the file is being sent
> >>down. You may also want to set the content-disposition to indicate to
> >>download the file, "Content-Disposition: Attachment" in the response header.
> Are you setting "Content-Disposition" at all in the header?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
>|||fibreiv wrote:
> Connect = "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=INNB;Data Source=pompom"
> Set db = Server.CreateObject("ADODB.Connection")
> db.Open Connect
> SQL = "SELECT filesize, contenttype, filename, filedata FROM thefiles where
> id = 2"
> Set rs = db.Execute( SQL )
> If rs.EOF Then Response.End
> Response.ContentType = "application/pdf"
> Response.AddHeader "Content-Disposition", "attachment;filename=agencies.pdf"
> Response.AddHeader "Content-Length", "16863"
> Response.BinaryWrite rs("filedata")
Have you tried putting Response.End at the end? Someone else may be able
to help identify a missing piece as it's been a long time since I did this.
--
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Sunday, March 25, 2012

Download the SQL servere date to excel file using ASP.net

Hi EveryOne

I would like to download the SQL server databse to excel file using ASP.Net

I need to download the one table to excel file. If anyone knows please help me.

With Regards

Nibu Abraham

i found this link

http://www.theserverside.net/discussions/thread.tss?thread_id=29385

give a try

|||

Hi Nibu

Create a stored procedure to display the required columns from the give table. Grant execute permission to this stored procedure to the role that your ASP.NET account is assigned to. In your datalayer, run the stored procedure to output a dataset. Add a gridview control (or datagrid if still at ASP.NET 1.1) to an ASPX form.

Set the datasource for the control to the dataset produced by the read function in your data layer, call databind and the data will be displayed.

There is a simple modifier to make the displayed page OLE load Excel - I will find out the how of this at the weekend.

HTH

|||

You need to add the following to your Page_Load

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

You will need to turn off the sort option on the grid and any other hyperlinks on the form.

HTH

sql

Download the file stored in sqlserver table as image datatype using asp.net 2.0

I am using Asp.net 2.0 with C# and sql server 2000. I need to download the file which is stored in sql server database table as image datatype. So I need to download from cs page.

Pls reply,

Arun.

Here's a good article on storing and retrieving binary data with asp.net 2.0
http://www.beansoftware.com/ASP.NET-Tutorials/Binary-Files-To-Database.aspx

|||

Hi,

Consider, you have query like

select files,filename,filesize,fileext from filesintable where id=1

that file on dar[0] as image datatype.

while (dar.Read())
{
bFile = (byte[])dar[0];
Response.AddHeader("Content-Disposition", "attachment; filename=" +dar[1].ToString());

switch (dar[3].ToString())
{
case "ask":
Response.ContentType = "video/x-ms-asf";
break;
case "avi":
Response.ContentType = "video/avi";
break;
case "doc":
Response.ContentType = "application/msword";
break;
case "zip":
Response.ContentType = "application/zip";
break;
case "xls":
Response.ContentType = "application/vnd.ms-excel";
break;
case "ppt":
Response.ContentType = "application/vnd.ms-powerpoint";
break;
case "gif":
Response.ContentType = "image/gif";
break;
case "jpg":
case "jpeg":
Response.ContentType = "image/jpeg";
break;
case "wav":
Response.ContentType = "audio/wav";
break;
case "mp3":
Response.ContentType = "audio/mpeg3";
break;
case "mpg":
case "mpeg":
Response.ContentType = "video/mpeg";
break;
case "rtf":
Response.ContentType = "application/rtf";
break;
case "htm":
case "html":
Response.ContentType = "text/html";
break;
case "asp":
Response.ContentType = "text/asp";
break;
case "pdf":
Response.ContentType = "application/pdf";
break;
default:
Response.ContentType = "application/octet-stream";
break;
}
Response.BinaryWrite(bFile);

}

It works fine.

Sunday, March 11, 2012

double-hop authentication for SSRS and ASP

I'm not an expert - but:
Yes - this is the classic double-hop scenario. I live with it everyday.
A lot depends on the Windows versions of the servers:
Windows 2000 - this requires Kerberos or the use of network service
accounts used between servers (or if the back-end service is SQL - SQL
logins).
Windows 2003 - you can use the Protocol Transition features to delegate
credentials without Kerberos.
See:
http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx
Difficult to help without more details, but if your using Windows 2000
servers and NTLM only, you will need to create a service account for
the Front End server to communicate with the Back End.
In a typical Web Server (A) talks to Application Server (B) that talks
to SQL Server (C) scenario...
1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
impersonate <user>.
2) App Server B serves use NTLM authentication to grant access to
<user>, but is not allowed to pass these credentials to C. So either:
a) the DCOM or .NET services on B are configured to run under a
service account <_service1> which has access rights on the SQL Server
(C) and necessary Databases objects
or
b) the services on B use SQL authentication to login and access (C).
Note: If you use this approach, and you need to audit for security; you
should log accesses to C on server B - so that you know who really was
accessing C (All C sees is the same user, all the time).
If your using Windows Server 2003, you can use delagation as explained
in the links I provided earlier.
punjab_tom@.hotmail.com wrote:
> Sleepy
> can you please tell more? my english is poor
> I have had security problems with Services of Reporting; I can see
> 'user is null' even though we are using NT Authentication.
> Tom
>
|||Hi,
I have a very similar problem. I've been through a lot of articles, but
something is still missing.
This is my setup:
All servers are Windows Server 2003.
IE6 connects to IIS using basic authentication. In ASP.NET code, using Win32
LogonUser, I impersonate a domain user and then make a call to the database
server. For some reason the impersonated user is not delegated to SQL server.
This is how I configured everything so far:
Created a domain account for the aspnet worker process (edited
machine.config on IIS server and edited <processmodel>:
<processModel enable="true" userName="domain\ASPNETuser" password="xxx"/>
The ASPNETuser is set to be Trusted for Delegation on the domain controller.
The IIS server is set to be Trusted for Delegation on the domain controller.
The users that are to be impersonated are NOT set as sensitive on the domain
controller.
The connection string to SQL looks like:"server=sqlserver;initial
catalog=dbname;integrated security=SSPI".
When connecting to the database, I get "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'".
What am I missing? How do I know the Kerberos is working.
"SleepyLab" wrote:

> Difficult to help without more details, but if your using Windows 2000
> servers and NTLM only, you will need to create a service account for
> the Front End server to communicate with the Back End.
> In a typical Web Server (A) talks to Application Server (B) that talks
> to SQL Server (C) scenario...
> 1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
> impersonate <user>.
> 2) App Server B serves use NTLM authentication to grant access to
> <user>, but is not allowed to pass these credentials to C. So either:
> a) the DCOM or .NET services on B are configured to run under a
> service account <_service1> which has access rights on the SQL Server
> (C) and necessary Databases objects
> or
> b) the services on B use SQL authentication to login and access (C).
> Note: If you use this approach, and you need to audit for security; you
> should log accesses to C on server B - so that you know who really was
> accessing C (All C sees is the same user, all the time).
> If your using Windows Server 2003, you can use delagation as explained
> in the links I provided earlier.
>
> punjab_tom@.hotmail.com wrote:
>

double-hop authentication for SSRS and ASP

hey
I work in a SSRS environment; I also do some web dev.. and we keep on
having authentication problems getting a token to pass from one user to
the webserver; and then to the database server.
this is called 'double-hop' authentication.. right?
I'm of the understanding that I need to check 'trust for delegation'
and run SetSpn on the ServiceAccount for my database server for it to
be the recipient of a double-hop token.
Isn't that correct?
Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
most of these; these guys put the SSRS Server on the same machine as
the database server.
Then they go to copy the report from the localserver to another; and
they bitch about how it doesn't have permissions.. it shows up and says
'user Null' or whatever-- it just sounds to me like a simple double-hop
problem.
I've tried to explain this to a half dozen clients over the years--
about the web authentication thing.. .because everywhere I go; people
use SQL authentication or they put the DB Server on the same machine as
the webserver.. or they make you launch the SSAS processing job from
the local machine.. instead of being able to connect from my desktop to
a SSAS machine to a 3rd machine- the db server.
It just seems to me like 99% of the companies out there dont know how
to do double-hop authentication.
And I just swear to god... everyone i tell this to theyre like 'well
were not a kerberos shop so we dont need to do that'
Does anyone have any REAL CLEAR guidelines for when we should use the
double-hop 'trust for delegation' and 'setspn' concept?
a) we DONT have kerberos
b) we have a half dozen servers and these guys put SSRS on the main db
server
(I assume it's because they dont speak double-hop)
c) at one of my last jobs; we had to terminal into the local db server
to do anything because we didn't have double-hop.. seemed like we were
throwing the baby out with the bathwater; giving people perms to the
terminal services on the db server; it just seems like overkill
d) I want to be able to go from DesktopA and hit ServerA and from
ServerA use a linked server to ServerB. That spells 'i need
double-hop' right?
thanks guys
-Susieyes I must do this also
my friend said that this was possible
susiedba@.hotmail.com wrote:
> hey
> I work in a SSRS environment; I also do some web dev.. and we keep on
> having authentication problems getting a token to pass from one user to
> the webserver; and then to the database server.
> this is called 'double-hop' authentication.. right?
> I'm of the understanding that I need to check 'trust for delegation'
> and run SetSpn on the ServiceAccount for my database server for it to
> be the recipient of a double-hop token.
> Isn't that correct?
> Ok.. now let's fast forward.. We got a bunch of SSRS Servers; and for
> most of these; these guys put the SSRS Server on the same machine as
> the database server.
> Then they go to copy the report from the localserver to another; and
> they bitch about how it doesn't have permissions.. it shows up and says
> 'user Null' or whatever-- it just sounds to me like a simple double-hop
> problem.
> I've tried to explain this to a half dozen clients over the years--
> about the web authentication thing.. .because everywhere I go; people
> use SQL authentication or they put the DB Server on the same machine as
> the webserver.. or they make you launch the SSAS processing job from
> the local machine.. instead of being able to connect from my desktop to
> a SSAS machine to a 3rd machine- the db server.
> It just seems to me like 99% of the companies out there dont know how
> to do double-hop authentication.
>
> And I just swear to god... everyone i tell this to theyre like 'well
> were not a kerberos shop so we dont need to do that'
> Does anyone have any REAL CLEAR guidelines for when we should use the
> double-hop 'trust for delegation' and 'setspn' concept?
> a) we DONT have kerberos
> b) we have a half dozen servers and these guys put SSRS on the main db
> server
> (I assume it's because they dont speak double-hop)
> c) at one of my last jobs; we had to terminal into the local db server
> to do anything because we didn't have double-hop.. seemed like we were
> throwing the baby out with the bathwater; giving people perms to the
> terminal services on the db server; it just seems like overkill
> d) I want to be able to go from DesktopA and hit ServerA and from
> ServerA use a linked server to ServerB. That spells 'i need
> double-hop' right?
> thanks guys
> -Susie|||I'm not an expert - but:
Yes - this is the classic double-hop scenario. I live with it everyday.
A lot depends on the Windows versions of the servers:
Windows 2000 - this requires Kerberos or the use of network service
accounts used between servers (or if the back-end service is SQL - SQL
logins).
Windows 2003 - you can use the Protocol Transition features to delegate
credentials without Kerberos.
See:
http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Sleepy
can you please tell more? my english is poor
I have had security problems with Services of Reporting; I can see
'user is null' even though we are using NT Authentication.
Tom
SleepyLab wrote:
> I'm not an expert - but:
> Yes - this is the classic double-hop scenario. I live with it everyday.
> A lot depends on the Windows versions of the servers:
> Windows 2000 - this requires Kerberos or the use of network service
> accounts used between servers (or if the back-end service is SQL - SQL
> logins).
> Windows 2003 - you can use the Protocol Transition features to delegate
> credentials without Kerberos.
> See:
> http://technet2.microsoft.com/WindowsServer/en/library/dac7ecea-7c82-43c0-847b-3a1a81454cfe1033.mspx|||Difficult to help without more details, but if your using Windows 2000
servers and NTLM only, you will need to create a service account for
the Front End server to communicate with the Back End.
In a typical Web Server (A) talks to Application Server (B) that talks
to SQL Server (C) scenario...
1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
impersonate <user>.
2) App Server B serves use NTLM authentication to grant access to
<user>, but is not allowed to pass these credentials to C. So either:
a) the DCOM or .NET services on B are configured to run under a
service account <_service1> which has access rights on the SQL Server
(C) and necessary Databases objects
or
b) the services on B use SQL authentication to login and access (C).
Note: If you use this approach, and you need to audit for security; you
should log accesses to C on server B - so that you know who really was
accessing C (All C sees is the same user, all the time).
If your using Windows Server 2003, you can use delagation as explained
in the links I provided earlier.
punjab_tom@.hotmail.com wrote:
> Sleepy
> can you please tell more? my english is poor
> I have had security problems with Services of Reporting; I can see
> 'user is null' even though we are using NT Authentication.
> Tom
>|||Which account is the Application Pool your Report Server virtual directories
running under?
If that runs as network service, you might want to try running it as the
domain user you've set up.
Or try using Integrated Security instead of basic authentication.
Kaisa M. Lindahl Lervik
"dawidg" <dawidg@.discussions.microsoft.com> wrote in message
news:8AB36908-AB90-4F46-B185-5F2E21A85E9D@.microsoft.com...
> Hi,
> I have a very similar problem. I've been through a lot of articles, but
> something is still missing.
> This is my setup:
> All servers are Windows Server 2003.
> IE6 connects to IIS using basic authentication. In ASP.NET code, using
> Win32
> LogonUser, I impersonate a domain user and then make a call to the
> database
> server. For some reason the impersonated user is not delegated to SQL
> server.
> This is how I configured everything so far:
> Created a domain account for the aspnet worker process (edited
> machine.config on IIS server and edited <processmodel>:
> <processModel enable="true" userName="domain\ASPNETuser" password="xxx"/>
> The ASPNETuser is set to be Trusted for Delegation on the domain
> controller.
> The IIS server is set to be Trusted for Delegation on the domain
> controller.
> The users that are to be impersonated are NOT set as sensitive on the
> domain
> controller.
> The connection string to SQL looks like:"server=sqlserver;initial
> catalog=dbname;integrated security=SSPI".
> When connecting to the database, I get "Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'".
> What am I missing? How do I know the Kerberos is working.
>
> "SleepyLab" wrote:
>> Difficult to help without more details, but if your using Windows 2000
>> servers and NTLM only, you will need to create a service account for
>> the Front End server to communicate with the Back End.
>> In a typical Web Server (A) talks to Application Server (B) that talks
>> to SQL Server (C) scenario...
>> 1) Web Site (A) uses NTLM authentication and ASP(.NET) code to
>> impersonate <user>.
>> 2) App Server B serves use NTLM authentication to grant access to
>> <user>, but is not allowed to pass these credentials to C. So either:
>> a) the DCOM or .NET services on B are configured to run under a
>> service account <_service1> which has access rights on the SQL Server
>> (C) and necessary Databases objects
>> or
>> b) the services on B use SQL authentication to login and access (C).
>> Note: If you use this approach, and you need to audit for security; you
>> should log accesses to C on server B - so that you know who really was
>> accessing C (All C sees is the same user, all the time).
>> If your using Windows Server 2003, you can use delagation as explained
>> in the links I provided earlier.
>>
>> punjab_tom@.hotmail.com wrote:
>> > Sleepy
>> >
>> > can you please tell more? my english is poor
>> > I have had security problems with Services of Reporting; I can see
>> > 'user is null' even though we are using NT Authentication.
>> >
>> > Tom
>> >
>>

Double Quote in database record

In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this:

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @.ArticleHeading) ORDER BY [Articles].[ArticleHeading]"

You need to do the replace on both the column AND the value you are comparing, otherwise they will never be equal.
eg.

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel]<> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like REPLACE(@.ArticleHeading),'chr(34)') ORDER BY [Articles].[ArticleHeading]"|||

I tried it but still it does not produce the right results.

My code:

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like (REPLACE(@.ArticleHeading, 'chr(34)', ''))) ORDER BY [Articles].[ArticleHeading]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_articleHeading As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_articleHeading.ParameterName = "@.ArticleHeading"
dbParam_articleHeading.Value = Request.QueryString("Alphabet")& "%"
dbParam_articleHeading.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_articleHeading)

|||What are you trying to do with: REPLACE([Articles].[ArticleHeading], 'chr(34)', '') ?|||Some records starts with a double qoute and a need to list all for example that starts with A but that needs to include something like "A nice day"|||

Using a sql query I manage to get the right results but what do I need to do in asp.net to get the right results. My SQL code that replaces the ":

SELECT *
FROM Articles
WHERE (REPLACE(ArticleHeading, '"', '') LIKE 'A%')
ORDER BY ArticleHeading

|||

Change:

REPLACE([Articles].[ArticleHeading], 'chr(34)', '')

To:

REPLACE([Articles].[ArticleHeading], chr(34), '')

Tuesday, February 14, 2012

does using sql parameters speed asp.net code up?

does using sql parameters speed asp.net code up?Depends on a lot of things. It's usually more secure than straight SQL code, which is what I'm assuming you're comparing this to. Also, if you are using stored procedures they are a bit speedier on the server side (but I don't know if you'd notice it, depends on the app).

But from a security perspective (to avoid SQL injection attacks), it's a good idea. IMHO|||I have a question about paramaters then.. I have only seen sample for INSERT how about UPDATE AND DELETE?

Here is my insert statement

SQL = "INSERT INTO table1(id, key_data) VALUES (@.ID, @.key)"

conn = New SQLConnection(SQLserver)
dbComm2 = New SqlCommand(SQL,conn)
dbComm2.Parameters.Add(New SqlParameter("@.ID", Session("id")))
dbComm2.Parameters.Add(New SqlParameter("@.key", KeyGeN))

How would i change these for delete and Update?

thanks|||xactly the same...just change your query


update table set col=@.var1, col2=@.var2 where col3=@.var3

then add the variables and their values like you did above

same for delete also

hth

Does this Stored Procedure Syntax look okay?

I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:

set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here
objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name
objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'Add Input Parameters to date and from date variables - dtFrom and dtTo
objComm.Parameters.Append.CreateParameter("@.dtFrom", adDate, adParamInput, 9)
objComm.Parameters.Append.CreateParameter("@.dtTo", adDate, adParamInput, 9)

CREATE PROCEDURE editorTAmonthlyReport
@.FromDate DateTime,
@.ToDate DateTime
As

Select
U.UserID,
U.Title,
U.FirstName,
U.Surname,
TAU.UserPosition,
U.Email,
A.AgencyName,
A.AddressLine1,
A.AddressLine2,
A.Town,
A.County,
A.Postcode,
C.Country,
Coalesce(U.Phone,A.Phone) As Phone,
TAU.DateOfCreation
From
[user] U LEFT OUTER JOIN MyTrafalgarUser MTU
ON MTU.userID=U.UserID
INNER JOIN TravelAgencyUser TAU
ON U.UserID=TAU.UserID
INNER JOIN Agency A
ON TAU.AgencyID=A.AgencyID
INNER JOIN Country C
ON A.CountryID=C.CountryID
Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @.FromDate) < 0 )
and datediff(d,TAU.DateOfCreation, @.ToDate) > 0
ORDER BY TAU.DateOfCreation
GO

Your stored procedure looks good to me .

You can test you stored procedure in SQL Analyser to see if it returns correct records.

|||

It's okay, I got it working.

Thanks.

Tuesday, March 27, 2012

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Downloading File located in SQL Server with ASP

Sunday, March 25, 2012

Download the SQL servere date to excel file using ASP.net

Download the file stored in sqlserver table as image datatype using asp.net 2.0

Sunday, March 11, 2012

double-hop authentication for SSRS and ASP

double-hop authentication for SSRS and ASP

Double Quote in database record

Tuesday, February 14, 2012

does using sql parameters speed asp.net code up?

Does this Stored Procedure Syntax look okay?

Drill down slow

Blog Archive

About Me