CODEWITHSUNDEEP
c#asp.netoracle-database
Chinonso Akolam
Chinonso Akolam

Reputation: 11

cannot implicitly convert type 'int' to 'system.data.dataset'

Hi all, please I need your help, I am trying to execute a query and put all the retrieved data in a data set, but I get this error "cannot implicitly convert type 'int' to 'system.data.dataset'"

Here's the code:

// this is a small piece of the sql
 String Astra_conn = ConfigurationManager.ConnectionStrings["AstraSeverConnection"].ConnectionString;

System.Text.StringBuilder sql = new System.Text.StringBuilder();

sql.Append(" SELECT ROWNUM AS ID, institution, LPAD (a.zone_name, 3, '0') AS campus, ");
sql.Append(" term_name AS term, student_instance_id AS student_id, subject, course, ");
sql.Append(" section_name AS section_num, offering AS title, ");

//Its OracleConnection because it is an Oracle server otherwise, it would be SqlConnection.

    DataSet rs = new DataSet();
    OracleConnection Astra_db_Conn = new OracleConnection(Astra_conn);
    string myquery = sql.ToString();
    OracleCommand cmd = new OracleCommand(myquery);

Astra_db_Conn.Open();
try
{
    SqlDataAdapter adpt = new SqlDataAdapter();
    rs = cmd.ExecuteNonQuery(); // this is where is get the error.
    adpt.Fill(rs);

}
catch(Exception e) 
{
log.Error("*** ERROR *** IRISExportQueries.loadStudentInfoLearningSites():" + e);

}

I've also tried

 Astra_db_Conn.Open();
 try
 {
     SqlDataReader reader = new SqlDataAdapter();
     reader = cmd.ExecuteNonQuery(); // this is where is get the error.


 }
 catch(Exception e)
 {
log.Error("*** ERROR *** IRISExportQueries.loadStudentInfoLearningSites():" + e);</pre>

 }

Then I get the error: "cannot implicitly convert type 'int' to 'System.Data.SqlClient.SqlDataReader'"

Thanks your help will be very much appreciated.

Upvotes: 0

Views: 6435

Answers (3)

Debasish
Debasish

Reputation: 68

SqlDataAdapter adapt= new SqlDataAdapter(cmd.CommandText,cmd.Connection);
adapt.Fill(rs, " Your Table name as it is in database inside this quotation");

now u can give source to ur data views like datalist or datatable or gridview as following

Datalist1.DataSource= rs.Tables("Your Table name as it is in database inside the above q mark")

now atlast jst bind it

Datalist1.DataBind();

Upvotes: 0

Claudio Redi
Claudio Redi

Reputation: 68400

The problem is that ExecuteNonQuery returns the number of affected rows (an integer) and not a DataSet or DataReader. I'm afraid you're not using ADO.NET components correctly.

These 2 lines are enough to fill a DataSet

SqlDataAdapter adpt = new SqlDataAdapter(cmd);
adpt.Fill(rs);

In any case this is not your only problem, you're mixing Sql* ADO.NET components with Oracle*ones. Adapter should be OracleDataAdapter

OracleDataAdapter adpt = new OracleDataAdapter(cmd);
adpt.Fill(rs);

Something else: you're never assigning the connection to the command. You should do

OracleCommand cmd = new OracleCommand(myquery, Astra_db_Conn);

And at last but not least important, dispose every instance of classes implementing IDisposable interface, otherwise unmanaged resources as connections to datasase won't be released.

This is the final version applying all my suggestions

var rs = new DataSet();
string myquery = sql.ToString();
using (var Astra_db_Conn = new OracleConnection(Astra_conn))
using (var cmd = new OracleCommand(myquery, Astra_db_Conn))
using (var adpt = new OracleDataAdapter(cmd))
{
    Astra_db_Conn.Open();
    adpt.Fill(rs);
}

Upvotes: 4

Aaron Viviano
Aaron Viviano

Reputation: 306

The method ExecuteNonQuery() returns an int with the number of rows that are affected by the command.

To access the data from the query you should see this existing answer: Direct method from SQL command text to DataSet.

Upvotes: 1

Related Questions