C# ASP.NET SQL Command running stored procedure

[Samantha Penhale]

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=InfoS vcs_ServerLogs; uid=sa;password =cr3ati0n;");
SqlCommand sqlAddLog = new SqlCommand ("sqlAddLog" , sqlCon6);
sqlCon6.Open();
sqlAddLog.Comma ndType = CommandType.Sto redProcedure;
sqlAddLog.Comma ndText = ("exec sqlAddLog");

sqlAddLog.Param eters.Add("@Ass igned_To",Syste m.Data.SqlDbTyp e.VarChar,20).V alue = ddlAdmins.Selec tedItem.Text; ;
sqlAddLog.Param eters.Add("@Ent ry_Date_Time",S ystem.Data.SqlD bType.DateTime, 8).Value = TxtDate.Text;
sqlAddLog.Param eters.Add("@Com ments",System.D ata.SqlDbType.V arChar,5000).Va lue = TxtAction.Text;
sqlAddLog.Param eters.Add("@Ser ver_Id",System. Data.SqlDbType. Int).Value =
ddlServers.Sele ctedValue;
Console.WriteLi ne();
sqlCon6.Close() ;

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

}

catch ( System.Exceptio n Ex )
{
Console.WriteLi ne();

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.Comma ndText = ("exec sqlAddLog");
Get rid of it, your define the command text correctly in the first
parameter of the constructor.

[Samantha Penhale]

Thanks.

1. I commented out the sqlAddLog.Comma ndText = ("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.

"sd********@gma il.com" wrote:

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

[Samantha Penhale]

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

"sd********@gma il.com" wrote:

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

[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=InfoSv cs_ServerLogs;u id=sa;password= cr3ati0n;");
SqlCommand sqlAddLog = new SqlCommand ("sqlAddLog" ,
sqlCon6);
sqlCon6.Open();
sqlAddLog.Comma ndType = CommandType.Sto redProcedure;

sqlAddLog.Param eters.Add("@Ass igned_To",Syste m.Data.SqlDbTyp e.VarChar,20).V alue
= ddlAdmins.Selec tedItem.Text; ;

sqlAddLog.Param eters.Add("@Ent ry_Date_Time",S ystem.Data.SqlD bType.DateTime,
8).Value = TxtDate.Text;

sqlAddLog.Param eters.Add("@Com ments",System.D ata.SqlDbType.V arChar,5000).Va lue
= TxtAction.Text;

sqlAddLog.Param eters.Add("@Ser ver_Id",System. Data.SqlDbType. Int).Value
=
ddlServers.Sele ctedValue;

sqlAddLog.Execu teNonQuery();

}

catch ( System.Exceptio n 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.Comma ndText = ("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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Samantha Penhale" <Sa************ *@discussions.m icrosoft.com> wrote in
message news:B9******** *************** ***********@mic rosoft.com...

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=InfoS vcs_ServerLogs; uid=sa;password =cr3ati0n;");
SqlCommand sqlAddLog = new SqlCommand ("sqlAddLog" , sqlCon6);
sqlCon6.Open();
sqlAddLog.Comma ndType = CommandType.Sto redProcedure;
sqlAddLog.Comma ndText = ("exec sqlAddLog");

sqlAddLog.Param eters.Add("@Ass igned_To",Syste m.Data.SqlDbTyp e.VarChar,20).V alue
= ddlAdmins.Selec tedItem.Text; ;
sqlAddLog.Param eters.Add("@Ent ry_Date_Time",S ystem.Data.SqlD bType.DateTime,
8).Value = TxtDate.Text;
sqlAddLog.Param eters.Add("@Com ments",System.D ata.SqlDbType.V arChar,5000).Va lue
= TxtAction.Text;
sqlAddLog.Param eters.Add("@Ser ver_Id",System. Data.SqlDbType. Int).Value =
ddlServers.Sele ctedValue;
Console.WriteLi ne();
sqlCon6.Close() ;

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

}

catch ( System.Exceptio n Ex )
{
Console.WriteLi ne();

LblError.Text =
Ex.Message;
}

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

[Samantha Penhale]

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

sqlConnection sqlCon6 = null;

sqlAddLog.Execu teNonQuery();

The syntax you gave me worked perfectly.

Samantha