"Incorrect syntax near the keyword 'user' in" INSERT INTO statement 1

[mrgulic]

I do not understand why I am getting this error. I have the exact statement used elsewhere accessing a differnt table with different field names of course and that one works.

These are the SQL fields:

SELECT [user_id]
      ,[user_fname]
      ,[user_lname]
      ,[user_details]
      ,[user_created]
      ,[user_created_by]
      ,[user_modified]
      ,[user_modified_by]
  FROM [uac].[dbo].[user]

Here is the code in the ...aspx.cs file

string sqlSecurity = ConfigurationManager.AppSettings["SQL_Security"];
string sqlDatasource = ConfigurationManager.AppSettings["SQL_Server"];
string sqlCatalog = ConfigurationManager.AppSettings["UAC_Catalog"];
string strUser = HttpContext.Current.User.Identity.Name.ToString().TrimStart().Replace("UFP\\", "").Trim();

SqlConnection con = new SqlConnection(sqlDatasource + sqlCatalog + sqlSecurity);
con.Open();


SqlCommand cmd = new SqlCommand("INSERT INTO user (" +
"user_id,user_fname,user_lname,user_details,user_created,user_created_by)" +
" VALUES (@id, @fname, @lname, @details, @created, @createdby) SELECT user_id = SCOPE_IDENTITY()", con);
cmd.Parameters.Add("@id", SqlDbType.NChar, 20).Value = lbl_userId.Text.ToString().Trim();
cmd.Parameters.Add("@fname", SqlDbType.NChar, 50).Value = lbl_userFirstName.Text.ToString().Trim();
cmd.Parameters.Add("@lname", SqlDbType.NChar, 50).Value = lbl_userLastName.Text.ToString().Trim();
cmd.Parameters.Add("@details", SqlDbType.NChar, 100).Value = txt_userDetails.Text.Trim();
cmd.Parameters.Add("@created", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@createdby", SqlDbType.NChar, 20).Value = strUser;
GetAuthorizedUserInfo(cmd.ExecuteScalar().ToString());

strUser is passed into the method


I just don't get why it get the following error:

Incorrect syntax near the keyword 'user'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'user'.

Thanks for your input.

 

[jmeckley]

it think there are 2 problems with the sql.
1. user is not in brackets. put the table and columns in brackets. this removes the issue of reserved keywords.
2. SELECT user_id = SCOPE_IDENTITY() will most likely cause problems as well because user_id is not the proper syntax for a local variable in tsql and, assuming this is a variable, you don't return the value,

try this instead

var sql = @"declare @newId int

insert into [database name].[account, usually dbo].[user] ([column 1], [column 2], ...[column n])Values(...)

select @newId = SCOPE_IDENTITY()
return @newId
"

I would also point out that creating your connection string in the manner you are is very cumbersome and error prone. rather than assemble the connection string from a variety of application settings, use the ConnectionStrings node of the web.config and the ConfigurationManager.ConnectionStrings member to manage the connections.

I'll plug my FAQ as well, it's in my signature below.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[mrgulic]

THANKS jmeckley! That worked. I thought about that, but I didn't think it could make a difference being the other page worked fine.

I just started using .net back in june so I am still learning the best way to do things. Thanks for your suggestions.

 

[jmeckley]

being new to .net development I recommend the following resources. The concepts may go over your head, but learning the concepts/frameworks talked about will pay off huge with developing software.

http://www.codebetter.com

(more than .net, but a majority of the posts are .net)

http://www.lostechies.com

http://www.ayende.com

http://www.jpboodhoo.com

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190