I can create a SQL connection, but can't run any queries

Hello,

I'm attempting to run a sql query from my console app, but my problem is that I can create a connection, but cannot run any queries, can you advise.

Here's my code to add a connection

Code:

---

using System;
using System.Data;
using System.Data.SqlClient;

namespace Connection_Sql
{
    class Connection_Sql
    {
        static void Main()
        {
           
            SqlConnection thisConnection = new SqlConnection(
              @"Server=********;" +
              "initial catalog=***;" +
              "uid=***;" +
              "password=********;");

            // create SqlCommand
            SqlCommand thisCommand = new SqlCommand();
            Console.WriteLine("Command created.");

            try
            {
                // open connection
                thisConnection.Open();
              Console.WriteLine("Connection opened.");
               

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                thisConnection.Close();
                Console.WriteLine("Connection Closed.");
            }
        }

    }
}

---

This produces the code below, showing that I created the connection.

Code:

---

Command created.
Connection Opened
Connection Closed.
Press any key to continue . . .

---

But when I try to add a sql query, doing the following code:

Code:

---

ing System;
using System.Data;
using System.Data.SqlClient;

namespace Connection_Sql
{
    class Connection_Sql
    {
        static void Main()
        {
            // create SqlConnection object
         

            SqlConnection thisConnection = new SqlConnection(
              @"Server=*******;" +
              "initial catalog=***;" +
              "uid=***;" +
              "password=********;");

            // create SqlCommand
            SqlCommand thisCommand = new SqlCommand("SELECT order_num, return_num from returns where order_num='123456'", thisConnection);
            Console.WriteLine("Command created.");

            try
            {
                // open connection
                thisConnection.Open();
                SqlDataReader thisReader = null;
               
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    Console.WriteLine(thisReader["Column1"].ToString());
                    Console.WriteLine(thisReader["Column2"].ToString());
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                thisConnection.Close();
                Console.WriteLine("Connection Closed.");
            }
        }

    }
}

---

I get the following error, regardless of the query I add. My queries work, so I'm not sure why I can create a connection, but not run any queries???

Code:

---

Command created.

System.Data.SqlClient.SqlException: Invalid object name '**********'.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
  at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
  at System.Data.SqlClient.SqlDataReader.get_MetaData()
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run
Behavior runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe
havior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
 result)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
ior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S
tring method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader()
  at Connection_Sql.Connection_Sql.Main() in C:\Documents and Settings\Visual Studio 2005\Projects\SQLCONNECTION\SQLCONNECTION\Program.cs:l
ine 30

Connection Closed.
Press any key to continue . . .

---

Open the connection before creating the SqlCommand.

Also the output of the SQL select statement doesn't contain "Column1" or "Column2" in its output : its columns (as you specified) are "order_num" and "return_num".

So the code should read :

Code:

---

Console.WriteLine(thisReader["order_num"].ToString());
Console.WriteLine(thisReader["return_num"].ToString());

---

Darwen.

Quote:

---

Originally Posted by darwen

Open the connection before creating the SqlCommand.

Also the output of the SQL select statement doesn't contain "Column1" or "Column2" in its output : its columns (as you specified) are "order_num" and "return_num".

So the code should read :

Code:

---

Console.WriteLine(thisReader["order_num"].ToString());
Console.WriteLine(thisReader["return_num"].ToString());

---

Darwen.

---

thanks, but that did not work, i got the same error.

Quote:

---

System.Data.SqlClient.SqlException: Invalid object name '**********'.

---

Since all of this is **** out, it might be tough, but do you have a table called "returns" in your DB that you are setting in your Initial Catalog? Can you open SQL Server Management Studio, and execute the SQL Statement from there? What do you get when you do?

How is order_num defined ? is it a string ?
Because if it is number you must not set the value between apostrophes you need to write

Code:

---

"SELECT order_num, return_num from returns where order_num=123456 "

---

:wave: