Invalid attempt to read when no data is present

Question

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 otherwiseany 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;

Suthish Nair · Answer

refer the article : DataGridView - Insert Update Delete and Retrieve records using stored procedure

Prabhu Raja · Answer

can you show your stored procedure?

Avuya Mxoli · Answer

Tried that and here is what I get as an error: An SqlParameter with ParameterName '@Country' is not contained by this SqlParameterCollection.

Prabhu Raja · Answer

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 Mxoli · Answer

In sql it executes perfectly but when I pass the same parameter in c# it doesnt even go into the while(Reader.Read())

Prabhu Raja · Answer

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 Mxoli · Answer

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 Raja · Answer

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 Mxoli · Answer

Error 1 '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.cs 32 21 Project It doesnt even autocomplete with databindit has databindings instead

Prabhu Raja · Answer

its Valid one. See Microsoft Documentation http://msdn.microsoft.com/en-us/library/fkx0cy6d.aspx

Avuya Mxoli · Answer

thanksdatabind()is not a valid extension for a gridview

Prabhu Raja · Answer

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 Mxoli · Answer

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 Raja · Answer

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 Mxoli · Answer

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.gamethis 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 Nair · Answer

thread resolved?

Prabhu Raja · Answer

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 Shaikh · Answer

Please try this and let us know also trim spaces from the values before sending to the procedure: SELECT IDLocation[DateTime] FROM Game WHERE ID LIKE @Country + '%' OR ID LIKE '%' + @Country; If you are passing a Unicode string i.e. NCHARNVARCHAR or NTEXT as compared to CHARVARCHAR 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 Mxoli · Answer

Prabhu I tried that and it returns 0can you check my procedure though coz I think theres something wrong wth it:SELECT IDLocation[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 More · Answer

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 Raja · Answer

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 Mxoli · Answer

reader.hasRows returns false...

Prabhu Raja · Answer

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 Raja · Answer

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 Mxoli · Answer

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 IDLocation 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 Shaikh · Answer

Try placing assignment to Game object in if (Reader.Read()) { ...... } Please do not forget to mark Accepted Answer.

Prabhu Raja · Answer

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;