I am developing a C# VS 2008 / SQL Server website application. I am a newbie to ASP.NET. I am getting the above error, however, on the last line of the following code. Can you give me advice on how to fix this? This compiles correctly, but I encounter this error after running it.
All that I am trying to do is to store the items from the second row of "dt" into string parameters. The first row is the header, so I don't want these values. The second row is the first row of values. My SQL stored procedure requires these values as strings. So I want to parse the second row of data and load into 2 string parameters. I added more of my code below.
DataTable dt;
Hashtable ht;
string[] SingleRow;
...
SqlConnection conn2 = new SqlConnection(connString);
SqlCommand cmd = conn2.CreateCommand();
cmd.CommandText = "dbo.AppendDataCT";
cmd.Connection = conn2;
SingleRow = (string[])dt.Rows[1].ItemArray;
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@" + ht[0], SingleRow[0]);
sqlParam.SqlDbType = SqlDbType.VarChar;
SqlParameter sqlParam2 = cmd.Parameters.AddWithValue("@" + ht[1], SingleRow[1]);
sqlParam2.SqlDbType = SqlDbType.DateTime;
My error:
System.InvalidCastException was caught
Message="Unable to cast object of type 'System.Object[]' to type 'System.String[]'."
Source="App_Code.g68pyuml"
StackTrace:
at ADONET_namespace.ADONET_methods.AppendDataCT(DataTable dt, Hashtable ht) in c:\Documents and Settings\Admin\My Documents\Visual Studio 2008\WebSites\Jerry\App_Code\ADONET methods.cs:line 88
InnerException:
Hmmm. You're trying to access properties of the DataTable dt, but it looks like you might be expecting that table to contain the results of the AppendDataCT query. It doesn't. Be careful with your row index accesses, too: Arrays in C# are 0-based, and dt.Rows[1] will retrieve the second row in the table.
That aside, review the documentation for DataRow.ItemArray. That method returns an array of objects, not an array of strings. Even if your row contains nothing but strings, you're still dealing with an array of objects, and you'll have to treat it that way. You can cast each individual item in the row to a string, if that's the proper datatype for that column:
foreach (string s in dt.Rows[1].ItemArray)
{
//...
}
EDIT: Ok, in response to your edit, I see what you're trying to do. There are many different ways to do this, and I'd particularly recommend that you move away from HashTables and towards generic equivalents like Dictionary - you'll save yourself much runtime casting grief. That said, here's the simplest adaption of your code:
DataRow dr = dt.Rows[1]; // second row
SqlParameter p1 = cmd.Parameters.AddWithValue((string)ht[0], (string)dr[0]);
SqlParameter p2 = ...
You don't need the leading "@"; ADO.NET will add that for you. The (string) casts will work as long as there's a string at key 0 (which is a fairly non-standard way to use hashtables - you'd usually have some kind of descriptive key), and if the first column in your datatable contains strings.
I recommend you look at typed datasets and generic collections. The lack of them here makes your code somewhat brittle.