Must declare the scalar variable @ID for insert parameter
The problem is still that you're trying to use the same "scope" with two different SQL commands. Even thought they are the same "variable" in C# in SQL they have different scope.
You'll need to execute both statements in one command and add the @ID
parameter as an Output
parameter in order to insert and get the identity out:
nonqueryCommand.CommandType = CommandType.Text;
nonqueryCommand.CommandText = "INSERT tblLoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime); " +
"SELECT @ID = SCOPE_IDENTITY()";
nonqueryCommand.Parameters.AddWithValue("@UserName", txtUserName.Text);
nonqueryCommand.Parameters.AddWithValue("@LoggedInDate", DateTime.Now);
nonqueryCommand.Parameters.AddWithValue("@LoggedInTime", DateTime.Now);
nonqueryCommand.Parameters.Add("@ID",SqlDbType.Int).Direction = ParameterDirection.Output;
thisConnection.Open();
nonqueryCommand.ExecuteNonQuery();
int id = (int)nonqueryCommand.Parameters["@ID"];
Must declare the scalar variable
The reason you are getting the DECLARE
error from your dynamic statement is because dynamic statements are handled in separate batches, which boils down to a matter of scope. While there may be a more formal definition of the scopes available in SQL Server, I've found it sufficient to generally keep the following three in mind, ordered from highest availability to lowest availability:
Global:
Objects that are available server-wide, such as temporary tables created with a double hash/pound sign ( ##GLOBALTABLE
, however you like to call # ). Be very wary of global objects, just as you would with any application, SQL Server or otherwise; these types of things are generally best avoided altogether. What I'm essentially saying is to keep this scope in mind specifically as a reminder to stay out of it.
IF ( OBJECT_ID( 'tempdb.dbo.##GlobalTable' ) IS NULL )
BEGIN
CREATE TABLE ##GlobalTable
(
Val BIT
);
INSERT INTO ##GlobalTable ( Val )
VALUES ( 1 );
END;
GO
-- This table may now be accessed by any connection in any database,
-- assuming the caller has sufficient privileges to do so, of course.
Session:
Objects which are reference locked to a specific spid. Off the top of my head, the only type of session object I can think of is a normal temporary table, defined like #Table. Being in session scope essentially means that after the batch ( terminated by GO
) completes, references to this object will continue to resolve successfully. These are technically accessible by other sessions, but it would be somewhat of a feat do to so programmatically as they get sort of randomized names in tempdb and accessing them is a bit of a pain in the ass anyway.
-- Start of session;
-- Start of batch;
IF ( OBJECT_ID( 'tempdb.dbo.#t_Test' ) IS NULL )
BEGIN
CREATE TABLE #t_Test
(
Val BIT
);
INSERT INTO #t_Test ( Val )
VALUES ( 1 );
END;
GO
-- End of batch;
-- Start of batch;
SELECT *
FROM #t_Test;
GO
-- End of batch;
Opening a new session ( a connection with a separate spid ), the second batch above would fail, as that session would be unable to resolve the #t_Test
object name.
Batch:
Normal variables, such as your @value1
and @value2
, are scoped only for the batch in which they are declared. Unlike #Temp
tables, as soon as your query block hits a GO
, those variables stop being available to the session. This is the scope level which is generating your error.
-- Start of session;
-- Start of batch;
DECLARE @test BIT = 1;
PRINT @test;
GO
-- End of batch;
-- Start of batch;
PRINT @Test; -- Msg 137, Level 15, State 2, Line 2
-- Must declare the scalar variable "@Test".
GO
-- End of batch;
Okay, so what?
What is happening here with your dynamic statement is that the EXECUTE()
command effectively evaluates as a separate batch, without breaking the batch you executed it from. EXECUTE()
is good and all, but since the introduction of sp_executesql()
, I use the former only in the most simple of instances ( explicitly, when there is very little "dynamic" element of my statements at all, primarily to "trick" otherwise unaccommodating DDL CREATE
statements to run in the middle of other batches ). @AaronBertrand's answer above is similar and will be similar in performance to the following, leveraging the function of the optimizer when evaluating dynamic statements, but I thought it might be worthwhile to expand on the @param
, well, parameter.
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'TblTest'
AND type = 'U' )
BEGIN
--DROP TABLE dbo.TblTest;
CREATE TABLE dbo.TblTest
(
ID INTEGER,
VALUE1 VARCHAR( 1 ),
VALUE2 VARCHAR( 1 )
);
INSERT INTO dbo.TblTest ( ID, VALUE1, VALUE2 )
VALUES ( 61, 'A', 'B' );
END;
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR( MAX ),
@PRM NVARCHAR( MAX ),
@value1 VARCHAR( MAX ),
@value2 VARCHAR( 200 ),
@Table VARCHAR( 32 ),
@ID INTEGER;
SET @Table = 'TblTest';
SET @ID = 61;
SET @PRM = '
@_ID INTEGER,
@_value1 VARCHAR( MAX ) OUT,
@_value2 VARCHAR( 200 ) OUT';
SET @SQL = '
SELECT @_value1 = VALUE1,
@_value2 = VALUE2
FROM dbo.[' + REPLACE( @Table, '''', '' ) + ']
WHERE ID = @_ID;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @PRM,
@_ID = @ID, @_value1 = @value1 OUT, @_value2 = @value2 OUT;
PRINT @value1 + ' ' + @value2;
SET NOCOUNT OFF;
Must declare scalar variable @Id?
You should add a SqlParameter
with the name @Id
command.CommandText = "SELECT * FROM CUSTOMER WHERE Id = @Id;";
command.Parameters.Add("@Id", SqlDbType.Int32).Value = Id;
Must declare scalar variable '@ID' with OUTPUT INTO
Yes, use a FROM
clause, because the value is in a table:
DECLARE @IDS TABLE (OrderID INT);
INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice)
OUTPUT INSERTED.OrderID INTO @IDS
VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50);
INSERT INTO [dbo].[Order_Product](OrderID, ProductID)
SELECT i.OrderID, 12
FROM @IDS i;
I changed the name to @IDS
to make it clear that it can have more than one row.
C# Error Must declare the scalar variable @EmpID SQL Insert
You're executing the query twice.
The first time using ExecuteScalar()
and afterwards you clear the parameter values and re-execute the query (now without values for the parameters) in the ExecuteNonQuery()
call. The latter throws the exception
Maybe this altered version will work as intended.
void UpdateInternal0(int employeeId)
{
try
{
using (SqlConnection con = new SqlConnection(Properties.Settings.Default.adminConnectionString))
{
con.Open();
foreach (DataRowView drv in InternalGrid0.SelectedItems)
{
DataRow row = drv.Row;
bool isSelected = Convert.ToBoolean(drv.Row[0]);
if (isSelected)
{
string sqlString = "INSERT INTO ChecklistTransitionTable (RelatedEmployeeID, RelatedDocIdx) VALUES (@EmpID, @DOC_Number)";
using (SqlCommand cmd = new SqlCommand(sqlString, con))
{
for (int i = 0; i < InternalGrid0.Columns.Count; i++)
{
int DocIdx = 0;
try {
DocIdx = (int)drv.Row[i];
} catch (InvalidCastException ice) {
// You should handle this here...
}
cmd.Parameters.AddWithValue("@EmpID", employeeId);
cmd.Parameters.AddWithValue("@DOC_Number", DocIdx);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Console.WriteLine("Test");
//Check Error
if (result < 0)
{
System.Windows.Forms.MessageBox.Show("Error inserting data into database!");
}
}
}
}
}
}
System.Windows.Forms.MessageBox.Show("Employee data successfully updated.");
}
catch (SqlException ex)
{
System.Windows.Forms.MessageBox.Show(string.Format("\nMessage ---\n{0}", ex.Message));
}
}
private void BtnGridUpdate_Click(object sender, EventArgs e)
{
int employeeID = Convert.ToInt32(EmpID.Text);
UpdateInternal0(employeeID);
}
Related Topics
Oledbparameters and Parameter Names
Logging Request/Response Messages When Using Httpclient
Entitytype Has No Key Defined Error
Reliably Stop System.Threading.Timer
Cannot Step into .Net Framework Source Code
Differencebetween Using Idisposable VS a Destructor in C#
@Html.Dropdownlistfor How to Set Default Value
Xml-Selectnodes with Default-Namespace via Xmlnamespacemanager Not Working as Expected
How to Set Properties on Struct Instances Using Reflection
Local Function VS Lambda C# 7.0
Why Is Httpcontext.Current Null After Await
ASP.NET Core MVC:How to Get Raw JSON Bound to a String Without a Type
Newtonsoft.JSON Cannot Convert Model with Typeconverter Attribute
Task Parallel Library Replacement for Backgroundworker