Problem in Query RSS

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Problem in Query

  Jan 16, 2013 06:57 AM|LINK

  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);

  • Marked as answer by decker dong - msft on Jan 22, 2013 02:25 AM
• • Reply
  

  jeeten.parma...

  Member

  75 Points

  97 Posts

  Re: Problem in Query

  Jan 16, 2013 07:43 AM|LINK

  hans_v

  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...

• • Reply
  

  Ebenezar - G...

  Member

  126 Points

  70 Posts

  Re: Problem in Query

  Jan 17, 2013 04:10 AM|LINK

  jeeten.parmar

  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.

  Be Yourself !
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Problem in Query

  Jan 17, 2013 08:12 AM|LINK

  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

   

  • Edited by hans_v on Jan 17, 2013 08:13 AM
  • Edited by hans_v on Jan 17, 2013 08:18 AM
  • Marked as answer by decker dong - msft on Jan 22, 2013 02:24 AM
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Problem in Query

  Jan 17, 2013 08:13 AM|LINK

  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?

• • Reply
  

  jeeten.parma...

  Member

  75 Points

  97 Posts

  Re: Problem in Query

  Jan 17, 2013 12:57 PM|LINK

  hans_v

  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();

  
  
  

• • Reply
  

  jeeten.parma...

  Member

  75 Points

  97 Posts

  Re: Problem in Query

  Jan 17, 2013 01:00 PM|LINK

  hans_v

  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...

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Problem in Query

  Jan 17, 2013 01:23 PM|LINK

  jeeten.parmar

  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

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Problem in Query

  Jan 18, 2013 12:07 AM|LINK

  jeeten.parmar

  jeeten.parmar

  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!