Calling a SQL Server stored procedure with C#

-1

I would like to execute an SQL command in C# to add a row of data into an existing SQL Table. I am new to this entirely, but was successful when using an Insert Into Table command, but still have issues when trying to call a Stored Procedure. I can successfully have data populate to the table if I have the following running in my application:

private void tmr_sql_send_levels_Tick(object sender, EventArgs e)
    {
    tmr_sql_send_levels.Enabled = false;
    con = new SqlConnection(@"Data Source=localhost;Initial 
    Catalog=FirstTestDB;Integrated Security=True");

    con.Open();

    cmd = new SqlCommand(@"INSERT INTO TankLevels (PltNum, TankNum, 
    Material, 
    TankLevel) Values (@PltNum, @TankNum, @Material, @TankLevel)", con);

    cmd.Parameters.AddWithValue("@PltNum", tbPltNum.Text);
    cmd.Parameters.AddWithValue("@TankNum", tbAI0TankNum.Text);
    cmd.Parameters.AddWithValue("@Material", cbAI0Mat.Text);

    float tankLevel;
    if (float.TryParse(tbAI0Val.Text, out tankLevel))
        cmd.Parameters.AddWithValue("@TankLevel", tankLevel);

    cmd.ExecuteNonQuery();

    tmr_sql_send_levels.Enabled = true;
    }

but I have no luck and get an exception:

> > System.Data.SqlClient.SqlException: 'Procedure storeTankLevels has no parameters and arguments were supplied.'
> 
> System.Data.SqlClient.SqlException   HResult=0x80131904  
> Message=Procedure storeTankLevels has no parameters and arguments were
> supplied.   Source=.Net SqlClient Data Provider   StackTrace:    at
> System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection, Action`1 wrapCloseInAction)    at
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at
> System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
> stateObj, Boolean& dataReady)    at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString, Boolean
> isInternal, Boolean forDescribeParameterEncryption, Boolean
> shouldCacheForAlwaysEncrypted)    at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry,
> SqlDataReader ds, Boolean describeParameterEncryptionRequest)    at
> System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, TaskCompletionSource`1 completion, Int32 timeout, Task& task,
> Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at
> System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
> completion, String methodName, Boolean sendToPipe, Int32 timeout,
> Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at
> System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at
> ADAM_TankLevel_Config_Form_Test.TankLevelConfigForm.tmr_sql_send_levels_Tick(Object
> sender, EventArgs e) in C:\Users\Justin Fehl\Documents\Visual Studio
> 2019\Projects\ADAM_TankLevel_Config_Form_Test\ADAM_TankLevel_Config_Form_Test\TankLevelConfigForm.cs:line 185    at System.Windows.Forms.Timer.OnTick(EventArgs e)    at
> System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)    at
> System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
> Int32 msg, IntPtr wparam, IntPtr lparam)    at
> System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)   
> at
> System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr
> dwComponentID, Int32 reason, Int32 pvLoopData)    at
> System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
> reason, ApplicationContext context)    at
> System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
> reason, ApplicationContext context)    at
> System.Windows.Forms.Application.Run(Form mainForm)    at
> ADAM_TankLevel_Config_Form_Test.Program.Main() in C:\Users\Justin
> Fehl\Documents\Visual Studio
> 2019\Projects\ADAM_TankLevel_Config_Form_Test\ADAM_TankLevel_Config_Form_Test\Program.cs:line
> 19
> 
>   This exception was originally thrown at this call stack:
>     [External Code]
>     ADAM_TankLevel_Config_Form_Test.TankLevelConfigForm.tmr_sql_send_levels_Tick(object, System.EventArgs) in TankLevelConfigForm.cs
>     [External Code]
>     ADAM_TankLevel_Config_Form_Test.Program.Main() in Program.cs

If I try to run:

private void tmr_sql_send_levels_Tick(object sender, EventArgs e)
{
tmr_sql_send_levels.Enabled = false;

con = new SqlConnection(@"Data Source=localhost;Initial 
Catalog=FirstTestDB;Integrated Security=True");

cmd = new SqlCommand("storeTankLevels", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@PltNum", tbPltNum.Text);
cmd.Parameters.AddWithValue("@TankNum", tbAI0TankNum.Text);
cmd.Parameters.AddWithValue("@Material", cbAI0Mat.Text);

float AI0tankLevel;

if (float.TryParse(tbAI0Val.Text, out AI0tankLevel))
    cmd.Parameters.AddWithValue("@TankLevel", AI0tankLevel);

con.Open();
int rowAffected = cmd.ExecuteNonQuery();
con.Close();

tmr_sql_send_levels.Enabled = true;
}

My attempt at creating the Stored Procedure was:

CREATE PROCEDURE storeTankLevels 


AS
DECLARE @PltNum varchar
DECLARE @TankNum varchar
DECLARE  @Material varchar
DECLARE @TankLevel int

INSERT INTO TankLevels (PltNum, TankNum, Material, TankLevel) Values (@PltNum, @TankNum, @Material, @TankLevel)

Any thoughts on what is not formatted correctly would be appreciated.

c#
sql-server
stored-procedures
asked on Stack Overflow Jul 17, 2020 by Angus74 • edited Jul 18, 2020 by Angus74

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0