huangs3
asked on
SQL Server 2005 programming with Visual Studio 2005 C#: how to declare table variable used by C#?
Hi Experts:
I used the following code as an attempt to parametermize a table name of a SQL Server 2005 database.
However, I got the error message in attached picture. I was asked for a "table variable", but I don't know how to declare it. Can you help me to make the code work without using Ac-Hoc method such as string concatenation and hard-coding?
Thank you very much!
Sui
I used the following code as an attempt to parametermize a table name of a SQL Server 2005 database.
However, I got the error message in attached picture. I was asked for a "table variable", but I don't know how to declare it. Can you help me to make the code work without using Ac-Hoc method such as string concatenation and hard-coding?
Thank you very much!
Sui
System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
Connection.Open();
System.Data.SqlClient.SqlCommand Command = Connection.CreateCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "UPDATE @TableName SET Name = @Name, WHERE Id = @Id";
Command.Parameters.Add("Id", SqlDbType.Int);
Command.Parameters.Add("Name", SqlDbType.VarChar);
Command.Parameters.Add("TableName", SqlDbType.VarChar);
Command.Parameters["TableName"].Value = "Building";
Command.Parameters["Id"].Value = 1;
Command.Parameters["Name"].Value = "Inventory1";
Command.ExecuteNonQuery();
TableVarErr.jpg
ASKER
Hi FernadoSoto:
No, it should not need @ character in Parameters.Add and Parameters["TableName"] expression. If I hard-code the table name but leave other parameters, the code would work. I tried you last idea and got "Must declare the table variable "@TableName"" error message.
Any further idea?
Thank you!
No, it should not need @ character in Parameters.Add and Parameters["TableName"] expression. If I hard-code the table name but leave other parameters, the code would work. I tried you last idea and got "Must declare the table variable "@TableName"" error message.
Any further idea?
Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi FernandoSoto:
Thank you for your follow up. That means no smarter solution than string concatenation for this case. I will accept your answer.
Thank you for your follow up. That means no smarter solution than string concatenation for this case. I will accept your answer.
Open in new window