locked
Problem in Query RRS feed

  • Question

  • User-2050663300 posted

    I don't know whats d wrong with these queries, I tried so much but not able to come out from this problem.

    Syntax error (missing operator) in query expression 'tblEmail Where Email=@Email'.

    OleDbCommand cmdin = new OleDbCommand();
    cmdin.Connection = dbcon;
    cmdin.CommandType = CommandType.Text;
    cmdin.CommandText = "Delete tblEmail Where Email=@Email";
    cmdin.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = email.ToString();
    cmdin.ExecuteNonQuery(); 


    Syntax error in UPDATE statement

    OleDbCommand odcmd = new OleDbCommand();
    odcmd.Connection = dbcon;
    dbcon.Open();
    odcmd.CommandType = CommandType.Text;
    odcmd.CommandText = "Update tblSMTPSetting Set Name=@Name, Email=@Email, Password=@Password, From_To=@FromTo, Reply_To=@ReplyTo, Host=@Host, Port=@Port";
    odcmd.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = txtName.Text;
    odcmd.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = txtEmail.Text;
    odcmd.Parameters.Add("@Password", OleDbType.VarChar, 50).Value = txtPassword.Text;
    odcmd.Parameters.Add("@FromTo", OleDbType.VarChar, 50).Value = txtFrom.Text;
    odcmd.Parameters.Add("@ReplyTo", OleDbType.VarChar, 50).Value = txtReplyTo.Text;
    odcmd.Parameters.Add("@Host", OleDbType.VarChar, 50).Value = txtHost.Text;
    odcmd.Parameters.Add("@Port", OleDbType.Integer, 10).Value = txtPort.Text;
    odcmd.ExecuteNonQuery();



    Tuesday, January 15, 2013 11:42 PM

Answers

  • User1139353921 posted

    Refer the below link....

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2013 12:37 AM
  • User-1199946673 posted

    add "FROM"

    I think this should do it

    But i think you need where condition to update data like : WHERE ID=?

    No, you don't need a WHERE clause, but without it, All records in the table will be updated, but most likely, this table only contains 1 record with the settings so it isn't a problem.

    The problem in the update query is that in Jet . Password is a Reserved Word, so it should be enclosed in Brackets when used as a field (or table) name:

    odcmd.CommandText = "Update tblSMTPSetting Set Name=@Name, Email=@Email, [Password]=@Password, From_To=@FromTo, Reply_To=@ReplyTo, Host=@Host, Port=@Port"; 
    odcmd.Parameters.AddWithValue("Name", txtName.Text); 
    odcmd.Parameters.AddWithValue("Email", txtEmail.Text); 
    odcmd.Parameters.AddWithValue("Password", txtPassword.Text); 
    odcmd.Parameters.AddWithValue("FromTo", txtFrom.Text); 
    odcmd.Parameters.AddWithValue("ReplyTo", txtReplyTo.Text); 
    odcmd.Parameters.AddWithValue("Host", txtHost.Text); 
    odcmd.Parameters.AddWithValue("Port", txtPort.Text);
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2013 2:57 AM
  • User-1199946673 posted

    Everything seems to be right in your code except two things.

    1. Add from in your delete command

    2. Add some condition  like ( where something=@somevalue), it works in a better way.

    Read this:

    http://forums.asp.net/post/5271897.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 17, 2013 4:12 AM

