CODEWITHSUNDEEP
c#sql-servertype-conversion
Michel Vanderbeke
Michel Vanderbeke

Reputation: 1

Display Date Column values from a SQL Server database table in a C# listbox or textbox

string sql = "Select * from tblContacten";
SqlCommand cmd = new SqlCommand(sql, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
    lstContacten.Items.Add(rdr.GetString(8).ToString());
}

The error message I get is:

"Cannot convert object of type System.DateTime to type System.String".

How can I display the content of a Date column in SQL Server into a c# textbox or listbox?

Upvotes: 0

Views: 78

Answers (2)

Saleel Ahsan K
Saleel Ahsan K

Reputation: 11

As correctly pointed out by Dan and Hogan, You are getting the error because of a data type mismatch.

SqlReader can provide you with the data in various formats, please refer to

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-5.0

for the full set of options available,

coming back to your question our requirement is to pull just Date column from the Sqlserver Database and display it in a textbox or list box

Ask for the correct type of data from SqlReader

Since our requirement is to read the Date, let's make use of SqlDataReader.GetDateTime()

rdr.GetDateTime(8); //if you prefer index

or

rdr.GetDateTime("last_update"); //if you prefer column name

Extract the required information

rdr.GetDateTime(8) will fetch us a DateTime Object since we are interested in the Date part of things lets extract the Date component of this instance using .Date property

our code will now become

rdr.GetDateTime(8).Date;

Convert to Required Format
Since we are looking to display it in some control like a text box or list item which is expecting a string, lets now convert extracted Date to a string using .ToString()

rdr.GetDateTime(8).Date.ToString(""); //gives time 12:00:00 AM with date as default

Format the Date as the format of choice

We can further format the Date using the various Date formats available

refer https://learn.microsoft.com/en-us/dotnet/api/system.datetime.date?view=net-5.0

For your question, we can use the Date Short String Format

rdr.GetDateTime(8).Date.ToString("d");//gives just the Date Part

Assign the value to a control

If textbox

txtBoxName.Text = rdr.GetDateTime(8).Date.ToString("d");

if listbox

lstContacten.Items.Add(rdr.GetDateTime(8).ToDate().ToString());

Upvotes: 1

Hogan
Hogan

Reputation: 70538

You need to ask for the correct type --

while (rdr.Read())
{
    string astring = lstContacten.Items.Add(rdr.GetDateTime(8).ToDate().ToString());
}

And as Dan points out in the comments -- how do you know what column you are getting you should use a column name.

while (rdr.Read())
{
    string astring = lstContacten.Items.Add(rdr.GetDateTime("last_update").ToDate().ToString());
}

Upvotes: 0

Related Questions