having trouble connecting to database

Hi I am new at this.

I have a little program written in C# in asp.net. The program basically accesses a database and stores new records. The database is supposedly already attached to MSDE so I am able to see the tables of the database inside asp.net. I can click on the individual slots of the table and modify the datas manually. However, I want to connect to the database from my C# program and be able to input data into the database via the website that the C# program produces. After I type in the data into the website and click the submit button on the website, I get an error page that says this:

Login failed for user 'sa'

the line of code thats causing this error is:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

Why is it not able to connect to the database?

YourChild:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

I am taking the liberty to presume that your database name is Friends.I just couldn't figure what the "\\NetSdk" means. Perhaps you can addsomething to my knowledge.

Most probably if you aren't using Integrated Security for accessing thedatabase, you'd need to specify the password in the connection stringas well. You can make it something like this:

con =new SqlConnection("data source=(local);initial catalog=Friends;user id=sa;pwd="yourpassword")

Remember,it is a bad habit to store your connection string in the code. Tryputting it in the web.config, but you might want to do this later, whenyou get the hang of things.

Smiles.|||

uXuf:

YourChild:

con =

new SqlConnection("data source=(local)\\NetSdk; initial catalog=Friends; user id=sa");

I am taking the liberty to presume that your database name is Friends. I just couldn't figure what the "\\NetSdk" means. Perhaps you can add something to my knowledge.

Most probably if you aren't using Integrated Security for accessing the database, you'd need to specify the password in the connection string as well. You can make it something like this:

con =new SqlConnection("data source=(local);initial catalog=Friends;user id=sa;pwd="yourpassword")

Remember, it is a bad habit to store your connection string in the code. Try putting it in the web.config, but you might want to do this later, when you get the hang of things.

Smiles.

Thank you it works! I'm so glad you came by! now I am in the process of trying to stick the 'userName' and 'password' into the web.config file by using:

<

identityimpersonate="true"userName="sa"password="mypassword"/>

only now when I hit compile, Windows is giving me an error that says:

"Unable to start debugging on the web server. Server side-error occurred on sending debug HTTP request." Do I need to change something else to make this work?

There is another piece of code at the top of the web.config file that looks like this:

<authenticationmode="None"/>

Should "None" be set to "Windows"?

Then there is another username and password inside the "Directory Security" in IIS via 'property' for the virtual directory corresponding to the directory of the project. Is this username and password associated with the userName and password used to access the database?

Have 100+ columns, or reduce it down to about 3?

Basically, what I'm doing is storing answers to questions in a survey. I have two ways I can organize the table:

1. Just having a table with lots of columns - one for each question

2. A table with only about maybe 3 columns:

SurveyID
QuestionID
QuestionAnswer

In this second case, the the primary key would be both SurveyID and QuestionID combined, of course.

I don't fully know the pros and cons of the two approaches, and both look like they would work. Right now, I'm using option 1, but I keep wondering if option 2 might be better. Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort), and I know option 2 would be a way of avoiding that. By the way, the questions themselves are stored in an xml file, if it means anything. Anyhow, once the survey is being used, there shouldn't be any further changing of questions. And there's also just too much I don't know about (how is performance affected, for example?).

Any ideas which is better and why?if you're currently doing it like option 1, then presumably you are able to write some sql for it?

okay, a couple of sample problems, may we see your sql please

1) which surveys had more than half the questions answered correctly
2) which surveys had the same number of answers as survey 23|||None of the surveys have been answered already. I'm still writing the code that will store the answers given. The questions themselves aren't final yet, but the xml file is getting constantly updated as we decided what to ask and not to ask. So when the xml file is changed, I generate a new (rather large) Create Table statement, drop the old table, and create the new one so things can be tested out. It's just extra work while developing the survey.

When a new survey is started, a new record is created for it right from the beginning, with all columns set to null. As questions get answered, values get inserted. This way, it's possible to come back later for whatever reason.

I don't see how posting the entire Create Table statement helps anything. The questions' answers are stored as bits, ints, reals, datetimes, or varchars, depending on the kinds of questions. Unanswered questions remain null, of course. It looks like there are about 160 columns in the table right now that are specifically for answers to the questions.

Really, my question simply is am I going to be ok with having a large number of columns in the table, or am I better off using what I mentioned above as "option 2", reducing the number of columns in the table.|||Think about R937's question a little more. Suppose you inherited a system like your option 1. Further, suppose you were given requirements to write reports based on R937's questions. How would you go about it?

Design questions need to take into account how you store the data AND how you get it back out. Unless of course, you are designing a black hole, in which case, it does not matter how to get data out.|||didn't want to see the CREATE TABLE statement

wanted you to think about the SQL that you would have to write against the humungous table to get meaningful stats out of it

my advice: use whichever structure you feel most comfortab le with|||oh...so in your opinion, it doesn't really matter? I was wondering if maybe one way was technically superior and/or more efficient for some reason that I may not have already seen.|||oh...so in your opinion, it doesn't really matter? no, i didn't really say that :)

i think 1NF might be applicable here

actually, i would almost always implement option 2

but since you were asking for advice, and since you seem to have settled on option 1 already, i figured it was important to let you know that being comfortable with a technique also has some importance|||Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort)Learn ALTER TABLE syntax - it is substantially less effort than the method you describe - especially once you have data in your tables.|||You have to go option 2. There is no other way...Don't even consider option 1 - total train wreck in the making.

(sorry to diffuse the suspense created by r937 but it was driving me nuts!)|||my current project is both 1 and 2 because we are processing gigs of claims every week. 1 for OLAP and 2 for OLTP. So my answer is both.sql

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D