Problems with getting data from Database

[Raenius]

Hi all,

I told you I'd be back ;-)

Oke here's the problem, I am currently toying around with retreiving data from my database server (SQL Server 2000) which is on my local LAN.

I just made a form with a button in Borland C#Builder and when you press the button it should connect and retreive and display..

The problem is that when we get to the while loop, it stops...or so it seems.

Here is the code:

		SqlConnection myConnection = new SqlConnection("user id=test;" +
									   "password=test;server=192.168.x.x;" +
									   "Trusted_Connection=yes;" +
									   "database=netsentry; " +
									   "connection timeout=30");
		try
		{
		myConnection.Open();
		MessageBox.Show("Succesfully connected!");
			try
			{
				SqlDataReader myReader = null;
				SqlCommand myCommand = new SqlCommand("SELECT * FROM ROUTERS",myConnection);
				myReader = myCommand.ExecuteReader();
					while(myReader.Read())
					{
					// here it stops number 4 never displays
			 MessageBox.Show("4");
					 MessageBox.Show(myReader["routerID"].ToString());
					 MessageBox.Show(myReader["name"].ToString());
					}
			}
			catch (Exception errr)
			{
			MessageBox.Show(errr.ToString());
			}
		//myConnection.Close();
		}

		catch(Exception err)
		{
		MessageBox.Show(err.ToString());
		}

Does someone have any suggestions or tips / advice?

- Raenius


"Free will...is an illusion"

 

[crazyboybert]

Hi Raenius

Try adding

MessageBox.Show(myReader.HasRows.ToString());

before entering you while loop. that should let you know if there are any rows to read in the reader. If you are not getting any errors and also never getting the message box displaying 4 this suggests to me your query isn't returning any data.

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[Raenius]

Wow that was fast, this forum/website never ceases to amaze me...

I added the line you mentioned and you were right, it appears that indeed my command does not return any data! After a check I found that my database is empty

I'll fill it up now and get back to you asap...

- Raenius

ps. Thanks again!

"Free will...is an illusion"

 

[Raenius]

Allright just tried it and it works beautifully ;-)

Thanks a lot for your time!

- Raenius..

ps. I'll be back again...

"Free will...is an illusion"

 

[Raenius]

Strange thing, I got a different error this time. This is my similar code:

		SqlCommand SelectCommand= new SqlCommand("SELECT MAX(routerID) FROM ROUTERS", myConnection);
					SqlDataReader reader = null;
					reader = SelectCommand.ExecuteReader();
					MessageBox.Show(reader.HasRows.ToString());
						while (reader.Read())
						{
						MessageBox.Show("test");
						MessageBox.Show(reader["routerID"].ToString());
						}

When I run it, it gives true on HasRows, it displays the Test message and then it stops and gives the error:

System.IndexOutOfRangeException: routerID

Does anyone have a suggestion?

- Raenius

"Free will...is an illusion"

 

[Kris11]

Where is routerID in your SQL. you have a computed field MAX(routerID) not routerID. Either refer this field with an index # or change your SQL to

"SELECT MAX(routerID) as routerID FROM ROUTERS"

and then refer the field as your doing now

-Kris

 

[obislavu]

>System.IndexOutOfRangeException: routerID
beacuse the "routerID" column is not a native column retrieved bt reader.
You could change to:

 MessageBox.Show(reader[0].ToString());

or change the select statement :

SqlCommand SelectCommand= new SqlCommand("SELECT MAX(routerID) AS routerid FROM ROUTERS", myConnection);

-obislavu-

 

[Raenius]

Thanks a lot, dang what a simple solution.

Thanks again.

- Raenius

"Free will...is an illusion"