?
Solved

SQL Server 2005 programming with Visual Studio 2005 C#: how to declare table variable used by C#?

Posted on 2009-04-11
4
Medium Priority
?
398 Views
Last Modified: 2013-11-26
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
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();

Open in new window

TableVarErr.jpg
0
Comment
Question by:huangs3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 24122728
SQL parameters need @ as the first character of the name. See code snippet.
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();

Open in new window

0
 

Author Comment

by:huangs3
ID: 24122923
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!
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 24123205
Hi huangs3;

Yes you do neeed the @ in the parmeter names but I do not think that you can use the SQL Parameter object to give a table a name as in your code. I modified your code as I have tested it on my syatem to work. try it this way.

Fernando
System.Data.SqlClient.SqlConnection Connection = new  System.Data.SqlClient.SqlConnection(ConnectionString);
Connection.Open();
String TableName = "Building";
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["Id"].Value = 1;
Command.Parameters["Name"].Value = "Inventory1";
Command.ExecuteNonQuery();

Open in new window

0
 

Author Comment

by:huangs3
ID: 24123373
Hi FernandoSoto:

    Thank you for your follow up. That means no smarter solution than string concatenation for this case. I will accept your answer.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question