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:
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:
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.
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...
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
Member
120 Points
303 Posts
Problem in Query
Jan 15, 2013 11:42 PM|jeeten.parmar|LINK
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();Member
417 Points
251 Posts
Re: Problem in Query
Jan 15, 2013 11:53 PM|ManikandanUlagu|LINK
create procedure in sql server and call the procedure from page......
Click "…Mark As Answer" if my reply helpful....
Contributor
2571 Points
1130 Posts
Re: Problem in Query
Jan 15, 2013 11:54 PM|Shuvo Aymon|LINK
instead of named parameter try with below approach
If more posts give you useful answers, Please each as "Answer".
Member
120 Points
303 Posts
Re: Problem in Query
Jan 16, 2013 12:11 AM|jeeten.parmar|LINK
I don't think so I will get any problem if I am using SQL Server.
But here, I am using Access Database...
Member
120 Points
303 Posts
Re: Problem in Query
Jan 16, 2013 12:14 AM|jeeten.parmar|LINK
Getting same error...
Member
417 Points
251 Posts
Re: Problem in Query
Jan 16, 2013 12:34 AM|ManikandanUlagu|LINK
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();
}
}
Click "…Mark As Answer" if my reply helpful....
Member
417 Points
251 Posts
Re: Problem in Query
Jan 16, 2013 12:36 AM|ManikandanUlagu|LINK
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();
}
}
Click "…Mark As Answer" if my reply helpful....
Member
417 Points
251 Posts
Re: Problem in Query
Jan 16, 2013 12:37 AM|ManikandanUlagu|LINK
Refer the below link....
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
Click "…Mark As Answer" if my reply helpful....
All-Star
52503 Points
15668 Posts
Re: Problem in Query
Jan 16, 2013 01:47 AM|oned_gk|LINK
add "FROM"
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=?
Suwandi - Non Graduate Programmer
Member
120 Points
303 Posts
Re: Problem in Query
Jan 16, 2013 02:55 AM|jeeten.parmar|LINK
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...
All-Star
25756 Points
7014 Posts
Re: Problem in Query
Jan 16, 2013 02:57 AM|hans_v|LINK
I think this should do it
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);Member
120 Points
303 Posts
Re: Problem in Query
Jan 16, 2013 03:43 AM|jeeten.parmar|LINK
I am getting message for successfulyl updation but when i check database, old values are still there...
Member
78 Points
67 Posts
Re: Problem in Query
Jan 17, 2013 12:10 AM|Ebenezar - Gislen Softwares|LINK
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.
All-Star
25756 Points
7014 Posts
Re: Problem in Query
Jan 17, 2013 04:12 AM|hans_v|LINK
Read this:
http://forums.asp.net/post/5271897.aspx
All-Star
25756 Points
7014 Posts
Re: Problem in Query
Jan 17, 2013 04:13 AM|hans_v|LINK
Can you show us the code you're using now?
Member
120 Points
303 Posts
Re: Problem in Query
Jan 17, 2013 08:57 AM|jeeten.parmar|LINK
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();Member
120 Points
303 Posts
Re: Problem in Query
Jan 17, 2013 09:00 AM|jeeten.parmar|LINK
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...
All-Star
25756 Points
7014 Posts
Re: Problem in Query
Jan 17, 2013 09:23 AM|hans_v|LINK
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
All-Star
94130 Points
18109 Posts
Re: Problem in Query
Jan 17, 2013 08:07 PM|Decker Dong - MSFT|LINK
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!