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=LocalH ost;database=Us ers;uid=geir;pw d=geir");

//Open database connection
con.Open();

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

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)
VALUES(strUn, strPw)";
sqlcmd.Connecti on = con;

try
{
sqlcmd.ExecuteN onQuery();
}
catch(SqlExcept ion ex)
{
lblInfo.Text = "ExecuteNonQuer y failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}

[Razvan Socol]

Re: INSER at runtime?

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/library/system.data.sqlclient.sqlcommand.prepare.aspx

Razvan

[Dan Guzman]

Re: INSER at runtime?

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.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)" +
" VALUES(@UserId, @Password)";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gsb58@start.no wrote in message
news:1159789120 .493746.210990@ m73g2000cwd.goo glegroups.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=LocalH ost;database=Us ers;uid=geir;pw d=geir");
>
//Open database connection
con.Open();
>
//Create variables to hold values from textboxes
string strUn = txtUsername.Tex t;
string strPw = txtPassword.Tex t;
>
//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)
VALUES(strUn, strPw)";
sqlcmd.Connecti on = con;
>
try
{
sqlcmd.ExecuteN onQuery();
}
catch(SqlExcept ion ex)
{
lblInfo.Text = "ExecuteNonQuer y failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}
>

[Krij]

Re: INSER at runtime?

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.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)" +
" VALUES(@UserId, @Password)";
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Krij" <gsb58@start.no wrote in message
news:1159789120 .493746.210990@ m73g2000cwd.goo glegroups.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=LocalH ost;database=Us ers;uid=geir;pw d=geir");

//Open database connection
con.Open();

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

//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)
VALUES(strUn, strPw)";
sqlcmd.Connecti on = con;

try
{
sqlcmd.ExecuteN onQuery();
}
catch(SqlExcept ion ex)
{
lblInfo.Text = "ExecuteNonQuer y failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}

[Dan Guzman]

Re: INSER at runtime?

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" <gsb58@start.no wrote in message
news:1159850272 .322216.303190@ h48g2000cwc.goo glegroups.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.Command Text = "INSERT INTO tblUsers(Userna me,Password)" +
> " VALUES(@UserId, @Password)";
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"Krij" <gsb58@start.no wrote in message
>news:115978912 0.493746.210990 @m73g2000cwd.go oglegroups.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=LocalH ost;database=Us ers;uid=geir;pw d=geir");
>
//Open database connection
con.Open();
>
//Create variables to hold values from textboxes
string strUn = txtUsername.Tex t;
string strPw = txtPassword.Tex t;
>
//Create a sqlCommand to insert textbox values into sql-database
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandT ext = "INSERT INTO tblUsers(Userna me,Password)
VALUES(strUn, strPw)";
sqlcmd.Connecti on = con;
>
try
{
sqlcmd.ExecuteN onQuery();
}
catch(SqlExcept ion ex)
{
lblInfo.Text = "ExecuteNonQuer y failed because: \n" +
"\n" +
ex.Message;
}
finally
{
con.Close();
}
>

>