sql data to string

[Guest]

Hi,

I need to fill a string with data from sql server, i am using the following
statement for retrieving the data (example)

public SqlDataReader Inleiding ()
{

strConn="Data Source=W2K3-BASE;uid=test;pwd=test;Initial
Catalog=northwind";
mycn = new SqlConnection(strConn);
SqlCommand myda = new SqlCommand ("Select description FROM Categories
where categoryid = 1 ", mycn);
myda.CommandType = CommandType.Text;
mycn.Open();
SqlDataReader ddsqlReader =
myda.ExecuteReader(CommandBehavior.CloseConnection);
return ddsqlReader;
}

But when I fill the string with the ddsql reader it gives me a true value
and not the value of the record! How can I solve this?

Filling the string like this:

string test = Testing().ToString();

 

[Marina]

Right...

That is because you are essentially returning a record set. The data reader
gives you one row of data at a time, in a readonly, forwardonly fashion.
Calling ToString on it, doesn't really make logical sense.

Additionally, you have a connection leak, since the data reader never gets
closed in your code.

Use ExecuteScalar when you need one cell worth of data (first column of
first row returned by query). And always close connections before you exit
the method that opened the connection (unless you are very careful about
closing them afterwards, but that doesn't appear to be the case).

 

[Guest]

ok thanks, i will give it a try. And i will close the connection!
--
thanks,

Remco Ploeg

Right...

That is because you are essentially returning a record set. The data reader
gives you one row of data at a time, in a readonly, forwardonly fashion.
Calling ToString on it, doesn't really make logical sense.

Additionally, you have a connection leak, since the data reader never gets
closed in your code.

Use ExecuteScalar when you need one cell worth of data (first column of
first row returned by query). And always close connections before you exit
the method that opened the connection (unless you are very careful about
closing them afterwards, but that doesn't appear to be the case).

 

[Guest]

ok here my new code, it seems to work:

public string Inleiding ()
{

SqlConnection myConn = new SqlConnection ("Data
Source=W2K3-BASE;uid=test;pwd=test;Initial Catalog=northwind" );

// define the command query
string query = "Select description FROM Categories where categoryid = 1";

// initialize command object with the specified query and connection
SqlCommand myCommand = new SqlCommand ( query, myConn );

// open the data connection
myConn.Open ( );

// execute the command
string test = myCommand.ExecuteScalar().ToString();

// close the data connection
myConn.Close ( );

return test.ToString();

}

--
thanks,

Remco Ploeg

Right...

That is because you are essentially returning a record set. The data reader
gives you one row of data at a time, in a readonly, forwardonly fashion.
Calling ToString on it, doesn't really make logical sense.

Additionally, you have a connection leak, since the data reader never gets
closed in your code.

Use ExecuteScalar when you need one cell worth of data (first column of
first row returned by query). And always close connections before you exit
the method that opened the connection (unless you are very careful about
closing them afterwards, but that doesn't appear to be the case).