CODEWITHSUNDEEP
c#sql
MissCoder87
MissCoder87

Reputation: 2669

Function for single SQL command

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;

}

Upvotes: 0

Views: 111

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 156978

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

Upvotes: 0

Kami
Kami

Reputation: 19407

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.

Upvotes: 3

Related Questions