attach db to .net2 project

Hi,

How do I attach an existing sql2000 database to a .net 2 project in the app_data folder?

thanks

Just detach the database from the SQL 2000 instance, then open your project-> right click the App_Data folder in Solution Explorer->choose Add Existing Items->find the database file and add it.sql

at least one item among all

I do not know how to project the customers who bought at least one of each of the color tv, white/black, mono. It is like finding which customer bought 1 tv from each type available at the same time. in the TELEVISION table the types are: color, white/black, mono.

As an example someone buys 5 tv among them, he has 2 color tv, 1 white/black, 2 mono however he did purchase one of each type of tv.

I need to project this output:

CustID Customer Name State

-- --

101 Jim White Ohio

234 Karl Smith Pennsylvania

Here is the tables structure

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

EmpIDFK size Quantity

TotalPrice Weight

CustomerIDFK SerialNumberPK LineNbr

CUSTOMER Table

CustomerIDPK

FName

LName

Address

City

State

Hi Mermaid

The example below will return the data you requested.

Chris

DECLARE @.INVOICE TABLE (InvoiceNbrPK INT, CustomerIDFK INT)
DECLARE @.TELEVISION TABLE (Type VARCHAR(13), SerialNumberPK INT)
DECLARE @.INV_LINE_ITEM TABLE (InvoiceNbrFK INT, SerialNumberFK INT, Quantity INT)
DECLARE @.CUSTOMER TABLE (CustomerIDPK INT, FName VARCHAR(20), LName VARCHAR(20))

INSERT INTO @.TELEVISION(Type, SerialNumberPK)
SELECT 'Mono', 1 UNION
SELECT 'Colour', 2 UNION
SELECT 'Black & White', 3

INSERT INTO @.INVOICE(InvoiceNbrPK, CustomerIDFK)
SELECT 1, 1 UNION
SELECT 2, 2 UNION
SELECT 3, 3 UNION
SELECT 4, 4

INSERT INTO @.CUSTOMER(CustomerIDPK, FName, LName)
SELECT 1, 'Kent', 'Waldrop' UNION
SELECT 2, 'Arnie', 'Rowland' UNION
SELECT 3, 'Louis', 'Davidson' UNION
SELECT 4, 'Umachandar', 'Jayachandran'

--Set up the data so that Kent and Umachander have both bought one of each TV
INSERT INTO @.INV_LINE_ITEM(InvoiceNbrFK, SerialNumberFK, Quantity)
SELECT 1, 1, 3 UNION
SELECT 1, 2, 2 UNION
SELECT 1, 3, 1 UNION
SELECT 2, 2, 5 UNION
SELECT 2, 3, 2 UNION
SELECT 3, 2, 3 UNION
SELECT 4, 1, 1 UNION
SELECT 4, 2, 1 UNION
SELECT 4, 3, 1

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
INNER JOIN @.INVOICE i ON i.CustomerIDFK = c.CustomerIDPK
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
GROUP BY c.CustomerIDPK,
c.FName + ' ' + c.LName
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2)

|||

Maybe something like this?

declare @.customer table
( custId integer,
FName varchar(20),
LName varchar(20),
State varchar(30)
)
insert into @.customer values (101, 'Jim', 'White', 'Ohio')
insert into @.customer values (102, 'Donna', 'White', 'Illinois')
insert into @.customer values (234, 'Karl', 'Smith', 'Pennsylvania')
insert into @.customer values (235, 'Karl', 'Benson', 'Guam')
--select * from @.customer

declare @.invoice table
( invoiceNbr varchar(15),
custId integer
)
insert into @.invoice values (1, 101)
insert into @.invoice values (2, 102)
insert into @.invoice values (3, 234)
insert into @.invoice values (4, 234)
insert into @.invoice values (5, 234)
--select * from @.invoice

declare @.television table
( serialNumber varchar(25),
type varchar(12)
)
insert into @.television values ('Dlx-0001', 'Color')
insert into @.television values ('Dlx-0002', 'Color')
insert into @.television values ('Dlx-0003', 'Color')
insert into @.television values ('Clr-0001', 'Color')

insert into @.television values ('BW-00001', 'white/black')
insert into @.television values ('BW-00002', 'white/black')

insert into @.television values ('Mon-0001', 'Mono')
insert into @.television values ('Mon-0002', 'Mono')
insert into @.television values ('Mon-0003', 'Mono')
--select * from @.television

declare @.inv_line_item table
( invoiceNbr varchar(15),
serialNumber varchar(25)
)
insert into @.inv_line_item values (1, 'Dlx-0001')
insert into @.inv_line_item values (1, 'BW-00001')
insert into @.inv_line_item values (1, 'Mon-0001')

insert into @.inv_line_item values (2, 'Dlx-0003')
insert into @.inv_line_item values (2, 'Mon-0003')

insert into @.inv_line_item values (3, 'Dlx-0002')
insert into @.inv_line_item values (4, 'BW-00002')
insert into @.inv_line_item values (5, 'Mon-0002')
--select * from @.inv_line_item

