Function for single SQL command

Question

I'm trying to write a general function for running SQL commands that only bring back one result.

I'm getting the following error:

Exception Details: System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.

Source Error: 

Line 130:
Line 131:            SQLCommand.CommandText = SQL;
Line 132:            String myResult = (String)SQLCommand.ExecuteScalar();
Line 133:
Line 134:            return myResult;

Source File: c:\Development\pros\Functions.cs    Line: 132

The code is:

public static string SingleSQL(string SQL)
{

    SqlConnection SQLCON = new SqlConnection(ConfigurationManager.ConnectionStrings["PIDDBConnectionString"].ToString());

    SQLCON.Open();
    SqlCommand SQLCommand = new SqlCommand();
    SQLCommand.CommandType = CommandType.Text;

    SQLCommand.CommandText = SQL;
    String myResult = (String)SQLCommand.ExecuteScalar();

    return myResult;

}

Kami · Accepted Answer · 2014-01-15 17:08:11Z

3

You do not assign the connection to the SqlCommand object.

Try something like

public static string SingleSQL(string SQL)
{

    SqlConnection SQLCON = new SqlConnection(ConfigurationManager.ConnectionStrings["PIDDBConnectionString"].ToString());

    SqlCommand SQLCommand = new SqlCommand();
    SQLCommand.Connection = SQLCON;
    SQLCommand.CommandType = CommandType.Text;

    SQLCommand.CommandText = SQL;

    try
    {
       SQLCON.Open();
       return (String)SQLCommand.ExecuteScalar();
    } finally {
       if (SQLCON.State != ConnectionState.Closed) SQLCON.Close();
    }
}

You should be able to change the function return type to object. The calling function is then able to convert as required.

edited Jan 15, 2014 at 17:08

answered Jan 15, 2014 at 16:55

Kami

19.5k4 gold badges53 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MissCoder87 Over a year ago

Thanks for the help, that gives: Error 5 The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string)' has some invalid arguments C:\Development\foos\Functions.cs 127 37 PIDs Error 6 Argument 1: cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string' C:\Development\fooss\Functions.cs 127 52 PIDs Error 7 The name 'myResult' does not exist in the current context C:\Development\foos\Functions.cs 142 20 PIDs

Kami Over a year ago

@TomBeech I have edited the code, try again. I incorrectly used the connection string as the first parameter. Writing this from memory without test :)

Dylan Brams Over a year ago

You don't need to open the connection before assigning it to the command? I have this feeling that's your problem.

Dan J Over a year ago

You also have to declare myResult outside of the try block, else it doesn't exist in the scope where you're returning it.

Kami Over a year ago

@DanJ Thanks for the catch, added a return statement as the variable is not really needed.

Patrick Hofman · Accepted Answer · 2014-01-15 17:25:01Z

0

You'd better use

SQLCON.CreateCommand()

This will always assure you have the command associated with the connection you want.

Why is this better? Assume you go use another database platform, then you don't have to change the code for the command (since the command returned is of type IDbCommand which is the interface behind the SqlCommand, etc.)

answered Jan 15, 2014 at 17:25

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Collectives™ on Stack Overflow

Function for single SQL command

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related