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.
User contributions licensed under CC BY-SA 3.0