Invalid attempt to read when no data is present

Avuya Mxoli

15y4k

Hi,
I am working on a project and I am having trouble with this error(see subject line). This happens when I try to select a country from a combo box and what should happen is that when I do this there is a stored procedure that is called in my sql database which reads form a table that has data but then I get this error which suggests otherwise,any ideas?Thnks
Here is where the error occurs:
using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlCommand cmd = new SqlCommand("GetGameForCountry", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Country", match);
//game is a class in my program Game aGame;
try
{
conn.Open();
}
catch
{
}
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
reader.Read();
// error here>>> aGame = new Game(reader["ID"].ToString(), reader["Location"].ToString(), reader["DateTime"].ToString());
reader.Close();
}
return aGame;

27 Replies

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

Suthish Nair15y ago
refer the article : DataGridView - Insert, Update, Delete and Retrieve records using stored procedure
Prabhu Raja15y ago
can you show your stored procedure?
Avuya Mxoli15y ago
Tried that and here is what I get as an error:
An SqlParameter with ParameterName '@Country' is not contained by this SqlParameterCollection.
Prabhu Raja15y ago
Hi,

Please Try this.

             try
{
             SqlConnection conn = new SqlConnection(ConnString))
SqlCommand cmd = new SqlCommand("GetGameForCountry");
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
          cmd.Connection = con;
cmd.Parameters["@Country"].value = match.Trim();
SqlDataReader reader = cmd.ExecuteReader();
                while( reader.Read())
               {
                 aGame = new Game(reader["ID"].ToString(), reader["Location"].ToString(), reader["DateTime"].ToString());
                }
}
catch
{
}
return aGame;
Avuya Mxoli15y ago
In sql it executes perfectly but when I pass the same parameter in c# it doesnt even go into the while(Reader.Read())
Prabhu Raja15y ago
There is no problem with our .net Code.

execute stored procedure in Sql Management Studio for some parameter value to check, if it returns values or not, then use the same value in .net code as Parameter to that stored procedure.
Avuya Mxoli15y ago
I dd that and it returns the msgbox with null, I also inserted the messagebox in an else where we were checking if(reader.HasRows) and there too it returns null which suggests that it does not even read the data
Prabhu Raja15y ago
yes, for windows application, Databind() in not needed.

private void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{

Game aGame= bl.GetGameForCountry(Convert.ToString(cboCountry.SelectedValue)); //verify this line should return values or not by breakpoints

// Please check sGame is null or not, before used

if ( aGame== null)
dgvGame.DataSource = aGame;

else
MessageBox.Show("Object is null");
}
Avuya Mxoli15y ago
Error1'System.Windows.Forms.DataGridView' does not contain a definition for 'DataBind' and no extension method 'DataBind' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)C:\Users\Avuya\Documents\Avus things\2011 work\C#2\Project Sem2\Project\Project\GameForCountry.cs3221Project
It doesnt even autocomplete with databind,it has databindings instead
Prabhu Raja15y ago
its Valid one. See Microsoft Documentation

http://msdn.microsoft.com/en-us/library/fkx0cy6d.aspx
Avuya Mxoli15y ago
thanks,databind()is not a valid extension for a gridview
Prabhu Raja15y ago
Can you try with this one.

private void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
Game aGame= bl.GetGameForCountry(Convert.ToString(cboCountry.SelectedValue));
dgvGame.DataSource = aGame; // Its Ok. You Can Use Game Class Object as DataSource To GridView
dgvGame.DataBind();
}
Avuya Mxoli15y ago
Thanks Raja, I initially used a gridview but when it displayed nothing I used the listbox just to look for the error. I still have my gridview error though can you try figure out why it displays nothing?thanks
Prabhu Raja15y ago
Hi,

private void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
Game aGame= bl.GetGameForCountry(Convert.ToString(cboCountry.SelectedValue));
dgvGame.DataSource = aGame;
listBox1.Items.Add(aGame); // Here You Added Game Class Object to Your List. I think Thats the Problem
}

But i suggest you to try like

private void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
Game aGame= bl.GetGameForCountry(Convert.ToString(cboCountry.SelectedValue));
dgvGame.DataSource = aGame; // Its Ok. You Can Use Game Class Object as DataSource To GridView

// Consider this, we have initialized Game Class Object as
// aGame = new Game(reader["ID"].ToString(), reader["Location"].ToString(), reader["DateTime"].ToString());
// Here we passing ID, Location and DateTime to Game Class Constructor, And Constructor will initialize Local Variable with these Values. So, you can //use those variables like this.

listBox1.Items.Add(aGame.Location); //If you want to Display Location in listBox, then use this one.
listBox1.Items.Add(aGame.ID); //If you want to Display Location in listBox, then use this one.
listBox1.Items.Add(aGame.DateTime); //If you want to Display Location in listBox, then use this one.
}
Avuya Mxoli15y ago
Thanks for all the input guys prahbu the problem was with my stored procedure syntax although the db ddnt complain now it returns the info on the sql side but now new problem. when I select the combobox I want to show the results on the gridview and it returns nothing sp i tried a listbox and it returns: Project.game"this should make sense to those who hav been following my posts. here is the selectedIndexChanged code:private void cboCountry_SelectedIndexChanged(object sender, EventArgs e){ Game aGame= bl.GetGameForCountry(Convert.ToString(cboCountry.SelectedValue)); dgvGame.DataSource = aGame; listBox1.Items.Add(aGame); }Thanks @Suthish I will say when the thread has been resolved hey
Suthish Nair15y ago
thread resolved?
Prabhu Raja15y ago
I tried the same in my computer, it works fine.