select custId,
[Customer Name],
state
from ( select c.custId,
c.fName + ' ' + c.LName as [Customer Name],
state,
count(distinct t.type) as typeCount
from @.customer c
inner join @.invoice i
on c.custId = i.custId
inner join @.inv_line_item l
on i.invoiceNbr = l.invoiceNbr
inner join @.television t
on l.serialNumber = t.serialNumber
and t.type in ('Color', 'Mono', 'White/Black')
group by c.custId,
c.state,
c.lName,
c.fName
having count(distinct t.type) = 3
) x

-- custId Customer Name state
--
-- 101 Jim White Ohio
-- 234 Karl Smith Pennsylvania

I like Chirs' solution better; I thought about doing it like that but I didn't have the HAVING condition worked out like that. Nice!

|||

Hi Chris,

I will try your solution and see what will be the output.

Thank you very much.

|||

Hi Chris,

Your example did return what I was expecting as projection. Thank you so much for the hint, you made my day.

Thanks again.

|||

Hi Kent,

I prefer Chris solution because you set up the count to be equal to 3. I tried your example and got something else because of the count. I really appreciate your input and thank you for helping me out. I can breath now, .

|||

Hi Mermaid
That's interesting. Do you have more than three distinct types in the Television table? You did specify only three in your initial post. Other than the hard-coding, both Kent's and my solutions are virtually identical.
I must admit that when I put my version together I was in two minds over whether or not to hard-code the number of distinct types into the HAVING clause.
Chris

|||

Here is another simpler and slightly better performing way to answer the question. I used the schema that Chris posted. The gist of the solution is that you get customers that do not have any television sets that are not accounted for in their invoices. This basically checks the inverse condition. This query will perform orders of magnitude faster than the GROUP BY approach due to lesser scans, no aggregation and simpler joins.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE NOT EXISTS(

SELECT *

FROM @.TELEVISION AS t
WHERE NOT EXISTS(
SELECT *
FROM @.INVOICE AS i
JOIN @.INV_LINE_ITEM AS ilt
ON i.CustomerIDFK = c.CustomerIDPK
WHERE ilt.InvoiceNbrFK = i.InvoiceNbrPK
and t.SerialNumberPK = ilt.SerialNumberFK
and ilt.Quantity > 0))

And you can actually simplify the GROUP BY query by doing below.

SELECT c.CustomerIDPK AS [CustID],
c.FName + ' ' + c.LName AS [Customer Name]
FROM @.CUSTOMER c
WHERE EXISTS(
SELECT 1
FROM @.INVOICE i
INNER JOIN @.INV_LINE_ITEM ilt ON ilt.InvoiceNbrFK = i.InvoiceNbrPK
INNER JOIN @.TELEVISION t ON t.SerialNumberPK = ilt.SerialNumberFK
WHERE ilt.Quantity > 0
And i.CustomerIDFK = c.CustomerIDPK
HAVING COUNT(DISTINCT t.Type) = (SELECT COUNT(DISTINCT t2.Type) FROM @.Television t2))

|||

Hi,

I try your first example the projection did not return anything. I think that the reverse is not working but your second example works the same way as Chris's. I noted both queries and thank you for your input. I do appreciate your help.

|||Could you please post the data / schema for which the first query doesn't work? That logic looks fine to me.|||

HI umachandar,

I worked on your example last night and the result is correct. Thanks for helping me out.

Assistance on implementing Data Mining

Hi,

I'm new to SQL Server and data mining, so please forgive my ignorance...

I'm working on a project which requires me to use the datamining provided by SQL Server 2005. I've a table for which i want to predict the values in a table (Encyclopedia)

The table contains the following fields:

Component

Major Attribute

Minor Attributes(which is basically a list of CSV for attributes in no particular order)

I want to predict the component if i enter the attributes ..... my questions:

1. Should i change the table structure in any way to assist in data mining?

2. What model would be preferrable?

3. If i'm using the model will it extend to the data added to the table automatically or do i have to update it regularily.

I need to submit the project by 20th... and i'm not even started. I tried a lot on my own..... but couldn't get anywhere without definitive assistance from anyone.

Please help

Thanks and Regards,

Sundeep Singh

You should probably use a nested table which contains multiple rows for each of the Minor Attributes associated with a Component - so your mining model might look something like this:

CREATE MINING MODEL CompPredict(
CaseID LONG KEY,
Component TEXT DISCRETE PREDICT,
MajorAttribute TEXT DISCRETE,
MinorAttributes TABLE(
MinorAttribute TEXT KEY
)
)
USING Microsoft_Decision_Trees

If you add data to the source database that you process your model from, you will need to reprocess the model either manually or using scheduled job or Integration Service package.

|||

Hi Raman

Thankx for ur answer..................

I converted the tables as you said

TableComponent(ComponentID, BodyPart,MajorAttribute,ComponentName)

TableMinor(ComponentID,MinorAttribute)

Used TableComponent as Case(ComponentID as Key), and TableMinor as Nested(MinorAttribute as key)

I tried predicting Component Name using BodyPart, MajorAttribute and Minor Attribute as Input.....

I tried it using Microsoft Decision trees and it generated only single node for it.........

What do you think can be the problem....................

Moreover i need to generate the association rules from the data but again it is generating no rules from the given data...........

