Solved

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

Posted on 2009-04-11
4
383 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
  • 2
  • 2
4 Comments
 
LVL 62

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 62

Accepted Solution

by:
Fernando Soto earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ADO.NET ENTITY DATA MODEL 3 32
Filename to be appended with DateTimeof Download 3 47
SQL Exceptions 3 40
C# MVC and a global static factory that never dies 4 49
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now