Must Declare Scalar Variable @Id

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



Leave a reply



Submit