Thanks

Sundeep Singh

|||How big is your data set? Have you tried tweaking the algorithm parameters (lower COMPLEXITY_PENALTY, MINIMUM_LEAF_CASES, MINIMUM_SUPPORT)?|||

Hi,

I tried to reduce the parameters and got some rules... but there is a problem. the association rules that are being generated use a single minorAtt at a time.

I mean that in the entire collection there is not one that uses two MinorAttributes to Predict the column.

And can you please help me with the query... assuming that i have a major symptom and some minor symptoms and i want to predict what Components they can be for along with the probability of correctness..

Thanks

Sundeep Singh

|||

Hi,

played with the algo params and it worked.. i don't know how as yet.. but i got some rules with more than one minor symptom.

But the problem with the query remains... somebody please help!!!!!!!! only four days left for submission....

Thanks

Sundeep Singh

|||

You can do a SELECT COUNT(*) FROM TableMinor and SELECT COUNT(DISTINCT ComponentID) FROM TableMinor. If these numbers are the same, than you have one minor/component, otherwise you do have multiple minors/component and everything's OK (except maybe your data).

If you don't have multiple minors/component, you don't need a nested table. From the initial description it seemed like there were.

|||

The number of minor are more than one, but the query that i asked for was to predict the component when i enter the minor and major attributes along with the probability of match...

Supposing that i have named my model as Encyclopedia.

Thanks

|||Try this to get the top 3 predictions for component:

SELECT FLATTENED
TopCount(PredictHistogram(Component), $AdjustedProbability, 3)
FROM [ComponentPredictModel]
NATURAL PREDICTION JOIN
(SELECT 'xxx' as MajorAttribute,
(SELECT ( SELECT 'x' AS Minor UNION
SELECT 'y' AS Minor )
AS [MinorAttributes])
) AS t

Assigning to multipule categories

Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it

When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.

If you need more clarification, post here and let me know.

Thanks in Advance,
Aaron Hawn (aaron@.ionzion.net)The table structure you provided is sufficient to answer that question. Can you clarify your issue?|||Operative word being think, I think you are asking how to represent multiple relationships using the table schema you've described. One row in the table represents one cross reference from a link to a category. To represent multiple category relationships for a single link, you add multiple rows to the Cross-reference table for that LinkID.

-PatP

Assigning data from an SQL query to a variable

Hello all,

for a project I am trying to implement PayPal processing for orders

public void CheckOut(Object s, EventArgs e)
{

String cartBusiness ="0413086@.chester.ac.uk";
String cartProduct;
int cartQuantity = 1;
Decimal cartCost;
int itemNumber = 1;

SqlConnection objConn3;
SqlCommand objCmd3;
SqlDataReader objRdr3;

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);
objConn3.Open();
objRdr3 = objCmd3.ExecuteReader();
cartProduct ="Cheese";
cartCost = 1;
objRdr3.Close();
objConn3.Close();

cartBusiness = Server.UrlEncode(cartBusiness);

String strPayPal ="https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness;

strPayPal += "&item_name_" + itemNumber + "=" + cartProduct;
strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity;
strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost);

Response.Redirect(strPayPal);

Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.

So I want cartProduct = Title and cartCost = Price from the SQL query.

How do I do this?

Thanks

DO NOT use string concatenation like you have now. Use Parameterized Queries.

you close the SQL connection not the reader first. Then loop through the reader, get the values and assign the variables. I would definetely recommend using Try/Catch block to catch any errors.

The following code is for illustration only.

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);

Try
If objConn3.State = 0 Then objConn3.Open()

objRdr3 = objCmd3.ExecuteReader();

If DataReader.HasRows Then
Do While objRdr3 .Read()
cartProduct = objRdr3 .Item("cartProduct"))
cartCost = objRdr3 .Item("cartCost"))
Loop

End If
Catch exc As Exception
Response.Write(exc)
Finally

objRdr3.close()
If objConn3.State = ConnectionState.Open Then
objConn3.Close()
End If
End Try

Assign session variable value to update parameter

Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.

code snippet:

<UpdateParameters><asp:ParameterName="hrs_credited"/>

<asp:ParameterName="updater_id"DefaultValue="<%$ Session("User_ID")%>"Type="Int32"/>

<asp:ParameterName="activity_id"/>

<asp:ParameterName="attendee_id"/>

</UpdateParameters>

The error message that I receive is:

Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:\Development\CME\dataentry\attendance.aspx 29

Does anyone have an idea how to assign the session var value to the parameter?

Thanks!

There is a special parameter called a SessionParameter that does exactly that. Refer to this page for more information:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter.aspx

ASPNETDB Connection Problems - Please Please Help!

Hi I am working on my final year project and after over coming one major problem with sql server I have encountered another.

It feels as if I have tried everthing to solve it but cant get my head around it. I really do not have much time left to finish my project and am very worried in case I cant get the app / db fixed in time.

The problem started when I was showing my friend the website over the internet. There was a space in the address / folder name so I decided to create a new folder and copy the contents of the code from the old folder to the new one. This included the app_data directory that holds the databases for my system.

