What is causing incorrect syntax error with update??

T

TreyH

The error is:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','.


And occurs towards the bottom of this code snippet

What is causing this??

Thanks

(FYI - the code doesn't wrap like it's showing here)

string sText = "Select * from dbo.USERS_tbl where 1=0";
string strConn = "Data Source=localhost;Initial
Catalog=portal_sscc_testing;uid=startuser;Pwd=startuser;";
SqlConnection objConn = new SqlConnection(strConn);

SqlDataAdapter myDA = new SqlDataAdapter(sText, objConn);
string sInserttxt = myCommand.InsertCommand.CommandText;
myDA.InsertCommand = new SqlCommand(sInserttxt, objConn);
myDA.InsertCommand.CommandType = CommandType.Text;//.StoredProcedure;

myDA.InsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar,
50, "UserName");
myDA.InsertCommand.Parameters.Add("@Password", SqlDbType.VarChar,
180, "Password");
myDA.InsertCommand.Parameters.Add("@UsrEmail", SqlDbType.VarChar,
150, "UsrEmail");
myDA.InsertCommand.Parameters.Add("@UsrPhone", SqlDbType.VarChar,
10, "UsrPhone");

objConn.Open();

DataSet myDs = new DataSet();
myDA.Fill(myDs, "user_tbl");

DataRow myRow = myDs.Tables["user_tbl"].NewRow();

myRow["UserName"] = txtUserName.Text;
myRow["Password"] = txtPassword.Text;
myRow["UsrEmail"] = txtEmail.Text;
myRow["UsrPhone"] = txtPhone.Text;

myDs.Tables["user_tbl"].Rows.Add(myRow);

myDA.Update(myDs, "user_tbl"); <===ERROR OCCURS HERE
[System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','.]


objConn.Close();
 
T

trey hafely

"INSERT INTO USERS_Smurfit(UserName, Password, UsrEmail, UsrPhone,
UsrExt, UsrFAX, RoleDefault, CCNumber) VALUES (@username, @Password,
@UsrEmail, @UsrPhone, @UsrExt, @UsrFAX, @RoleDefault, @CCNumber); SELECT
ID, UserName, Password, UsrEmail, UsrPhone, UsrExt, UsrFAX,
RoleDefault, CCNumber FROM USERS_Smurfit WHERE (ID = @@IDENTITY)";

There was an error with the statement above, I corrected that but, now
it says:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into
column UserName

I checked and I am passing a value to UserName.

Any ideas?????

thanks
 
R

Ron Allen

Trey,
Password is a reserved word in Sql Server so you need to quote the
column as [Password] for the field references.

Ron Allen
 
T

trey hafely

Ron,

Yeah, I thought of that but, it apparently didn't make a difference
either way. Go figure.

What was the problem was there was a trigger on this table. Once I
stopped the trigger, it worked fine.

I now need to figure out why the trigger jammed it up.
Thanks all!!!!
 
M

Miha Markic

Hi trey,

I see that you've solved the problem already. Good.
There is another possible problem in your code - instead of @@IDENTITY you
should use SCOPE_IDENTITY..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top