Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
894 views
in Technique[技术] by (71.8m points)

Writing information from SQL Server to C#

Below I have a piece of code that should be writing out columns from my database, however when I try to execute, it gives me an exception that says it can't read a column with no values, but its wrong because it should contain dates in the columns that I want information to come out of.

Here is the table definition:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME           TABLE_TYPE
PATRICK_DEV   dbo          FILE_DATE_PROCESSED  BASE TABLE

Here is the code:

try
{
    SqlConnection connect = new SqlConnection("Server=OMADB01;Database=PATRICK_DEV;Trusted_Connection=True;");
    connect.Open();

    SqlCommand command = new SqlCommand("INSERT FILE_DATE_PROCESSED(UID, FILE_DATE_PROCESSED, FILE_NAME, DATE_ENTERED) SELECT newid(), '2015-12-31 19:32:45', 'myfilename.txt', getdate()", connect);

    SqlDataReader reader = null;
    reader = command.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine(reader["FILE_DATE_PROCESSED"].ToString());
        Console.WriteLine(reader["DATE_ENTERED"].ToString());
    }

    connect.Close();
}
catch (Exception e)
{
   Console.WriteLine(e.ToString());
}

This is what I am dealing with right now: I have a database in SQL that looks like this:

UID FILE_DATE_PROCESSED FILE_NAME DATE_ENTERED

In this database, every time I add a file, column 2 should contain the last time I entered the file, and column 4 should contain the time I am entering the file now. This is the result that I am looking for:

UID     FILE_DATE_PROCESSED FILE_NAME      DATE_ENTERED
random  random date         the file name  the current date
number 

If there is a different way of solving this problem than what I have posted please let me know I will very much appreciate it.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Your query executes an INSERT (IE. Adds data to your table) It doesn't retrive any record. The INSERT statement in T-SQL could use a SELECT subquery to feed the values that are requested by the columns listed after the INSERT.

So your query add a new record every time you run it, but there is no column returned from that query and using the reader indexer on a non existant column produces the mentioned error.

If you just want to read values then you should change your query to

try
{
    using(SqlConnection connect = new SqlConnection(....))
    using(SqlCommand command = new SqlCommand(
        @"SELECT FILE_DATE_PROCESSED, DATE_ENTERED FROM FILE_DATE_PROCESSED", connect))
    {
        connect.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
                 Console.WriteLine(reader["FILE_DATE_PROCESSED"].ToString());
                 Console.WriteLine(reader["DATE_ENTERED"].ToString());
           }
        }
   }
}
catch (Exception e)
{
   Console.WriteLine(e.ToString());
}

I suggest also to change the column name FILE_DATE_PROCESSED or the table name because having two objects with the same name could be an endless source of confusion and an excellent error maker


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...