C# ASP.NET SQL Command running stored procedure

[Guest]

Hello,

Thanks in advance for any insight you can offer. I've a ASP.NET project
written in C#, two web forms, a lovely gob of using statements. I originally
had one webform with all my fields and datagrid stacked, and thought it would
be cleaner to server.transfer to another webform when I wanted to submit a
new record. When I had just one webform this same syntax worked fine. But now
that I'm not on the same webform as where the datagrid resides, I had to
change the code to use a sqlCommand. I am not presently getting an error, and
it builds fine. (By the way, I did turn off the custom error messages but it
doesn't give me the full error page.)

Earlier on I was getting errors like 'input string not in correct format',
but those seemed to have cleared. I also got an error that was a null object
reference and I seem to have resolved that too.

again thanks,

Samantha



try

{
SqlConnection sqlCon6 = new SqlConnection(
"Server=DP05900;database=InfoSvcs_ServerLogs;uid=sa;password=cr3ati0n;");
SqlCommand sqlAddLog = new SqlCommand ("sqlAddLog", sqlCon6);
sqlCon6.Open();


sqlAddLog.CommandType = CommandType.StoredProcedure;
sqlAddLog.CommandText = ("exec sqlAddLog");

sqlAddLog.Parameters.Add("@Assigned_To",System.Data.SqlDbType.VarChar,20).Value = ddlAdmins.SelectedItem.Text; ;
sqlAddLog.Parameters.Add("@Entry_Date_Time",System.Data.SqlDbType.DateTime, 8).Value = TxtDate.Text;
sqlAddLog.Parameters.Add("@Comments",System.Data.SqlDbType.VarChar,5000).Value = TxtAction.Text;
sqlAddLog.Parameters.Add("@Server_Id",System.Data.SqlDbType.Int).Value =
ddlServers.SelectedValue;


Console.WriteLine();
sqlCon6.Close();

// Server.Transfer ("ControlPanel.aspx");

}

catch ( System.Exception Ex )
{
Console.WriteLine();

LblError.Text =
Ex.Message;
}

finally
{
if (sqlCon6 != null)
sqlCon6.Close();
}
}

 

[sdbillsfan]

You don't mention what the problem is but just glancing at it I see a
few things wrong.

1.) No call to ExecuteNonQuery() on the command object so no call to
the database to update/insert will ever be made.
2.) You try to reference your SqlCon6 object in the finally block but
it is defined in the try block so won't be visible to the finally
block. Define it above the try block as SqlConnection sqlCon6 = null;
3.) You have a call to sqlCon6.Close() in both your try block and your
finally block, it is only needed in your finally block.

 

[sdbillsfan]

Oops, I missed the line
sqlAddLog.CommandText = ("exec sqlAddLog");
Get rid of it, your define the command text correctly in the first
parameter of the constructor.

 

[Guest]

Thanks.

1. I commented out the sqlAddLog.CommandText = ("exec sqlAddLog");
2. I couldn't add the sqlCon6 = null above the try since it wouldn't build
.... saying it would cause sqlCon6 to mean something other than what it
currently meant.
3. I commented out the sqlCon6.Close(); that is found in my try statement.
4. Now I am getting the original error, which I'd thought I'd eliminated ...
Object reference not set to an instance of an object.

I've read a great deal of Google and MSDN posts on this. I know its a
"newbie error" but for the life of me, I can't see what I've not instantiated.

Thanks again.

 

[Guest]

Also, I didn't specify that the code, written as it was originally, didn't
generate an error nor did it create a row in the database. The stored
procedure works fine both through Query Analyzer and also when referenced in
my original code.

Thanks,

S

 

[sdbillsfan]

It should look similar to this (I can't try compiling myself right now,
not on a dev machine):

SqlConnection sqlCon6 = null;
try
{
sqlCon6 = new
SqlConnection("Server=DP05900;database=InfoSvcs_ServerLogs;uid=sa;password=cr3ati0n;");
SqlCommand sqlAddLog = new SqlCommand ("sqlAddLog",
sqlCon6);
sqlCon6.Open();
sqlAddLog.CommandType = CommandType.StoredProcedure;

sqlAddLog.Parameters.Add("@Assigned_To",System.Data.SqlDbType.VarChar,20).Value
= ddlAdmins.SelectedItem.Text; ;

sqlAddLog.Parameters.Add("@Entry_Date_Time",System.Data.SqlDbType.DateTime,
8).Value = TxtDate.Text;

sqlAddLog.Parameters.Add("@Comments",System.Data.SqlDbType.VarChar,5000).Value
= TxtAction.Text;

sqlAddLog.Parameters.Add("@Server_Id",System.Data.SqlDbType.Int).Value
=
ddlServers.SelectedValue;

sqlAddLog.ExecuteNonQuery();

}

catch ( System.Exception Ex )
{

LblError.Text =
Ex.Message;
}

finally
{
if (sqlCon6 != null)
sqlCon6.Close();
}
}


That exception you're seeing is pretty ambiguous, which line exactly is
causing it (turn on "break into debugger" for all exceptions by hitting
alt-ctr-e, highlighting clr exceptions, then selecting break into
debugger from the radio buttons).

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

remove this line:
sqlAddLog.CommandText = ("exec sqlAddLog");

You do not call any executing method once you have your parameters, you have
to call one of:
ExecuteReader
ExecuteScalar
ExecuteNonQuery

depending of what you want to return.


after that you should check your DB and see if the row was inserted.


cheers,

 

[Guest]

Perfecto! Thank you so much! Now I understand both of the major changes...

sqlConnection sqlCon6 = null;

sqlAddLog.ExecuteNonQuery();

The syntax you gave me worked perfectly.

Samantha