I have a table created in ASP.net and I want to populate the table with information from the database once the page has been loaded. I'm getting an error that the specified cast is not valid. What am I doing wrong? Heres my code
public string getData() { string htmlStr = ""; SqlConnection conn = new SqlConnection(connString); SqlCommand command = conn.CreateCommand(); command.CommandText = "SELECT * from INFO"; conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { DateTime Date = reader.GetDateTime(0); DateTime Time = reader.GetDateTime(1); htmlStr += "<tr><td>" + Date + "</td><td>" + Time + "</td></tr>"; } conn.Close(); return htmlStr; } <table> <caption>INFO</caption> <tr> <td> Date </td> <td> Time </td> </tr> <%=getData()%> </table> This is my error:
It is throwing the exception on this line from the above code:
DateTime Date = reader.GetDateTime(0); 3 Answers
From your comment:
this line
DateTime Date = reader.GetDateTime(0);was throwing the exception
The first column is not a valid DateTime. Most likely, you have multiple columns in your table, and you're retrieving them all by running this query:
SELECT * from INFO Replace it with a query that retrieves only the two columns you're interested in:
SELECT YOUR_DATE_COLUMN, YOUR_TIME_COLUMN from INFO Then try reading the values again:
var Date = reader.GetDateTime(0); var Time = reader.GetTimeSpan(1); // equivalent to time(7) from your database Or:
var Date = Convert.ToDateTime(reader["YOUR_DATE_COLUMN"]); var Time = (TimeSpan)reader["YOUR_TIME_COLUMN"]; htmlStr is string then You need to Date and Time variables to string
while (reader.Read()) { DateTime Date = reader.GetDateTime(0); DateTime Time = reader.GetDateTime(1); htmlStr += "<tr><td>" + Date.ToString() + "</td><td>" + Time.ToString() + "</td></tr>"; } Try this:
public void LoadData() { SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Stocks;Integrated Security=True;Pooling=False"); SqlDataAdapter sda = new SqlDataAdapter("Select * From [Stocks].[dbo].[product]", con); DataTable dt = new DataTable(); sda.Fill(dt); DataGridView1.Rows.Clear(); foreach (DataRow item in dt.Rows) { int n = DataGridView1.Rows.Add(); DataGridView1.Rows[n].Cells[0].Value = item["ProductCode"].ToString(); DataGridView1.Rows[n].Cells[1].Value = item["Productname"].ToString(); DataGridView1.Rows[n].Cells[2].Value = item["qty"].ToString(); if ((bool)item["productstatus"]) { DataGridView1.Rows[n].Cells[3].Value = "Active"; } else { DataGridView1.Rows[n].Cells[3].Value = "Deactive"; } 