After setting up a virtual directory in IIS v 5.1 i ran the website only to be confronted with not being able to log in.

I have three connections ..... aspnetdb.mdf, x.mdf, y.mdf.

x.mdf and y.mdf are both being read from ok when I run the app but it doesnt seem to be reading from the aspnetdb.mdf database.

I have tried all sorts of things and had all sorts of error messages including:

cant write to database as its read only

cant access database because its already there.

It seems to me that there is more than one aspnetdb.mdf file attached therefore the app is reading from the new one and not the one i want it to.

I will paste the web.config file below. Please note that before i copied and pasted code to a different location this web.config was working fine. Also note that when I hit ctrl+alt+del it shows up 3 sqlservr processes -

sqlServr.exe - Network Service
sqlServr.exe - Rodney (my log on name)
sqlServr.exe - ASPNET

<connectionStrings>

<add name="Classes" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Classes.mdf"

providerName="System.Data.SqlClient" />

<add name="Iftams" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Iftams.mdf"

providerName="System.Data.SqlClient" />

<remove name="UsersDB" />

<add name="UsersDB" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

I have tried reinstalling sqlserver, creating a new website from the vs2005 ide and also creating a virtual directory first and then copying files to it.

I have also tried attaching to management studio, changing user settings etc

I really dont know what to do next. My head is fried. Please help.

I would really appreciate your help on this one.

Regards in hope

Rodney

The ASP.net forum is a better place to ask questions about ASP.net and Visual Web Developer. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

aspnet_regsql

Hi,

It seems everytime I create a new project, I have to run aspnet_regsql to map the database to my application, otherwise, there'll be errors when trying to connect to SQL in my application. My question is, is this a norm? To be executing this procedure everytime I create a web application?

bump, anyone?

|||

Hi,

In your project, if you want to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, you have to use that tool to access that database in your SqlServer. My suggestion is once you use that tool, make a copy of the database which built by that tool as a sample database and stored in your SqlServer. In the furture if you want to get a new database for use by SqlServer providers in ASP.NET. Just create a new empty database and import the structure from the sample one.

Thanks.

ASP.Net, Microsoft SQL Server 2005 and XML

Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
oAs the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
oBut After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
oThe problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.
Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.

> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> oAs the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> oBut After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> oThe problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Also instead of trying to construct your XML using string concatenations,
why don't you use FOR XML?
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad747edd8c8b87741135600@.news.microsoft.com ...
> Hello djamilabouzid@.gmail.com,
> Its not all that clear as what is cause a problem here because we don't
> know what reade1.GetString(47) is actually returning.
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

ASP.Net, Microsoft SQL Server 2005 and XML

Hi!
I work in a web site project which uses ASP.net and Microsoft SQL
Server 2005. I use XML and XSL for my pages. My problem is:
o As the data in the database contain some characters that are not
allowed by XML structure, I used CDATA for all my fields in order to
store them in the XML file like that :
vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
reader1.GetString(47) + "]]></transcription_texte>"
o But After I realized that with CDATA I can't access to my XML
structures inside the field (knowing that some fields in the database
stored XML data). So I changed my field to normal writing like that:
vignettes_xml = vignettes_xml + "<transcription_texte>" +
reader1.GetString(47) + "]</transcription_texte>"
o The problem is : with both the first and the second writing I have in
my XML variable transcription_texte an empty data. But in my database
every transcription_texte contains a long XML structure. I did not
understand what the source of the problem is.
Your answers can be very helpful.
Regards,
Djamila.Hello djamilabouzid@.gmail.com,
Its not all that clear as what is cause a problem here because we don't know
what reade1.GetString(47) is actually returning.

> Hi!
> I work in a web site project which uses ASP.net and Microsoft SQL
> Server 2005. I use XML and XSL for my pages. My problem is:
> o As the data in the database contain some characters that are not
> allowed by XML structure, I used CDATA for all my fields in order to
> store them in the XML file like that :
> vignettes_xml = vignettes_xml + "<transcription_texte><![CDATA[" +
> reader1.GetString(47) + "]]></transcription_texte>"
> o But After I realized that with CDATA I can't access to my XML
> structures inside the field (knowing that some fields in the database
> stored XML data). So I changed my field to normal writing like that:
> vignettes_xml = vignettes_xml + "<transcription_texte>" +
> reader1.GetString(47) + "]</transcription_texte>"
> o The problem is : with both the first and the second writing I have
> in my XML variable transcription_texte an empty data. But in my
> database every transcription_texte contains a long XML structure. I
> did not understand what the source of the problem is.
> Your answers can be very helpful.
> Regards,
> Djamila.
>
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

ASP.NET ReportViewer Problems

Hi,
I am using the web version of the ReportViewer control in a Visual
Studio 2005 Beta 2 project. We have an Oracle database, and SQL
Reporting Services. I have been using the ReportViewer in local mode
therefore.
But I have a problem with printing/output of reports. At the moment
the ReportViewer will only export to Excel, which is the only way to
get a report to print.
Is there a way to get a report to output all pages of a report at once
using the viewer. Then users could simply print the web page to print
out the report without having to export to Excel.
Is there something I am doing wrong? Is there a way to export a report
programmatically somehow? (Remembering I am using local mode. Should I
really be using the SQL Reporting Services server? If so can I using
the SQL 2005 Express tool?).
Thanks for any help,
Regards,
RichardSorry, I meant to say we have an Oracle database and *no* SQL Reporting
services.
Thanks,
Richard|||Now some time has passed, it would be worth trying the release version as it
has lots of improvements over the beta 2 version.

