INSER at runtime?

[Krij]

Hi!

Can anybody tell me what I'm missing here?

I'm trying to insert a new record into a sql-database from code at
runtime (not stored procedure),
but get the following error message:

"The 'strUn' is not permitted in this context.
Only constants, expressions or variables allowed here.
Column names are not permitted."

//Create sql connection
SqlConnection con = new SqlConnection
("server=LocalHost;database=Users;uid=geir;pwd=gei r");

//Open database connection
con.Open();

//Create variables to hold values from textboxes
string strUn = txtUsername.Text;
string strPw = txtPassword.Text;

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)
VALUES(strUn, strPw)";
sqlcmd.Connection = con;

try
{
sqlcmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
lblInfo.Text = "ExecuteNonQuery failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}

[Razvan Socol]

Hi, Krij

You should use parameters for an SqlCommand to pass the values of some
variables to SQL Server. For more information, see the example in the
following page:
http://msdn2.microsoft.com/en-us/lib...d.prepare.aspx

Razvan

[Dan Guzman]

The reason you are getting the error is because you are passing C# variable
names in the SQL statement and SQL Server has no idea what those are. I
completely agree with Razvan's suggestion to use parameters. You can then
use those parameters in your SQL statement:

sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)" +
" VALUES(@UserId, @Password)";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gs***@start.nowrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Hi!

Can anybody tell me what I'm missing here?

I'm trying to insert a new record into a sql-database from code at
runtime (not stored procedure),
but get the following error message:

"The 'strUn' is not permitted in this context.
Only constants, expressions or variables allowed here.
Column names are not permitted."

//Create sql connection
SqlConnection con = new SqlConnection
("server=LocalHost;database=Users;uid=geir;pwd=gei r");

//Open database connection
con.Open();

//Create variables to hold values from textboxes
string strUn = txtUsername.Text;
string strPw = txtPassword.Text;

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)
VALUES(strUn, strPw)";
sqlcmd.Connection = con;

try
{
sqlcmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
lblInfo.Text = "ExecuteNonQuery failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}

[Krij]

Hi!

Thanks for answering. Maybe it is the wrong forum to bring up this
question?

But you did both point out a very important idea for me. This was only
a training case for writing code rather than using the built in toolbox
components in C#.

Of course I'll follow your guidance in a real time app.

:-)
Dan Guzman wrote:

The reason you are getting the error is because you are passing C# variable
names in the SQL statement and SQL Server has no idea what those are. I
completely agree with Razvan's suggestion to use parameters. You can then
use those parameters in your SQL statement:

sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)" +
" VALUES(@UserId, @Password)";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gs***@start.nowrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Hi!

Can anybody tell me what I'm missing here?

I'm trying to insert a new record into a sql-database from code at
runtime (not stored procedure),
but get the following error message:

"The 'strUn' is not permitted in this context.
Only constants, expressions or variables allowed here.
Column names are not permitted."

//Create sql connection
SqlConnection con = new SqlConnection
("server=LocalHost;database=Users;uid=geir;pwd=gei r");

//Open database connection
con.Open();

//Create variables to hold values from textboxes
string strUn = txtUsername.Text;
string strPw = txtPassword.Text;

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)
VALUES(strUn, strPw)";
sqlcmd.Connection = con;

try
{
sqlcmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
lblInfo.Text = "ExecuteNonQuery failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}

[Dan Guzman]

Maybe it is the wrong forum to bring up this

question?

Questions directly related to using SQL Server APIs are appropriate here.
In cases like this, it's difficult to know where the problem may be since
the symptom is a SQL Server error message.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gs***@start.nowrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

Hi!

Thanks for answering. Maybe it is the wrong forum to bring up this
question?

But you did both point out a very important idea for me. This was only
a training case for writing code rather than using the built in toolbox
components in C#.

Of course I'll follow your guidance in a real time app.

:-)
Dan Guzman wrote:

>The reason you are getting the error is because you are passing C#
variable
names in the SQL statement and SQL Server has no idea what those are. I
completely agree with Razvan's suggestion to use parameters. You can
then
use those parameters in your SQL statement:

sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)" +
" VALUES(@UserId, @Password)";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gs***@start.nowrote in message
news:11**********************@m73g2000cwd.googleg roups.com...

Hi!

Can anybody tell me what I'm missing here?

I'm trying to insert a new record into a sql-database from code at
runtime (not stored procedure),
but get the following error message:

"The 'strUn' is not permitted in this context.
Only constants, expressions or variables allowed here.
Column names are not permitted."

//Create sql connection
SqlConnection con = new SqlConnection
("server=LocalHost;database=Users;uid=geir;pwd=gei r");

//Open database connection
con.Open();

//Create variables to hold values from textboxes
string strUn = txtUsername.Text;
string strPw = txtPassword.Text;

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)
VALUES(strUn, strPw)";
sqlcmd.Connection = con;

try
{
sqlcmd.ExecuteNonQuery();
}
catch(SqlException ex)
{
lblInfo.Text = "ExecuteNonQuery failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}