Writing information from SQL Server to C#

Question

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.

You are executing an INSERT operation, there is nothing to read back — Steve, Jun 09 '15 at 20:49
To get the table definitions use `SELECT * FROM information_schema.tables` and that should give you the list of tables. If you need a full free standing query to get the table definitions refer to http://stackoverflow.com/questions/6215459/t-sql-query-to-show-table-definition — Matt Rowland, Jun 09 '15 at 20:53
@Steve there is a select statement after the Insert statement. Hard to see as it's cut off if you don't scroll. — Mark, Jun 09 '15 at 21:03
@Mark NO, that SELECT is used to feed costant values to the INSERT statement. It is not returned by the query. If you use ExecuteReader on an INSERT there is no column names returned hence the error — Steve, Jun 09 '15 at 21:12
@Steve would you recommend me put a select statement then before the insert or some other measure? — user4970927, Jun 09 '15 at 21:14
But we should be clear on what you attempting to do. Your query INSERTs data in the table. If you have data to retrieve then you execute just a SELECT without any INSERT — Steve, Jun 09 '15 at 21:16
@Steve sorry this is a little late, but it is working on SQL Management Studio, and it is working in the query window on VS, but it is still not working in the code. Is there like an inheritancy for using these words? — user4970927, Jun 10 '15 at 13:18
Nothing that I am aware of. Something different should be at play here. The FROM keyword and the SELECT syntax are the most stable standard from Sql beginning. From the little bit of your code I have no clue of what is the cause of the "Syntax Error". In particular if you have changed your code to follow my answer below — Steve, Jun 10 '15 at 14:09
@Steve I changed your code and it seems to be working now. The only thing I have left to ask is how do I only the first row from each column? — user4970927, Jun 10 '15 at 14:15
Sorry I am leaving now and have no time to answer. What do you mean for _first row from each column_? Perhaps first column from each row? Just get the column you want by its name and put it in your query text as first name after the SELECT — Steve, Jun 10 '15 at 14:24

Mark · Answer 1 · 2015-06-09T21:20:17.357

0

You don't have a select statement. Changing to add a select statement with names might help:

     @cmdText = @"DECLARE @FILE_DATE_PROCESSED DATETIME = '2015-12-31 19:32:45',
            @DATE_ENTERED DATETIME =getdate()
    INSERT FILE_DATE_PROCESSED(UID, FILE_DATE_PROCESSED, FILE_NAME, DATE_ENTERED) 
    SELECT newid(), @FILE_DATE_PROCESSED, 'myfilename.txt',@DATE_ENTERED
    SELECT @FILE_DATE_PROCESSED AS FILE_DATE_PROCESSED, @DATE_ENTERED as DATE_ENTERED"
 SqlCommand command = new SqlCommand(@cmdText)

edited Jun 09 '15 at 21:20

answered Jun 09 '15 at 21:01

Mark

2,926
3
28
31

Thank you. The problem is, I do not think AS is the correct kind of syntax. I say that because running the code I am getting an exception that says Incorrect syntax near the keyword 'AS' – user4970927 Jun 09 '15 at 21:08
Hmm works from me in SSMS. the AS should be optional - try removing – Mark Jun 09 '15 at 21:14
Sorry I misread you original SQL. I have updated my answer with something that may work better – Mark Jun 09 '15 at 21:22

Steve · Accepted Answer · 2015-06-09T21:34:50.637

0

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

edited Jun 09 '15 at 21:34

answered Jun 09 '15 at 21:22

Steve

213,761
22
232
286

It says that from isn't a correct syntax. And then when I get rid of it, it yells at me even more – user4970927 Jun 09 '15 at 21:32
The basic syntax for a SELECT query is `SELECT FROM `. So if you get a syntax error I think that there is a problem with your names. Try to put square brackets around the field name `[FILE_DATE_PROCESSED]` and the same table. Meanwhile I wish to make a little test
– Steve Jun 09 '15 at 21:37
No, there is no problem using the a column name with the exact name of the table, just confusion. Please post what is exactly the error and recheck your code for mispellings or other subtle typo – Steve Jun 09 '15 at 21:42
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Incorrect syntax near the keyword 'FROM'. – user4970927 Jun 09 '15 at 21:44
That is what I received as an error. And I rechecked my code and I still get the same error, even with the brackets – user4970927 Jun 09 '15 at 21:45
If you try to use Sql Server Management Studio and execute the query above did you get any error? Another idea is to change the query to `SELECT * FROM FILE_DATE_PROCESSED` – Steve Jun 09 '15 at 21:53

Writing information from SQL Server to C#

2 Answers2

Linked