Asp.net queries with sqlserver2000

Sir,i am running .net1.1 on my system with sqlserver as my database.I am devaloping an e-trading project in
ASP.NET with VB.net.My problem is that,I am not getting the correct syntax to query the database with
SELECT(with WHERE clause)in vb.net.
Please help me.Please show your code and give the EXACT details of the error, inclusing message.

ASP.Net on Window server 2000 - database access

Hi

I have problem in configuring my web project on Window server 2000. I have created the web application using Visual Studio 2005 tools. I have configured Window server 2000 with the .Net Framework Version 2.0. I can access the page which doesn't have database access.

The connection string in the web config file is giving me the problem that connection string can't be found. Does any one have any idea?

My database connection string looks like this.

<

connectionStrings>

<

addname="etzAusBldConnectionString"connectionString="Data Source=CASCADE;Initial Catalog=etzAusBldBaseData1;User ID=sa1;Password=****"providerName="System.Data.SqlClient" />

</

connectionStrings>

Moe

Well, how are you looking for it?

ASP.NET datagrid as datasource

Hello, I've got an ASP.NET project that populates a datagrid depending
on the search criteria of the end-user. I'd like to take the populated
datagrid as my datasource for my report. Can anyone shed some light on
how to do this or point me in the right direction.
Thanks in advance.You should investigate the new controls with VS 2005. For those you give a
table and a report and the control renders it (no server) if using in local
mode.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gallan" <gregallan@.tvl.com> wrote in message
news:1148401708.679329.228750@.j55g2000cwa.googlegroups.com...
> Hello, I've got an ASP.NET project that populates a datagrid depending
> on the search criteria of the end-user. I'd like to take the populated
> datagrid as my datasource for my report. Can anyone shed some light on
> how to do this or point me in the right direction.
> Thanks in advance.
>

ASP.NET database won't connect...

