I can create a SQL connection, but can't run any queries
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Jul 2008
    Posts
    23

    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 . . .

  2. #2
    Join Date
    Jan 2002
    Location
    Scaro, UK
    Posts
    5,940

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

    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.
    Last edited by darwen; February 4th, 2009 at 09:16 AM.
    www.pinvoker.com - PInvoker - the .NET PInvoke Interface Exporter for C++ Dlls.

  3. #3
    Join Date
    Jul 2008
    Posts
    23

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

    Quote Originally Posted by darwen View Post
    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.

  4. #4
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

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

    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?

  5. #5
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,537

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

    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 "
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center