Database(sql2008 express)(ASP inC#)

Question

4.50/5 (2 votes)

See more:

hi , i wrote this code snippet, but i have not any result.
after compile, i have not any data in the ListBox.
Table in the database also have data.
please help me thank you.

C#

public partial class _Default : System.Web.UI.Page 
{
    private string connectinString=
        WebConfigurationManager.ConnectionStrings["Pubs"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
       if(!IsPostBack)
       {
           FillAuthorList();
       }
    }  
      private void FillAuthorList()
      {
         lstItems.Items.Clear();
         string SelectSQL="SELECT id, au_lname, au_fname FROM Authors";
          SqlConnection con = new SqlConnection(connectinString);
          SqlCommand command = new SqlCommand(SelectSQL, con);
          SqlDataReader reader;
          
          try
          {
              con.Open();
              reader = command.ExecuteReader();
              reader.Read();
              while (reader.Read())
              {
                  ListItem newItem=new ListItem();
                  newItem.Text=reader["au_lname"]+","+reader["au_fname"];
                  newItem.Value=reader["id"].ToString();
                  lstItems.Items.Add(newItem);
              }
            reader.Close();
          }
          catch(Exception Err)
          {
              lblResult.Text="Error reading list";
              lblResult.Text+=Err.Message;
          }

          con.Close();  
            }
}

[Edit]Code block added[/Edit]

Posted 26-Dec-12 0:41am

maysam_p82

Updated 26-Dec-12 0:42am

Thomas Daniels

v2

Add a Solution

Comments

maysam_p82 26-Dec-12 6:43am

Compile pointer goes to while loop and then jump to the con.close() method. without error.

Deenuji 26-Dec-12 6:48am

pls post ur error below this code.....

1 solution

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)

OriginalGriff · Accepted Answer · 2012-12-26T00:50:00

Solution 1

First off, if you only have one record in your DB table, then you will get nothing - because you discard the first record you read:

C#

reader.Read();
while (reader.Read())

Every time you call the Read method, the current record is replaced with the next. Get rid of the first line of these two to leave just the while loop.

Secondly, I would also recommend explicit casting:

C#

newItem.Text=(string) reader["au_lname"] + "," + (string) reader["au_fname"];

You don't need it (as implicit ToString calls will probably be made), but it is worth doing from a readability and type safety point of view.

If you have more than one row of data, then you need to look at your lblResult label - do you get any message? That should help.

Posted 26-Dec-12 0:50am

OriginalGriff

Comments

maysam_p82 26-Dec-12 6:58am

i have more record stored in the table.
I don't have any message at the lblResult.
also,I said compiler does not go into the while loop

OriginalGriff 26-Dec-12 7:25am

Um...no you didn't...

maysam_p82 26-Dec-12 7:03am

I think dataReader couldn't find any data at the table.also i must say that i cant attach database in sqlserverexpress,Im using server explorer as good.
but im not sure every thing is okay about this kind of use.

OriginalGriff 26-Dec-12 7:28am

If you aren't getting a message on your label, then what is happening? There are a number of ways this could go wrong, starting with your connection string - which if you are having trouble may be the source of your problem. Look at the string: edit your code to display the actual string you are using, and check it against the SQL server instance you are using.
Then check that it connects ok in your code, before moving to actual data.
I would suspect that you are trying to access the wrong DB - but that should give you an error message in the label.

maysam_p82 26-Dec-12 7:44am

<add name="Pubs" connectionstring="data source=localhost\SQLEXPRESS;initial catalog=Pubs;integrated security=SSPI">
this is my string placed in the web.config
Also I tested another application that give the server version,and that worked successfully. I don't know why it doesn't work properly:(.
please help me

OriginalGriff 26-Dec-12 7:47am

I'm trying to - but you don't seem to give me any information!
What is the actual string you use to connect - not "I think it's this, it's in my Web.Config" - display it in your method instead of trying to load info, and see exactly what you get.

maysam_p82 26-Dec-12 7:46am

Should I did something else other than creating database,place in app_data folder,creating connection string and creating table ,assigning data to that?

OriginalGriff 26-Dec-12 7:50am

If you are using SQL server, then you can't just dump your databse into the App_data folder - you have to attach it to treh SQL server instance - which is probably on a different PC (or PC's) and has no access to your Data folder. Have you tried creating a database in the actual server instance?

maysam_p82 26-Dec-12 7:47am

In server explorer?

maysam_p82 26-Dec-12 7:56am

I tried that earlier,but I could not attach my database,in another folder like ap_data. But I read in my book that I can do the same in server explorer as good. Second, why in my app. I didn't receive exception once app connected to DB while I didn't attach my DB in sql server express2008?

maysam_p82 26-Dec-12 8:01am

why i cant attach any database in sqlserver in another folder?I was received error about permissions and so on,and that forced me to use server explorer instead.

OriginalGriff 26-Dec-12 8:07am

It is a permissions issue - the problem is that the SQL Server instance doesn't have permissions to access the folder on the PC your website is stored on - it may not even have any access to the PC for security reasons!

Try creating the DB in the SQL server instance - if you have the connection details, you can try connecting to it via SSMS and create it from there,. or you can do it from code.

maysam_p82 26-Dec-12 8:15am

But so why I didn't receive any error about it?also i think did that earlier and I could not connect to DB. Also i hear the app looks for a DB in app_data folder in asp ones.

OriginalGriff 26-Dec-12 8:23am

Why no error? Dunno - I can't see your screen!
The app will look for databases in app_data - but that is LOCAL databases (Access / SQLCE SQLite), not SQL server databases where the program accessing them is not yours! When you connect to SQL, you are talking to a program (probably on a different machine on a LAN) which accesses the database - you program does not touch the database itself, only SQL Server does. If it can't get at the file (and it can't) then you can't get any data!

maysam_p82 26-Dec-12 8:41am

Tnx,i think my problem has solve ,I have mistake in connecting to DB.I've created a DB with the same name(Pubs) and I did not fill in data in tables.;) Because I thought asp connect in to the app_data DB not in sql server folders.Anyway I filled the table now,but when it tries to load the page send me this error:
"unable to start program: http://localhost:1077/ExAsp/Default.aspx
Element not found"

maysam_p82 26-Dec-12 8:44am

Is this error for connection timeout parameter?

maysam_p82 26-Dec-12 8:45am

Because it seems asp suspends while sql respond,I think.

OriginalGriff 26-Dec-12 9:24am

It's possible - it can take a little while to connect to SQL, but not in human terms.

What timeout values have you set?

maysam_p82 26-Dec-12 10:15am

Thank u,that was not for the technical problems,I've reboot my machine and the problem fixed. My app finally executed.

OriginalGriff 26-Dec-12 10:25am

Glad to hear it! :)

maysam_p82 26-Dec-12 12:58pm

So can u tell me that what is server explorer role in thi scenario?because I was defined another database in server explorer and also a table and finally i couldnt work with it. And Also I didn't understand app_data's role. Because , once I copied and pasted my database file in app_data , then in sql server attached it,I received error,and icouldn't attach DB file from even another folder at all ! In addition , SQL server connection and authentication are a little bit complicated,and depends to various aspects that is hard to understand for me!