"The SelectCommand property has not been initialized before calling 'Fill'.

Question

Hi,

I am still struggling with the problem I posted earlier this week about filling my gridview. The same procedure works fine when I execute it in sql but in c# it doesnt fill the grid. I get ths exception error when I do this:
DataTable dtGames = new DataTable();
try
{

SqlCommand cmd = new SqlCommand("GetGameForCountry '" + game + "'", conn);
dbAdapter = new SqlDataAdapter(dbCommand);
dbAdapter.Fill(dtGames);
}
catch(SqlException sqlex)
{
MessageBox.Show("SQL exception: " + sqlex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return dtGames;
I then call this in the combobox text changed event like this:

try
{
dgvGame.DataSource = bl.getGameForCountry(cboCountry.SelectedValue.ToString());

}
catch (SqlException sqlex)
{
MessageBox.Show("SQL exception: " + sqlex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

Any ideas why I am getting this error?Thanks

Avuya Mxoli · Answer

Hi all, my problem has been resolved. As i was filling the combobox I was making use of the .selected value which was actually the value member of the text box. I though I was using the display member all along so to change to value member I changed to use the .text instead. Thanks for all your input oh and Vulpes I changed my name, when I registered I thought they wanted my first name as it is in my ID so I was playing around with my profile yesterday and noticed that I can actually change my name to the name I'm known by

Vulpes · Answer

Ncedisa - you've changed your name! As regards the second problem, I can't see any obvious reason why it shouldn't work:-/ The parameter is OK - it's nvarchar rather than varchar but that shouldn't matter. I take it that 'Country 1' and 'Country 2' are columns of the Game table. Just as a matter of interest, does it work if you hard-code a couple of country names and then set the @Country parameter to one of these names?

Sam Hobbs · Answer

But the error message " The SelectCommand property has not been initialized before calling 'Fill' " is gone, correct; so the question is answered. The reason it is still not working is a different problem. Sorry about my comment about GetGameForCountry; I guess you should ignore it.

Avuya Mxoli · Answer

USE [Rugby2011] GO /****** Object: StoredProcedure [dbo].[GetGameForCountry] Script Date: 10/13/2011 23:22:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[GetGameForCountry] @Country nvarchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * FROM Game WHERE @Country = [Country 1] OR @Country =[Country 2] --WHERE ID LIKE '%'+@country OR ID LIke @country+'%'; END

Vulpes · Answer

I think the reason why you were getting the original error was due to the lack of this line: cmd.CommandType = CommandType.StoredProcedure; The SQLDataAdapter was therefore looking for a SELECT command and not finding one. If the stored procedure works OK from SQl Server, then the only other thing I can think of is that you're not calling it correctly from C# and it's not therefore returning any rows. So, as Suthish said earlier, I think you'll have to post the stored procedure itself so we can take a look at it.

Avuya Mxoli · Answer

Sam thank you very much for your input, I tried Shutish's code and it doesnt help the problem so by marking it as the correct answer i will be lying.And wat do u mean I dnt show what GetGameFprCountry is?I posted the procedure...or does ur question mean something else?I will look at your article thanks

Sam Hobbs · Answer

Note that your original code does not show the SelectCommand property being set, so that is totally consistent with the error message. Suthish's code is the only code I see that sets the SelectCommand property, so I think that answers your question. You should accept Suthish's code as the answer, then create a new thread for a new question. Also note that you don't show what GetGameForCountry is. If you need help with it, or use of it, then you should show what it is. Also, it it were me, I would use a TableAdpater or Entity Framework and then you don't have to waste time with details. It is good to know about the details, but there is so much to learn that it is usually better to not dwell on details that you don't have to do. Try my Database Table Update in a DataGridView without Writing Code ; it shows use of a TableAdpater. The Entity Framework is newer technology and there are also articles about it.

Avuya Mxoli · Answer

Thanks Vulpes the dbCommand was a typo I made earlier but fixed it. Tried that and its back to returning just the column headings

Vulpes · Answer

It looks to me as though you are passing a parameter to the stored procedure and, if that's the case, I'm not sure you can do it by simply including it in the command string. I'd try instead: SqlCommand cmd = new SqlCommand("GetGameForCountry", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Country", SqlDbType.VarChar); cmd.Parameters["@Country"].Value = game; dbAdapter = new SqlDataAdapter(cmd); // not sure where dbCommand came from before? dbAdapter.Fill(ds);

Avuya Mxoli · Answer

@Vulpes thanks for that but it actually came with a new error whicjh doesnt make any sense and now it doesnt even display the column headings. the errors say something like:could not find stored procedure "GetGameForCountry" and cannot find table 0...I don't see why it will say this after I have been getting the columns from the same procedure that it claims it cannot find. @Nair I thinki i already posted those and yes im sure it returns them because I have been testing it and it comes with results

Suthish Nair · Answer

Are you sure the sql statement returing rows.. can you post/upload the sql script and whole code logic for fetching the records.

Avuya Mxoli · Answer

Yes Nair and I still get an empty grid with just column headings

Vulpes · Answer

Ncedisa, I've just noticed that you've forgotten to set the SqlCommand's CommandType property to stored procedure: SqlCommand cmd = new SqlCommand("GetGameForCountry '" + game + "'", conn); cmd.CommandType = CommandType.StoredProcedure; dbAdapter = new SqlDataAdapter(dbCommand); dbAdapter.Fill(ds); Any sign of any data coming across now?

Suthish Nair · Answer

Ncedisa, did you tried what I suggested?

Avuya Mxoli · Answer

No Vulpes it didnt bring back anything.Empty grid

Vulpes · Answer

Ncedisa, When you said in your opening post that it doesn't fill the grid, did you in fact get one row of data because that's what I'd expect when you call SqlDataAdapter.Fill() with a DataTable argument? Here's an extract from the MSDN documentation: "The overload of Fill that takes DataTable as a parameter only obtains the first result. Use an overload of Fill that takes DataSet as a parameter to obtain multiple results."

Avuya Mxoli · Answer

uhm Pravin the exact error message is in the post subjuct line,here is the procedure: @Country nvarchar(50) SELECT * FROM Game WHERE @Country = [Country 1] OR @Country =[Country 2]

Avuya Mxoli · Answer

Thanks Vulpes,tried that and now it brings back only the table columns no data inside

Vulpes · Answer

Instead of trying to fill a DataTable directly, I'd try filling a DataSet and then get the DataTable from its Tables collection: DataSet ds = new DataSet(); try { SqlCommand cmd = new SqlCommand("GetGameForCountry '" + game + "'", conn); dbAdapter = new SqlDataAdapter(dbCommand); dbAdapter.Fill(ds); } catch(SqlException sqlex) { MessageBox.Show("SQL exception: " + sqlex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); } return ds.Tables[0]; The last line assumes that there's only one table being returned by the query or, if there are multiple tables, it's the first one you want. You can also use the name of the table if you know it: return ds.Tables["games"]; // or whatever it's called

Suthish Nair · Answer

Am not sure about this object (dbCommand). Always use Parameterized Queries.. SqlConnection conn = new SqlConnection(connection string); SqlDataAdapter dbAdapter = new SqlDataAdapter(); dbAdapter.SelectCommand = new SqlCommand(GetGameForCountry '" + game + "'", conn); dbAdapter.Fill(dataset);

Pravin More · Answer

Hi Ncedisa, can you paste exact error message and complite create sproc script here? Thanx, Pravin.