0
int value2;
using (SqlConnection dataConnection = new SqlConnection(@"Data Source=MANNAN-PC\SQLEXPRESS;Initial Catalog=WareHouse;Integrated Security=True;"))
using (SqlCommand dataCommand = new SqlCommand("Select Sum(I_Quantity) from Itemswork where I_Detail='" + maskedTextBox2.Text + "' and Order_No ='" + maskedTextBox1.Text + "'", dataConnection))
{
      dataConnection.Open();
      value2 = Convert.ToInt32(dataCommand.ExecuteScalar());
}

It shows the Error of DBnull because the column from which I'm getting the value is already int. I want to know what is the other way to get that value in variable's value2 definition.

Regent
  • 5,142
  • 3
  • 21
  • 35
user3872542
  • 1
  • Welcome to Stack Overflow. You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Jul 24 '14 at 10:46
  • Remember that ExecuteScalar could return NULL if the query expression cannot find any record matching your conditions. – Steve Jul 24 '14 at 10:50
  • What is the error _exactly_? What is the return value of `dataCommand.ExecuteScalar()`? – Soner Gönül Jul 24 '14 at 10:50
  • Just for testing if your conditions match anything change the SUM(I_Quantity) in COUNT(*) and tell us what is the value2 value. – Steve Jul 24 '14 at 10:52

2 Answers2

0

Check the remarks section here:SqlCommand.ExecuteScalar Method 

value2 = (Int32)dataCommand.ExecuteScalar();
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
0

As @Soner states you should be using parameterized queries to reduce the issues relating to SQL Injection etc. One of the problems you're actually experiencing is that the result being returned is NULL.

The SUM where no columns are returned is not 0, what you could do is change your output to check whether the returned value IsDbNull before trying to parse it into an integer. (See Unable to cast object of type 'System.DBNull' to type 'System.String` - although it's parsing to string the logic is the same)

ExecuteScalar will return

  • null if there is no result set
  • otherwise the first column of the first row of the resultset, which may be DBNull.

(Example modified from linked question and not syntax checked)

var tempObject = dataCommand.ExecuteScalar();
value2 = (tempObject == null || Convert.IsDBNull(tempObject) ? (int) tempObject: 0;
Community
  • 1
  • 1
talegna
  • 2,407
  • 2
  • 19
  • 22