SQL query result to variable?

Question

I usually do this in VB and its much easier, this is my first time in C# and I can't figure it out. I'm just wanting to read a row, check that it has rows, and assign the number that exist in that column to StockQuan

    public int StockQuan;
    public int NewAmount;

    public const string ConnectionStringToORACLE = "Provider=OraOLEDB.Oracle.1;Data Source=Souarce;User ID=user;Password=pass;";
    public OleDbDataReader rowread;

    public OleDbDataReader Read_quan(string txtPartNo, int txtAmount)
    {
        var conn = new OleDbConnection(ConnectionStringToORACLE);
        conn.Open();

        string query = "select QUANTITY_IN_STOCK from MPCS.SHOP_INV_STOCK where SI_KEY = ? AND LOCATION = ?";
        var cmd = new OleDbCommand(query, conn);

        cmd.Parameters.AddWithValue("SI_KEY", txtPartNo);
        cmd.Parameters.AddWithValue("LOCATION", "HQ-TC");

        //rowread = cmd.ExecuteReader();
        //rowread.Read();
        //if (rowread.HasRows)
        //{
            //while (rowread.Read())
            //{
                StockQuan = (int)cmd.ExecuteScalar();
            //}
            NewAmount = StockQuan - txtAmount;

        //}
        //else
        //{
        //    MessageBox.Show("Error reading quantity. Check with Administrator", "Error", MessageBoxButton.OK, MessageBoxImage.Warning);
        //}
        return rowread;
    }

I commented out most things to just simplify where I'm going wrong and now the error is invalid cast on (int)cmd.ExecuteScalar();

What happens if you run the above code and there is no matching record (i.e. it returns 0 rows)? — mjwills
– mjwills, Commented Aug 31, 2017 at 11:15
The way you are executing SQL does work, but you may consider using a lightweight ORM like github.com/StackExchange/Dapper . It may make your life a little easier. — mjwills
– mjwills, Commented Aug 31, 2017 at 11:16

tchelidze · Accepted Answer · 2017-08-31 11:11:35Z

3

Try following

 StockQuan = Convert.ToInt32(cmd.ExecuteScalar());

answered Aug 31, 2017 at 11:11

tchelidze

8,3781 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lee Over a year ago

@Boneist I was but when doing so I was told I had to wait 10 minutes before marking it as the correct answer.

Collectives™ on Stack Overflow

SQL query result to variable?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related