I setup a asp.net project running on http://localhost/ which connects to a
database on another server running sqlserver...
I was able to connect to the database and create my application no problem.
The sqlserver database server had windows 2000 without any SP's or updates.
This was no problem and even though the OS hadn't the .net framework
installed, everything worked fine.
the requirements for that sqlserver database server have since changed, and
windows update was visited to get all the updates, including the latest SP's
as well as the .net framework. I also installed the remote only debugging
for VS.net on this server so that I'm also able to use this as a IIS
server...
Anyway, back to my problem, since these updates the asp.net application i
run from my machine, whcih connects to this database server, has stopped
working with error "SQL Server does not exist or access denied" on the line
where the .Open() is called on the ADO.net database connection object.
Any ideas?It sounds like one of the updates tightened the security on your SQL database.
You'll want to verify that the account you are using for the data still has
sufficient rights.
Is it possible that the sa account or its password was changed?
"Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in message
news:eO9GBgBXDHA.2200@.TK2MSFTNGP09.phx.gbl...
I setup a asp.net project running on http://localhost/ which connects to a
database on another server running sqlserver...
I was able to connect to the database and create my application no problem.
The sqlserver database server had windows 2000 without any SP's or updates.
This was no problem and even though the OS hadn't the .net framework
installed, everything worked fine.
the requirements for that sqlserver database server have since changed, and
windows update was visited to get all the updates, including the latest SP's
as well as the .net framework. I also installed the remote only debugging
for VS.net on this server so that I'm also able to use this as a IIS
server...
Anyway, back to my problem, since these updates the asp.net application i
run from my machine, whcih connects to this database server, has stopped
working with error "SQL Server does not exist or access denied" on the line
where the .Open() is called on the ADO.net database connection object.
Any ideas?|||The strange thing is that when i connect to it via the server explorer it
still works, and I'm able to view tables, edit data etc etc...
If i delete my connection, recreate it and rename it, I get the same result.
If I connect to the database via classic ASP, it works fine...
I've checked the sa login but appears it's still okay.
"Ken Cox [Microsoft MVP]" <BANSPAMken_cox@.sympatico.ca> wrote in message
news:ODWGphBXDHA.652@.TK2MSFTNGP10.phx.gbl...
> It sounds like one of the updates tightened the security on your SQL
database.
> You'll want to verify that the account you are using for the data still
has
> sufficient rights.
> Is it possible that the sa account or its password was changed?
> "Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in message
> news:eO9GBgBXDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I setup a asp.net project running on http://localhost/ which connects to a
> database on another server running sqlserver...
> I was able to connect to the database and create my application no
problem.
> The sqlserver database server had windows 2000 without any SP's or
updates.
> This was no problem and even though the OS hadn't the .net framework
> installed, everything worked fine.
> the requirements for that sqlserver database server have since changed,
and
> windows update was visited to get all the updates, including the latest
SP's
> as well as the .net framework. I also installed the remote only debugging
> for VS.net on this server so that I'm also able to use this as a IIS
> server...
> Anyway, back to my problem, since these updates the asp.net application i
> run from my machine, whcih connects to this database server, has stopped
> working with error "SQL Server does not exist or access denied" on the
line
> where the .Open() is called on the ADO.net database connection object.
> Any ideas?
>
>|||Daniel,
What does your connection string look like?
Benny Tordrup
"Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> skrev i en meddelelse
news:eO9GBgBXDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I setup a asp.net project running on http://localhost/ which connects to a
> database on another server running sqlserver...
> I was able to connect to the database and create my application no
problem.
> The sqlserver database server had windows 2000 without any SP's or
updates.
> This was no problem and even though the OS hadn't the .net framework
> installed, everything worked fine.
> the requirements for that sqlserver database server have since changed,
and
> windows update was visited to get all the updates, including the latest
SP's
> as well as the .net framework. I also installed the remote only debugging
> for VS.net on this server so that I'm also able to use this as a IIS
> server...
> Anyway, back to my problem, since these updates the asp.net application i
> run from my machine, whcih connects to this database server, has stopped
> working with error "SQL Server does not exist or access denied" on the
line
> where the .Open() is called on the ADO.net database connection object.
> Any ideas?
>|||Does this mean i have to set up an "asp.net" account?
I've not seen any place to do this, how is it done?
thanks.
Dan
"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:%23vq9tMCXDHA.2484@.TK2MSFTNGP09.phx.gbl...
> Your server explorer runs under a different account than ASP.Net.
> --
> HTH,
> Kevin Spencer
> Microsoft MVP
> .Net Developer
> http://www.takempis.com
> Complex things are made up of
> lots of simple things.
> "Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in message
> news:u6frlmBXDHA.3444@.tk2msftngp13.phx.gbl...
> > The strange thing is that when i connect to it via the server explorer
it
> > still works, and I'm able to view tables, edit data etc etc...
> >
> > If i delete my connection, recreate it and rename it, I get the same
> result.
> > If I connect to the database via classic ASP, it works fine...
> >
> > I've checked the sa login but appears it's still okay.
> >
> >
> >
> > "Ken Cox [Microsoft MVP]" <BANSPAMken_cox@.sympatico.ca> wrote in message
> > news:ODWGphBXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > It sounds like one of the updates tightened the security on your SQL
> > database.
> > > You'll want to verify that the account you are using for the data
still
> > has
> > > sufficient rights.
> > >
> > > Is it possible that the sa account or its password was changed?
> > >
> > > "Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in message
> > > news:eO9GBgBXDHA.2200@.TK2MSFTNGP09.phx.gbl...
> > > I setup a asp.net project running on http://localhost/ which connects
to
> a
> > > database on another server running sqlserver...
> > >
> > > I was able to connect to the database and create my application no
> > problem.
> > >
> > > The sqlserver database server had windows 2000 without any SP's or
> > updates.
> > > This was no problem and even though the OS hadn't the .net framework
> > > installed, everything worked fine.
> > >
> > > the requirements for that sqlserver database server have since
changed,
> > and
> > > windows update was visited to get all the updates, including the
latest
> > SP's
> > > as well as the .net framework. I also installed the remote only
> debugging
> > > for VS.net on this server so that I'm also able to use this as a IIS
> > > server...
> > >
> > > Anyway, back to my problem, since these updates the asp.net
application
> i
> > > run from my machine, whcih connects to this database server, has
stopped
> > > working with error "SQL Server does not exist or access denied" on the
> > line
> > > where the .Open() is called on the ADO.net database connection object.
> > >
> > > Any ideas?
> > >
> > >
> > >
> >
> >
>|||Daniel,
Apply the following commands in Query Analyzer (replace <ComputerName> with
the name of the SQL Server Computer and <DatabaseName> with the name of the
database that ASP.NET needs access to:
<SQL>
Exec sp_grantlogin '<ComputerName>\ASPNET'
go
use [<DatabaseName>]
go
EXEC sp_grantdbaccess '<ComputerName>\ASPNET'
go
exec sp_addrolemember @.rolename='db_owner', @.membername='<ComputerName>\ASPNET'
go
</SQL>
Best regards,
Benny Tordrup
"Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> skrev i en meddelelse
news:uIQz4oCXDHA.2444@.tk2msftngp13.phx.gbl...
> Does this mean i have to set up an "asp.net" account?
> I've not seen any place to do this, how is it done?
> thanks.
> Dan
>
> "Kevin Spencer" <kevin@.takempis.com> wrote in message
> news:%23vq9tMCXDHA.2484@.TK2MSFTNGP09.phx.gbl...
> > Your server explorer runs under a different account than ASP.Net.
> >
> > --
> > HTH,
> >
> > Kevin Spencer
> > Microsoft MVP
> > .Net Developer
> > http://www.takempis.com
> > Complex things are made up of
> > lots of simple things.
> >
> > "Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in message
> > news:u6frlmBXDHA.3444@.tk2msftngp13.phx.gbl...
> > > The strange thing is that when i connect to it via the server explorer
> it
> > > still works, and I'm able to view tables, edit data etc etc...
> > >
> > > If i delete my connection, recreate it and rename it, I get the same
> > result.
> > > If I connect to the database via classic ASP, it works fine...
> > >
> > > I've checked the sa login but appears it's still okay.
> > >
> > >
> > >
> > > "Ken Cox [Microsoft MVP]" <BANSPAMken_cox@.sympatico.ca> wrote in
message
> > > news:ODWGphBXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > It sounds like one of the updates tightened the security on your SQL
> > > database.
> > > > You'll want to verify that the account you are using for the data
> still
> > > has
> > > > sufficient rights.
> > > >
> > > > Is it possible that the sa account or its password was changed?
> > > >
> > > > "Daniel Bass" <danielbassNOJUNK@.MAILpostmaster.co.uk> wrote in
message
> > > > news:eO9GBgBXDHA.2200@.TK2MSFTNGP09.phx.gbl...
> > > > I setup a asp.net project running on http://localhost/ which
connects
> to
> > a
> > > > database on another server running sqlserver...
> > > >
> > > > I was able to connect to the database and create my application no
> > > problem.
> > > >
> > > > The sqlserver database server had windows 2000 without any SP's or
> > > updates.
> > > > This was no problem and even though the OS hadn't the .net framework
> > > > installed, everything worked fine.
> > > >
> > > > the requirements for that sqlserver database server have since
> changed,
> > > and
> > > > windows update was visited to get all the updates, including the
> latest
> > > SP's
> > > > as well as the .net framework. I also installed the remote only
> > debugging
> > > > for VS.net on this server so that I'm also able to use this as a IIS
> > > > server...
> > > >
> > > > Anyway, back to my problem, since these updates the asp.net
> application
> > i
> > > > run from my machine, whcih connects to this database server, has
> stopped
> > > > working with error "SQL Server does not exist or access denied" on
the
> > > line
> > > > where the .Open() is called on the ADO.net database connection
object.
> > > >
> > > > Any ideas?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

ASP.NET 2 Data Binding with SQL 2005 XML

I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
Any leads to web sites and books that would help along these lines would also be appreciated.
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
On Mar 21, 6:15 pm, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
> I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
> The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
> I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
> I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
> I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
> Any leads to web sites and books that would help along these lines would also be appreciated.
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0
http://www.developer.com/net/net/article.php/3406251
XML data type tips in SQL Server 2005
http://www.codeproject.com/dotnet/XMLdDataType.asp
and...
http://www.google.com/search?hl=en&q=XML+data+type+SQL+2005+asp.net

ASP.NET 1.1 and SQL Server 2005. Possible?

Hey,
I am starting to work on a project using ASP.NET. I am intending to useversion 1.1 cause version 2.0 is still Beta and not finalized yet. Iwas wondering if it is possible to use MS SQL Server 2005 with ASP.NET1.1. As far as I know the SQL Server 2005 requires ASP.NET 2.0. But Istill want to check if it is possible some how and if it was possible,will I find a server on the Internet that hosts SQL Server 2005 withASP.NET 1.1 cause till now all the companies I found provide onlyASP.NET 2.0 with SQL Server 2005.
[I don't actually know for sure but...]
Depends what you mean by "use" I guess. I'm sure it will respond to basic SQL Server requests at least as an OLEDB database, possibly as a SQL Server provider. However if you want to make use of any of the nice stuff then I doubt it very much.

ASP.NET + SQLServer Data Mixup

I've created a ASP.NET site using a SQLServer database hookup, and after creating the project with minimal errors I have run into a major problem. The site is made to be used by multiple users using Update and Select commands at the same time, but I have found that, when multiple users are using the site at the same time, (20+) the data seems to be going to the wrong people.

For example, user 1 was recieving user 2's data. The code works fine with 1-5 users on at once, but seems to have this problem very frequently with more than that. The computers that were being used at the same time were all in the same room, under the same LAN domain, with very similar computer names (ABCD-101-XX where XX is the computer number). I was wondering if this could be the problem, if the computers are in escence waiting for their data, and just steal the first piece of data they can find.

This seemed to happen when the users were performing the same query at the same time, so the data was in the right place, it was just someone elses data.

Any help would be greatly appreciated.Are you storing any data in the Application state or in Cache? These locations are shared between all users and you will definitely get incorrect results. Do not ever share connection or command objects in a global fashion.|||No, I havent been storing any data in the Application state or in the Cache, so that cant be a problem. If anyone out there has experienced this problem before or may know of some sort of a way to fix this, any help would be greatly appreciated.|||A week later and this problem still exists, anyone have any sort of a solution??|||This isn't really a SQL Server/MSDE issue. You might have better luck in the Data Access forum.

And no, I've never seen this problem before, on any platform. My gut feeling is that it's a hardware/LAN issue.

Terri|||Could you share your code with us? I expecte there is concurrency problem in your
Update and Select commands

where I assume you didn't put these two statements within one transaction.

ASP.NET & MS SQL

I need help im a new user to this and previoully have used asp.net with MS Access. But now I am making a new project in ASP.NET with MS SQL So far what I have done is the following but still does not run. I am really strugling at the moment. Please help me, thanks

Im not sure if this is correct
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"

or this:
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

But this is the code below so far which is corrected:

<%@. Page Language="vb" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %
<script runat="server"
Sub Page_Load()
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"
strConnection += "server=ldnlt0433;uid=web;pwd=**secret**; database=JoinersAndLeavers"

''set up command with SQL details and connection details
Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "select * from JoinRequest;"

Dim objCOmmand as New sqlCommand(strSQL, objConnection)
'record set object declared to store records
Dim objDataReader As SqlDataReader

Dim ResultText As String
Dim ResultBit As Boolean

try
objConnection.Open() 'open the connection to the DB

'execute the command with its SQl and connection
' the results go into the record set
objDataReader = objCOmmand.ExecuteREader()

Do While objDataReader.Read()=True
ResultText += objDataReader("AccountType")
ResultText += " "
ResultBit += objDataReader("OutlookMail")
ResultBit += " "
ResultText += objDataReader("PhoneLogin")
ResultText += " "
ResultText += objDataReader("Desktop")
ResultText += " "
ResultText += objDataReader("Laptop")
ResultText += " "
ResultText += objDataReader("Token")
ResultText += " "
ResultText += objDataReader("Mobile")
ResultText += " "
ResultText += objDataReader("XDA")
ResultText += " "
ResultText += objDataReader("Blackberry")
ResultText += " "
ResultText += objDataReader("USBkey")
ResultText += "<br>"
Loop
' response.write( ResultText + "<br> results are out <br>" )

objDataReader.Close()
objConnection.Close()

If IsPostBack Then 'add the new data on if PostBack
' Create and open the connection object
' the conection objects path was set up earlier
objConnection.Open()

' set the SQL string
strSQL = "INSERT INTO JoinRequest ( AccountType, OutlookMail, PhoneLogin, Desktop, Laptop, Token, Mobile, XDA, Blackberry, USBkey ) VALUES ( '" + AccountType.Text + "' , '" + OutlookMail.Text + "', '" + PhoneLogin.Text + "', '" + Desktop.Text + "', '" + Laptop.Text + "', '" + Token.Text + "', '" + Mobile.Text + "', '" + XDA.Text + "', '" + Blackberry.Text + "', '" + USBKey.Text + "')"
' execute the command
' Create the Command and set its properties
objCOmmand = New SqlCommand(strSQL, objConnection)

' execute the command
objCommand.ExecuteNonQuery()

objConnection.Close()
End If

'read back from file and display
objConnection.Open()

strSQL = "SELECT * FROM JoinRequest"
objCOmmand = New SqlCommand(strSQL, objConnection)
dg1.DataSource = objCOmmand.ExecuteREader(CommandBehavior.CloseConnection)
dg1.databind()

objDataReader.Close()
objConnection.Close()

catch e as Exception
Response.Write("Connection failed to open successfully.<br>" + e.ToString())
end try

end sub

r1cz:

Im not sure if this is correct
Dim strConnection As String = "Provider=Microsoft.Jet.sql.4.0;"

or this:
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

Those are 2 different things. The first statement is just initialization of a string with Jet as the DB Provider. The second statement is initialization of SQLCommand.

You mentioned your DB is MS SQL Server.So your connection information would look like:

Dim strConnection As String = "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

Protected objConnection As New SqlConnection(strConnection )
Dim objCOmmand as New sqlCommand(strSQL, objConnection)

Check outwww.connectionstrings.com for sample connection strings. Also check out the Tutorials section of these forums (Its in the menu at the top of the page)

And you should be using Parameterized Queries. You can either google or search these forums for more information on why/how you should use Parameterized Queries.

|||

Thank you that was helpful I found parameters and got that done aswell thank you for your advice. Im jus stuck at the moment now becuase I got 1 form but I want to open two tables in the database. The 1 form will enter details for two tables.

1 table will have employee information
1 table employee's request information

They both link by using a foreign key so in the form I will have some user information i.e. name and then their request as check boxes. I have a look at different forums and google tryin to get information but no luck. Is this possible if so how? Thanks.

|||

You could send all the values to a stored proc and from the stored proc isnert/update all the tables you need to. That way you could get it all done in one trip and your code is cleaner.You could start by writing a stored proc that works as you want it to and test it via Query Analyzer. Having done that check out the ExecuteNonQuery method.

Asp.net - SQL Server remote connection falied

Hi, I have an issue with ASP.NET 2.0 and SQL Server 2005

My project when hosted in a machine its working, but in case of some other machine it is throwing the following error

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have turned off my firewall and network configuration for sql server and its also done as tcp/ip and named pipes…though it is not working …

The surprise is if I run using IDE its running , in the same machine after hosting it is not running, where the db is in another machine. What could be the problem? Any suggestions would be highly appreciated. Regards,Naveen

Regards,

Naveen

This is usually issue with user name, password and permissions.

First did you check your connection string? You need to change server name.

Second, are you using Windows authentication or mixed mode in your SQL Server?

|||

Thanks millet, it worked when I changed the permission

|||

Hi Naveen,

Can u pls. post the changes you made. because i'm also getting this error.