Cannot select SQL query with DAL to show in message box

[adrian.mcguinness]

I have a connection in a DAL to SQL server. Using this connection I am trying to read the query. I have tried using ExecuteReader() and ExecutreNonQuery() with no luck.
Here is my code:

      private void button1_Click(object sender, EventArgs e)
        {
                    try{
            var cn = new Connection_Handler();
            string input = selectinput.Text;
            string reader = cn.Execute_SQL(input).ToString();
            MessageBox.Show(reader);
                        }
            catch(Exception ex){
                MessageBox.Show(ex.Message);
            }
}
    class Connection_Handler
    {
        SqlConnection cn;
        void ConnectiontoSQL()
        {
            string str = "server=xxx; database=jobSearch; user=sa; password=xxx";
            cn = new SqlConnection(str);
            cn.Open();            
        }
        public int Execute_SQL(string select)
        {
            ConnectiontoSQL();
            string Query = "select " + select + " from jobSearch where searchID = 1";
            SqlCommand cmd = new SqlCommand(Query, cn);
            SqlDataReader reader = cmd.ExecuteReader();
            return Convert.ToInt32(reader);
        }
    }

my error message is "unable to cast object of type 'sqldatareader' to type 'iconvertible' "

[AleMonteiro]

Hi there!

You can't convert an SQLDataReader to an int. You have to use the reader to get your values.

Check those links:
http://msdn.microsoft.com/pt-br/library/haa3afyz(v=vs.110).aspx
http://www.akadia.com/services/dotnet_data_reader.html

Anyway... I think is more practial to use an DataSet, something like this:

class Connection_Handler
    {
        SqlConnection cn;
        void ConnectiontoSQL()
        {
            string str = "server=xxx; database=jobSearch; user=sa; password=xxx";
            cn = new SqlConnection(str);
            cn.Open();            
        }
        public DataSet SelectSQL(string select) {

            SqlCommand command = new SqlCommand( select, this.cn );

            DataSet objDataSet = new DataSet();
            SqlDataAdapter objSqlDataAdapter;

            objSqlDataAdapter = new SqlDataAdapter( command );
            objSqlDataAdapter.Fill( objDataSet );

            command.Dispose();

            objSqlDataAdapter.Dispose();
            objSqlDataAdapter = null;

            return objDataSet;
        }

        public int Execute_SQL(string select)
        {
            ConnectiontoSQL();
            string Query = "select " + select + " from jobSearch where searchID = 1";

            DataSet ds = SelectSQL(Query);

            if ( ds.Tables[0].Rows.Count > 0 ) { // Found something

                // Returns the first cell of the first row
                return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());

            }
            // Exit code for no item found
            return -1;
        }
    }

Or, if you are going to retrieve just one value, you could use ExecuteScalar that already returns the first cell of the first row.

int result = Convert.ToInt32(command.ExecuteScalar())

[adrian.mcguinness]

it returns "cannot find table 0"
The database isnt empty.

[AleMonteiro]

Is ds.Tables null? Is the connection and query ok?

[adrian.mcguinness]

Hi it accesses the database. the resulting error message is "input string was not a correct format"

[AleMonteiro]

In wich line does this error occurrs?

[adrian.mcguinness]

On debugging, ds.Tables[0].Rows[0][0] shows the value from the query in execute_SQL(). from

            if (ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());

            }

            return -1;

the error is thrown after the try-catch statement.

I suspect it is to do with

            string reader = Convert.ToInt32(cn.Execute_SQL()).ToString();
            MessageBox.Show(reader);

[AleMonteiro]

Whats the value in ds.Tables[0].Rows[0][0] ?

In debugging, use the Exception.StackTrace and Exception.InnerException.StackTrace to see exactly where the errors occurs and it's full stack trace.
Obs.: InnerException is recursive.

[adrian.mcguinness]

the value is "monster.co.uk" as expected in ds.Tables[0].Rows[0][0]. when debuging but the exception is still thrown.

but the stackexception is

    ex.StackTrace   "   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)\r\n   at System.Convert.ToInt32(String value)\r\n   at SearchingforJob.DAL.Connection_Handler.Execute_SQL() in C:\\Documents\\Visual Studio 2010\\Projects\\SearchingforJob\\SearchingforJob\\DAL\\Connection_Handler.cs:line 46\r\n   at SearchingforJob.input.button1_Click(Object sender, EventArgs e) in C:\\Documents\\Visual Studio 2010\\Projects\\SearchingforJob\\SearchingforJob\\input.cs:line 33"   string

[BMXDad]

Looks like your trying to convert "monster.co.uk" to an int

[AleMonteiro]

ggamble is right... I assume that you were selecting an int, because you was already converting.

If the value is 'monster.co.uk' you can't convert it, just use .ToString().

[adrian.mcguinness]

Hi, I have an if statement in my connectionhandler and it will not let me return a string for ds.Tables[0].Rows[0][0] i tried making a variable and it still didnt like it.

here is the code im on at the moment.

            if (ds.Tables[0].Rows.Count > 0)
            {
                string reader = ds.Tables[0].Rows[0][0].ToString();
                return reader;

            }

            return -1;

[AleMonteiro]

That's probably because your method returns an int.
Try this:

public string Execute_SQL(string select)
        {
            ConnectiontoSQL();
            string Query = "select " + select + " from jobSearch where searchID = 1";
            DataSet ds = SelectSQL(Query);
            if ( ds.Tables[0].Rows.Count > 0 ) { // Found something
                // Returns the first cell of the first row
                return ds.Tables[0].Rows[0][0].ToString();
            }
            // Exit code for no item found
            return "-1";
        }

[adrian.mcguinness]

will not debug. cannot implicitly convert type "string" to "int"

[adrian.mcguinness]

Sorted it. changed public int Execute_SQL(), to public string Execute_SQL()

[AleMonteiro]

That's exactly what I posted. =)

[adrian.mcguinness]

My apologies.