Problem pulling value from empty table

G F

Hi,

So I need to pull a few values out of one row in a table, but the code I have skips a crucial part of the program (where the values are added together with other variables) if the table is empty.

Here is the code I'm working with:

string newIDtemp;

newIDtemp = txtBox_TD_Current_ID.Text;

string constring = "datasource=localhost;port=3306;username=root;pwd=;";

string Query = "SELECT * FROM bbb.approvaltotal WHERE TDId= '" + newIDtemp + "' ;";

MySqlConnection conDataBase3 = new MySqlConnection(constring);

MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase3);

MySqlDataReader myReader;

try

{

MessageBox.Show(tdapprovalcurrentjurfee.ToString());

MessageBox.Show(tdapprovalcurrentpnfee.ToString());

MessageBox.Show(tdapprovalcurrenttotal.ToString());

conDataBase3.Open();

myReader = cmdDataBase.ExecuteReader();

while (myReader.Read())

{

string approvaltotalid = myReader.GetString("Id");

string approvaltotaltdid = myReader.GetString("TDId");

string approvalpermitnowtotal = myReader.GetString("PermitNowTotal");

string approvaljurisdictiontotal = myReader.GetString("JurisdictionTotal");

Basically, if the table returns Null, the While(myReader.Read()) does not execute.

I just need to pull the values out of the table, add some decimals to them, and then put them back in to the same spots. Like an update but with addition.

Is there a better way than using a try-while statement? The reason i'm using it is because its working for other parts of my program, but wasn't aware the while skips if the reader is empty.

I've read on other sites that it's better to use Execute Scalar, if it's just one row, but I'm having a hard time finding a good example that fits my situation.

Any help would be appreciated!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishvjit Shinde6y ago
Hi

Try to check with if condition
Like
if(myReader.Read())
{
While(Condition)
{
.....
}
}
Rajanikant Hawaldar6y ago
OK then you add this row count check condition to avoid runtime exception if (ds.Tables[0].Rows.Count > 0) { // in this condition you can access row index 0 Console.WriteLine("Record 1: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[0]["Id"]); } Like add condition to wherever your access rows
G F6y ago
hi, I adapted my code, but exact same problem except I now get an error:

System.IndexOutOfRangeException: 'There is no row at position 0.'
I've highlighted the line in yellow, that produced the error.
MySqlConnection cn = new MySqlConnection();
DataSet CustomersDataSet = new DataSet();
MySqlDataAdapter da;
MySqlCommandBuilder cmdBuilder;
cn.ConnectionString = "datasource=localhost;port=3306;username=root;pwd=;";
cn.Open();
da = new MySqlDataAdapter("SELECT * FROM bds.approvaltotal WHERE TDId= '" + newIDtemp + "' ;", cn);
cmdBuilder = new MySqlCommandBuilder(da);
da.Fill(CustomersDataSet, "ApprovalTotals");
//Write out the value in the approval field before updating the data using the DataSet.
Console.WriteLine("Record 1: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[0]["Id"]);
Console.WriteLine("Record 2: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[1]["TDId"]);
Console.WriteLine("Record 3: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[2]["PermitTotal"]);
Console.WriteLine("Record 4: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[3]["JTotal"]);
Console.WriteLine("Record 5: " + CustomersDataSet.Tables["ApprovalTotals"].Rows[4]["ATotal"]);
Console.WriteLine("Approval total read successfully");
cn.Close();
Rajanikant Hawaldar6y ago
Instead DataReader use DataAdapter, check below sample
using System.Data;
using System.Data.SqlClient;
using System;
namespace Q308507 {

class Class1
{
  static void Main(string[] args)
  {

    SqlConnection cn = new SqlConnection();
    DataSet CustomersDataSet = new DataSet();
    SqlDataAdapter da;
    SqlCommandBuilder cmdBuilder;

    //Set the connection string of the SqlConnection object to connect
    //to the SQL Server database in which you created the sample
    //table.
    cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";
    cn.Open();

    //Initialize the SqlDataAdapter object by specifying a Select command
    //that retrieves data from the sample table.
    da = new SqlDataAdapter("select * from CustTest order by CustId", cn);

    //Initialize the SqlCommandBuilder object to automatically generate and initialize
    //the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
    cmdBuilder = new SqlCommandBuilder(da);

    //Populate the DataSet by running the Fill method of the SqlDataAdapter.
    da.Fill(CustomersDataSet, "Customers");

    //Display the Update, Insert, and Delete commands that were automatically generated
    //by the SqlCommandBuilder object.
    Console.WriteLine("Update command Generated by the Command Builder : ");
    Console.WriteLine("==================================================");
    Console.WriteLine(cmdBuilder.GetUpdateCommand().CommandText);
    Console.WriteLine("         ");

    Console.WriteLine("Insert command Generated by the Command Builder : ");
    Console.WriteLine("==================================================");
    Console.WriteLine(cmdBuilder.GetInsertCommand().CommandText);
    Console.WriteLine("         ");

    Console.WriteLine("Delete command Generated by the Command Builder : ");
    Console.WriteLine("==================================================");
    Console.WriteLine(cmdBuilder.GetDeleteCommand().CommandText);
Console.WriteLine("         ");

    //Write out the value in the CustName field before updating the data using the DataSet.
    Console.WriteLine("Customer Name before Update : " + CustomersDataSet.Tables["Customers"].Rows[0]["CustName"]);

    //Modify the value of the CustName field.
    CustomersDataSet.Tables["Customers"].Rows[0]["CustName"] = "Jack";

    //Post the data modification to the database.
    da.Update(CustomersDataSet, "Customers");

    Console.WriteLine("Customer Name updated successfully");

    //Close the database connection.
    cn.Close();

    //Pause
    Console.ReadLine();
   }
}
}