A big problem in connecting to database

Question

0.00/5 (No votes)

See more:

Hi!

My program worked very well untill yesterday, when i was running it, my grid view that got data source from a data table of sql data base didn't show any thing. i tried to understand what was wrong with a break point, i understood that program jumps this part and doesn't connect to data base:

C#

SqlConnection cs = new SqlConnection("Data Source=.\\local_2008; Initial Catalog=Library; User Id=userLibrary; Password=123456");

the interesting part is that, it doesn't show any error and when i used try/catch the exception doesn't show anything about error.
and what made me crazy is that when i use a breakpoint on this line to get the error and select the part and use Quick Watch it shows no error and then i click f5 to continue the program works as it should and shows the table!!!!!!
please help me to solve this unbelievable problem

thank you

C#

SqlConnection cs = new SqlConnection("Data Source=.\\local_2008; Initial Catalog=Library; User Id=userLibrary; Password=123456");
SqlCommand cmd = new SqlCommand("searchBook", cs);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@biCode", SqlDbType.Int).Value = biCode;
cmd.Parameters.Add("@biName", SqlDbType.NVarChar).Value = biName;
cmd.Parameters.Add("@biAuthor", SqlDbType.NVarChar).Value = biAuthor;
cmd.Parameters.Add("@biTranslator", SqlDbType.NVarChar).Value = biTranslator;
cmd.Parameters.Add("@biPublisher", SqlDbType.NVarChar).Value = biPublisher;
cmd.Parameters.Add("@biSubject", SqlDbType.Int).Value = biSubject;
cmd.Parameters.Add("@biStatus", SqlDbType.Int).Value = biStatus;
cmd.Parameters.Add("@biIssueDate", SqlDbType.VarChar).Value = biIssueDate;
cmd.Parameters.Add("@biLink", SqlDbType.NVarChar).Value = biLink;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

return dt;

Good news:
I used app.config again and the old error didn't happened again but now it jumps out from da.Fill(dt) with
Instance failure
error now what should i do?

new part:

C#

string str = System.Configuration.ConfigurationManager.AppSettings["cs"];
                SqlConnection cs = new SqlConnection(str);

Posted 9-Sep-13 23:37pm

i.fakhari

Updated 11-Sep-13 5:23am

v4

Add a Solution

Comments

Richard MacCutchan 10-Sep-13 5:52am

You have created a connection, but where is the code that actually connects to the database?

i.fakhari 10-Sep-13 5:59am

Supplementary detail moved to original question.

Richard MacCutchan 10-Sep-13 6:27am

You need to check the response from the da.Fill() method call, to see if any rows were read. It may be that your stored procedure is not returning any data.

i.fakhari 10-Sep-13 6:44am

the code doesn't read that part it jumps out from the first line.
what do you mean off checking da.fill you mean may my data base table has some problems.
it may help that this problem when happened that i wanted to use app.config to use connect string but when i remove it to solve the problem i found the problem again !!!!!!!!!!!!!!

Richard MacCutchan 10-Sep-13 6:53am

Try rebuilding your project and running again through the debugger. It sounds like you may have some bad code in there.

Also, the SqlDataAdapter.Fill() method returns a count of the number of rows filled. You should check this return value, not just assume that your query will work.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Sep-13 7:28am

Hi Richard,

He has not opened the Connection at all. Please check my answer.

Thanks,
Tadit

i.fakhari 10-Sep-13 7:38am

i rebuilt it Richard and i checked that there were right

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Sep-13 7:29am

Please check my answer.

i.fakhari 10-Sep-13 7:40am

You know my procedure is not setting or updating to execute or open query it's just a selection from db
but i tried to open cs but unfortunately it doesn't work

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Sep-13 7:43am

Whatever you do on DataBase, you need to Open the Connection.

Have you changed the ConnectionString as I suggested in my Answer?

i.fakhari 10-Sep-13 8:22am

Yes but it showed the old error
The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.

i.fakhari 10-Sep-13 7:43am

i think the problem get back to the app.config that i used because when i searched the first error that showed i found s.t about problems of app.config
the error that catch give me:
The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.

i.fakhari 10-Sep-13 8:10am

let me upload some photos to make the problem clear
1. my code
http://www.uploadmb.com/dw.php?id=1378814099
2. try/catch jump out in first line and show this error
http://www.uploadmb.com/dw.php?id=1378814218
3. this is the exception that showed by a message box
http://www.uploadmb.com/dw.php?id=1378814308
4. then the grid view shows nothing
http://www.uploadmb.com/dw.php?id=1378814465
5. so i set a break point to get the error
http://www.uploadmb.com/dw.php?id=1378814516
6. and i used quick watch to understand what's wrong with it and there is no error!
http://www.uploadmb.com/dw.php?id=1378814604
7. this part make me crazy. when i press f10 to go on the program will work rightly!!!!!!!!!
http://www.uploadmb.com/dw.php?id=1378814705
8. and the grid view shows the data.
http://www.uploadmb.com/dw.php?id=1378814776

i think with these photos and explanations there is no dark point in my question !!! so now help me to solve it
thank you

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Sep-13 10:16am

Please check the InnerException.

i.fakhari 10-Sep-13 11:33am

what do you mean of InnerException?

3 solutions

Solution 2

for dataadapter you have not passed the connectionstring. try

C#

DataAdapter da=New DataAdapter(query,ConString)

it should work.and you need not manually open a connection for a dataadapter.it does that automatically.

Posted 10-Sep-13 2:43am

Member 8622273

Updated 10-Sep-13 4:13am

v2

Comments

i.fakhari 10-Sep-13 11:36am

I did it
but
As i said it jumps from first step and doesn't read it even

Solution 1

Try like below...

C#

SqlConnection cs = new SqlConnection("Data Source=.;Initial Catalog=Library;User ID=userLibrary;Password=123456");

Posted 10-Sep-13 0:52am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Updated 10-Sep-13 4:22am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

i.fakhari · Accepted Answer · 2013-09-11T05:39:00

Solution 3

special thanks to who help

I don't know what was the first problem but when i used app.config it solved.

and about the second part
it was from the connect string. be careful when you use app.config because it's XML and doesn't turn // to / and you should do that your self :D
BYE BYE

Posted 11-Sep-13 5:39am

i.fakhari