I have created a Table :

Table Name : Game

Columns :

ID nvarchar(50)
Location nvarchar(50)
DateTime SmallDateTime.

After that i have Inserted Values to Table. Currently table contains

ID Location DateTime
Ind India 1/1/2011
11Ind Cape Town 1/1/2011

And My procedure is,

Alter Procedure GameCheck
@country nvarchar(250)
as
Select * from Game Where ID Like '%'+@country Or ID LIke @country+'%'
Return

And Executed As,

DECLARE @RC int
DECLARE @country nvarchar(250)

-- TODO: Set parameter values here.

EXECUTE @RC = [Dem].[dbo].[GameCheck]
N'Ind'
GO

The Output i got,

ID Location DateTime
Ind India 1/1/2011
11Ind Cape Town 1/1/2011

Everything is correct. I think in your Table, ID Column may not contain France String. Try to Pass Only N'rance'.
Javeed M Shaikh15y ago
Please try this and let us know, also trim spaces from the values before sending to the procedure:

SELECT ID,Location,[DateTime] FROM Game WHERE ID LIKE @Country + '%' OR ID LIKE '%' + @Country;

If you are passing a Unicode string i.e. NCHAR,NVARCHAR or NTEXT as compared to CHAR,VARCHAR or TEXT you may see the T-SQL code passes string prefix with 'N' (N actually stands for National language character set)

Please do not forget to mark "Accepted Answer".
Avuya Mxoli15y ago
Prabhu I tried that and it returns 0,can you check my procedure though coz I think theres something wrong wth it:SELECT ID,Location,[DateTime] FROM Game WHERE ID LIKE ('@Country %') OR ID LIKE ('%@Country'); are my wildcards correct coz wen I execute it returns this:@return_value = [dbo].[GetGameForCountry] @Country = N'France' SELECT 'Return Value' = @return_value. I dont knw where the N comes from.Thanks
Pravin More15y ago
Hi Ncedisa,
                try this code......

String ID="",Location ="",DateTime ="";
    while( reader.Read())
        {
             if(reader.IsDBNull(0))
                  ID="";
             else
           ID = reader[0].ToString();

             if(reader.IsDBNull(1))
                  Location ="";
             else
           Location = reader[1].ToString();
           if(reader.IsDBNull(2))
                  DateTime ="";
             else
           DateTime = reader[2].ToString();

            aGame = new Game(ID, Location, DataTime);
        }

hope it will help you.
Thanx,
Pravin.
Prabhu Raja15y ago
I expect this. Can you execute your ("GetGameForCountry") Procedure in SQL management studio with the same Parameter Value to check whether the procedure returns any rows or not.
Avuya Mxoli15y ago
reader.hasRows returns false...
Prabhu Raja15y ago
Hi, Try this one.

if( reader.HasRows)
{

while( reader.Read())
{
String ID = reader["ID"].ToString(); // or String ID = reader[0].ToString();
String Location = reader["Location"].ToString(); // or String Location = reader[1].ToString();
String DateTime = reader["DateTime"].ToString(); // or String DateTime = reader[2].ToString();

// put a break point for below statement, and conform that all above variables having values or not
aGame = new Game(ID, Location, DataTime);
}

}
Prabhu Raja15y ago
Because, the Reader may be null. So, the while loop is skipped . Put a break point in while loop and check the reader has values or not. Also You may include a if () Condition like,

if( reader.HasRows)
{

   while( reader.Read())
               {
                 aGame = new Game(reader["ID"].ToString(), reader["Location"].ToString(), reader["DateTime"].ToString());
                }

}
Avuya Mxoli15y ago
Thanks Prahbu actually the problem was with that return aGame problem I had earlier which u fixed by setting game aGame = null. I did however place the reader contents in a while like u suggesting but now it doesnt even go inside the loop. When I place a break point it just skips and goes to reader.close() and it does not give an error but when I read on the bottom where it shows the stack it writes next to ID,Location and DateTime:threw an exception of type 'System.InvalidOperationException'object {System.InvalidOperationException} which is the one that I got as the subject of this post
Javeed M Shaikh15y ago
Try placing assignment to Game object in

if (Reader.Read())
{
......
}

Please do not forget to mark "Accepted Answer".
Prabhu Raja15y ago
Hi, try this.

using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlCommand cmd = new SqlCommand("GetGameForCountry", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Country", match);
try
{
conn.Open();
}
catch
{
}
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
while( reader.Read())
               {
                 aGame = new Game(reader["ID"].ToString(), reader["Location"].ToString(), reader["DateTime"].ToString());
                }
reader.Close();
}
return aGame;