All replies

  • User1139353921 posted

    create procedure in sql server and call the procedure from page......

    Tuesday, January 15, 2013 11:53 PM
  • User-1412735316 posted

    instead of named parameter try with below approach

    cmdin.CommandText = "Delete tblEmail Where Email=? ";



    Tuesday, January 15, 2013 11:54 PM
  • User-2050663300 posted

    create procedure in sql server and call the procedure from page......

    I don't think so I will get any problem if I am using SQL Server.

    But here, I am using Access Database...

    Wednesday, January 16, 2013 12:11 AM
  • User-2050663300 posted

    instead of named parameter try with below approach

    cmdin.CommandText = "Delete tblEmail Where Email=? ";

    Getting same error...

    Wednesday, January 16, 2013 12:14 AM
  • User1139353921 posted

    Hi ,

    Write the query using string builder and execute the query.

    or try this below one

    [C#]

    string ConnString = Utils.GetConnString();

    string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";

    using (OleDbConnection conn = new OleDbConnection(ConnString))

    {

      using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

      {

        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

        cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

      }

    }

    Wednesday, January 16, 2013 12:34 AM
  • User1139353921 posted

    Follow the below steps...

    //For delete

    [C#]

    string ConnString = Utils.GetConnString();

    string SqlString = "Delete * From Contacts Where FirstName = ? And LastName = ?";

    using (OleDbConnection conn = new OleDbConnection(ConnString))

    {

      using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

      {

        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

        cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

      }

    }

    /// For update

    [C#]

    string ConnString = Utils.GetConnString();

    string SqlString = "Update Contacts Set FirstName = ?, LastName = ?";

    using (OleDbConnection conn = new OleDbConnection(ConnString))

    {

      using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))

      {

        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);

        cmd.Parameters.AddWithValue("LastName", txtLastName.Text);

        conn.Open();

        cmd.ExecuteNonQuery();

      }

    }

    Wednesday, January 16, 2013 12:36 AM
  • User1139353921 posted

    Refer the below link....

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2013 12:37 AM
  • User-1716253493 posted

    add "FROM"

    cmdin.CommandText = "Delete FROM tblEmail Where Email=?";
    
    

    odcmd.CommandText = "Update tblSMTPSetting Set Name = ?, Email = ?, Password = ?, From_To = ?, Reply_To = ?, Host = ?, Port = ?"; 
    odcmd.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = txtName.Text; 
    odcmd.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = txtEmail.Text; 
    odcmd.Parameters.Add("@Password", OleDbType.VarChar, 50).Value = txtPassword.Text; 
    odcmd.Parameters.Add("@FromTo", OleDbType.VarChar, 50).Value = txtFrom.Text; 
    odcmd.Parameters.Add("@ReplyTo", OleDbType.VarChar, 50).Value = txtReplyTo.Text; 
    odcmd.Parameters.Add("@Host", OleDbType.VarChar, 50).Value = txtHost.Text; 
    odcmd.Parameters.Add("@Port", OleDbType.Integer, 10).Value = txtPort.Text; 


    But i think you need where condition to update data like : WHERE ID=?

    Wednesday, January 16, 2013 1:47 AM
  • User-2050663300 posted

    ManikandanUlagu

    Refer the below link....

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    It is showing message  successfully deleted bt when i check database that value is still there....

    and same prob with update query as syntax error...

    Wednesday, January 16, 2013 2:55 AM
  • User-1199946673 posted

    add "FROM"

    I think this should do it

    But i think you need where condition to update data like : WHERE ID=?

    No, you don't need a WHERE clause, but without it, All records in the table will be updated, but most likely, this table only contains 1 record with the settings so it isn't a problem.

    The problem in the update query is that in Jet . Password is a Reserved Word, so it should be enclosed in Brackets when used as a field (or table) name:

    odcmd.CommandText = "Update tblSMTPSetting Set Name=@Name, Email=@Email, [Password]=@Password, From_To=@FromTo, Reply_To=@ReplyTo, Host=@Host, Port=@Port"; 
    odcmd.Parameters.AddWithValue("Name", txtName.Text); 
    odcmd.Parameters.AddWithValue("Email", txtEmail.Text); 
    odcmd.Parameters.AddWithValue("Password", txtPassword.Text); 
    odcmd.Parameters.AddWithValue("FromTo", txtFrom.Text); 
    odcmd.Parameters.AddWithValue("ReplyTo", txtReplyTo.Text); 
    odcmd.Parameters.AddWithValue("Host", txtHost.Text); 
    odcmd.Parameters.AddWithValue("Port", txtPort.Text);
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 16, 2013 2:57 AM
  • User-2050663300 posted

    oned_gk

    add "FROM"

    I think this should do it

    oned_gk

    But i think you need where condition to update data like : WHERE ID=?

    No, you don't need a WHERE clause, but without it, All records in the table will be updated, but most likely, this table only contains 1 record with the settings so it isn't a problem.

    The problem in the update query is that in Jet . Password is a Reserved Word, so it should be enclosed in Brackets when used as a field (or table) name:

    odcmd.CommandText = "Update tblSMTPSetting Set Name=@Name, Email=@Email, [Password]=@Password, From_To=@FromTo, Reply_To=@ReplyTo, Host=@Host, Port=@Port";
    odcmd.Parameters.AddWithValue("Name", txtName.Text);
    odcmd.Parameters.AddWithValue("Email", txtEmail.Text);
    odcmd.Parameters.AddWithValue("Password", txtPassword.Text);
    odcmd.Parameters.AddWithValue("FromTo", txtFrom.Text);
    odcmd.Parameters.AddWithValue("ReplyTo", txtReplyTo.Text);
    odcmd.Parameters.AddWithValue("Host", txtHost.Text);
    odcmd.Parameters.AddWithValue("Port", txtPort.Text);
    

    I am getting message for successfulyl updation but when i check database, old values are still there...

    Wednesday, January 16, 2013 3:43 AM
  • User-1762115340 posted

    I don't know whats d wrong with these queries, I tried so much but not able to come out from this problem.

    Syntax error (missing operator) in query expression 'tblEmail Where Email=@Email'.

    OleDbCommand cmdin = new OleDbCommand();
    cmdin.Connection = dbcon;
    cmdin.CommandType = CommandType.Text;
    cmdin.CommandText = "Delete tblEmail Where Email=@Email";
    cmdin.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = email.ToString();
    cmdin.ExecuteNonQuery();


    Syntax error in UPDATE statement

    OleDbCommand odcmd = new OleDbCommand();
    odcmd.Connection = dbcon;
    dbcon.Open();
    odcmd.CommandType = CommandType.Text;
    odcmd.CommandText = "Update tblSMTPSetting Set Name=@Name, Email=@Email, Password=@Password, From_To=@FromTo, Reply_To=@ReplyTo, Host=@Host, Port=@Port";
    odcmd.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = txtName.Text;
    odcmd.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = txtEmail.Text;
    odcmd.Parameters.Add("@Password", OleDbType.VarChar, 50).Value = txtPassword.Text;
    odcmd.Parameters.Add("@FromTo", OleDbType.VarChar, 50).Value = txtFrom.Text;
    odcmd.Parameters.Add("@ReplyTo", OleDbType.VarChar, 50).Value = txtReplyTo.Text;
    odcmd.Parameters.Add("@Host", OleDbType.VarChar, 50).Value = txtHost.Text;
    odcmd.Parameters.Add("@Port", OleDbType.Integer, 10).Value = txtPort.Text;
    odcmd.ExecuteNonQuery();


    Everything seems to be right in your code except two things.

    1. Add from in your delete command

    2. Add some condition  like ( where something=@somevalue), it works in a better way.

    You might be wondering for getting same kind of answers. But that is all dude.

    I think you should do refresh or execute sql statement in order to see the changes in the database. Incase if you are using visual studio, after execution of the program, Open the table in the front end itself (Show Table data) from the database explorer. Right click and select Execute SQL statement. You can find the chages done.

    Thursday, January 17, 2013 12:10 AM
  • User-1199946673 posted

    Everything seems to be right in your code except two things.

    1. Add from in your delete command

    2. Add some condition  like ( where something=@somevalue), it works in a better way.

    Read this:

    http://forums.asp.net/post/5271897.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 17, 2013 4:12 AM
  • User-1199946673 posted

    I am getting message for successfulyl updation but when i check database, old values are still there...

    Can you show us the code you're using now?

    Thursday, January 17, 2013 4:13 AM
  • User-2050663300 posted

    jeeten.parmar

    I am getting message for successfulyl updation but when i check database, old values are still there...

    Can you show us the code you're using now?

    Code for Update:

    OleDbConnection dbcon = new OleDbConnection(connectionstring);
    
                        string update = "Update tblSMTPSetting Set Name=@Name, Email=@Email, [Password]=@Password, From_Email=@FromEmail, Reply_To=@ReplyTo, Host=@Host, Port=@Port Where ID=1 ";
    
                        OleDbCommand odcmd = new OleDbCommand(update, dbcon);
    
                        dbcon.Open();
    
                        odcmd.CommandType = CommandType.Text;
    
                        odcmd.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = txtName.Text;
                        odcmd.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = txtEmail.Text;
                        odcmd.Parameters.Add("@Password", OleDbType.VarChar, 50).Value = txtPassword.Text;
                        odcmd.Parameters.Add("@FromEmail", OleDbType.VarChar, 50).Value = txtFrom.Text;
                        odcmd.Parameters.Add("@ReplyTo", OleDbType.VarChar, 50).Value = txtReplyTo.Text;
                        odcmd.Parameters.Add("@Host", OleDbType.VarChar, 50).Value = txtHost.Text;
                        odcmd.Parameters.Add("@Port", OleDbType.Integer, 10).Value = txtPort.Text;
    
                        odcmd.ExecuteNonQuery();
    
                        dbcon.Close();

    Code for Delete:

    string edelete = "Delete * from tblEmail Where Email=@Email";
    
    OleDbCommand cmdin = new OleDbCommand(edelete, dbcon);
    cmdin.CommandType = CommandType.Text;
    cmdin.Parameters.Add("@Email", OleDbType.VarChar, 50).Value = email.ToString();
    
    cmdin.ExecuteNonQuery();



    Thursday, January 17, 2013 8:57 AM
  • User-2050663300 posted

    Ebenezar - Gislen Softwares

    Everything seems to be right in your code except two things.

    1. Add from in your delete command

    2. Add some condition  like ( where something=@somevalue), it works in a better way.

    Read this:

    http://forums.asp.net/post/5271897.aspx

    I have one more question :
    It is updating and deleteing data in database which is in bin -> debug -> app_data folder instead of app_data folder of project.

    Can you please tell me something about it...
    It is windows application, using vs2010...

    Thursday, January 17, 2013 9:00 AM
  • User-1199946673 posted

    It is windows application, using vs2010...

    These forums are intended for ASP.NET (web) related questions only! The syntax errors are solved now, if you want more help on windows development, you should find another forum, like

    http://social.msdn.microsoft.com/Forums/en-US/category/visualstudio,vslanguages,vsarch,vstfs,netdevelopment

    Thursday, January 17, 2013 9:23 AM
  • User3866881 posted

    Hi,

    I come here just to see that the thread's state has been solved without marking anyone's answers.

    So I'll close to mark hans_v as answers.

    Anything urgent, please feel free to feedback.

    Reguards!

    Thursday, January 17, 2013 8:07 PM