-
February 4th, 2009, 09:27 AM
#1
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 . . .
-
February 4th, 2009, 10:13 AM
#2
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 10:16 AM.
-
February 4th, 2009, 10:39 AM
#3
Re: I can create a SQL connection, but can't run any queries
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.
-
February 4th, 2009, 11:22 AM
#4
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?
-
February 4th, 2009, 05:00 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|