|
hi,
in C# got error while executing update query. error is "syntax error in update statement".
my connection string is
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
Directory.GetCurrentDirectory() + "\\album.accdb;Persist Security Info=False";
query statement
m_thisCommand = m_thisConnection.CreateCommand();
strTemp = "update users set password='admin' where username='admin'";
m_thisCommand.CommandText = strTemp;
m_thisCommand.ExecuteNonQuery();
here
m_thisCommand is OleDbCommand
m_thisConnection is OleDbConnection
Thanks in advance
Have A Nice Day!
Murali.M
Blog
modified 6-Dec-13 4:20am.
|
|
|
|
|
"Password" is a reserved keyword[^] and hence, not a good name for a column.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
i think u r missing a statement...
m_thisCommand.CommandType = CommandType.Text;
please try this,,,
DineshT
|
|
|
|
|
Commandtype.text is default and does not need to be specified.
Oh, and Txtspeak is considered immature and shouldn't be used in professional context.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
For the sake of this question, I've massively reduced everything. I know there are alternate solutions, but I want to understand SQL better and solve this specific problem. (So please refrain from suggesting alternate schemas--I already have those; I really want to understand INTERSECT vs SELF INNER JOIN.)
Given two tables with two columns each:
Table: Words
Id Name
1 One
2 Two
3 Three
Table: Letters
Id Letter
1 A
1 B
1 C
2 A
2 D
3 A
3 C
The task is to find all the Ids in Words which match two letters from Letters. For example, I want to find all the rows in Words which match the letters A AND C from letters. Again, massive oversimplification.
I can do the following (in SQLite):
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='A'
INTERSECT
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='C'
ORDER BY name COLLATE NOCASE
This returns One and Three. I'm happy. Except, it bugs me. I should be able to do the same thing with a series of INNER JOINS, but I've yet to come up with a solution which actually runs. Even with an intermediate table, I'm still not sure how to solve it and thus want to first solve it with this schema. Is there a SQL wizard in the house?
EDIT: Just after posting this, I came up with the following. It works, but is it optimal? Is there a better way? (It seems that if Words is really big, we end up with two huge joins which are then reduced or with the optimizer figure this out?)
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id
INNER JOIN Letters AS L2 ON Words.Id=L2.Id
WHERE L1.Letter='A' AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
I aslso got the following suggestion:
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id AND L1.Letter='A'
INNER JOIN Letters AS L2 ON Words.Id=L2.Id AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
|
|
|
|
|
Personally I use the self join, mainly because the intersect is a new fangled concept that has not penetrated my inertia. Some of the new stuff is just repackaging the the old stuff to be more palatable, linq to sql comes to mind.
My TSQL seems to have ossified about a decade ago as that gets 95% of my work done. I'd hate to have to set up a distributed database these days.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use joins whenever it's possible. The DB will then be able to use indexes for the joining.
Intersect is meant to be used when you have two resultsets with the same columns but different origins and no common indexing.
Instead of a self join you can try this query:
SELECT W.name
FROM Words w JOIN Letters l ON w.ID = l.id
WHERE l.letter IN ('A','C')
GROUP BY W.name
HAVING Count(w.name) = 2A group by is generally faster than a distinct and it will never need more than one index seek, your self join might need two depending on what the optimizer comes up with. But it's a bit quirky as you need to have the letter count available.
But you should consider it if you have large resultsets because it can be pipelined and therefore need much less memory.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
How about using Exists?
SELECT
Id,
Name
FROM
Words As W
WHERE
Exists
(
SELECT 1
FROM Letters As L
WHERE L.Id = W.Id
And L.Letter = 'A'
)
And
Exists
(
SELECT 1
FROM Letters As L
WHERE L.Id = W.Id
And L.Letter = 'C'
)
The query optimizer will probably give you the same plan for this as the INNER JOIN option, but this makes your intention slightly clearer IMO.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am getting this error:
FUNCTION mydatabase.sp_add_new_job_order does not exists
I checked on google and all were talking about spaces causing the problem but I am still unable to identify where is the problem.
Kindly help..
here is my code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11,6), IN param_created_user int, OUT param_record_identity int)
BEGIN
DECLARE param_job_order_id int;
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, start_time, end_time, total_hours, job_order_note, is_contract, contract_id, created_date, created_user) VALUES (param_customer_id, param_cleaner_id, param_job_order_date, param_start_time, param_end_time, param_total_hours, param_job_order_note, param_is_contract, param_contract_id, NOW(), param_created_user);
SET param_job_order_id = LAST_INSERT_ID();
SET param_record_identity = param_job_order_id;
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_job_order_id;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
CALL sp_add_event_log("JOBORDER", param_job_order_id, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
I'm having trouble with this query, use to work in SQL Server 2008, but in 2012, I'm getting rows returned from various dates
So I'm just trying to get all the orders from Dec 3, 2013, I'm passing integer values into the function
12
3
2013
and using parameters Day, Month, Year
My Original SQL
"SELECT * From CompletedOrders " & _
"UNION " & _
"SELECT * FROM CompletedOrdersHistory " & _
"WHERE Day(OrderDate)=<a href="/Members/theday">@TheDay</a> " & _
"AND Month(OrderDate)=@TheMonth " & _
"AND Year(OrderDate)=<a href="/Members/theyear">@TheYear</a>"
Not really sure how to write this or what to search on. I have spent about an hour on this searching the internet.
[update]
I got this to work, but I have to use the greater than, so now I have to back date the date, which will get me trouble at the start of the month, guess I'll try looking up removing a day in SQL
DECLARE @TheDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 2;
set <a href="/Members/Year">@year</a> = 2013;
set @TheDate = DateFromParts(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>);
SELECT * From CompletedOrders
WHERE OrderDate > @TheDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @TheDate;
Well this is what I ended up with. It works, not sure of efficient it is
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 3;
set <a href="/Members/Year">@year</a> = 2013;
set @startDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 0, 0, 0, 0);
set @stopDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 23, 59, 59, 999);
SELECT * From CompletedOrders
WHERE OrderDate > @startDate AND OrderDate < @stopDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @startDate AND OrderDate < @stopDate;
modified 4-Dec-13 22:19pm.
|
|
|
|
|
Hi, i am a fresher.i am trying to insert values using front end,i am getting error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
During table creation for Date i used datetime datatype.
in front end design i used code like this.
Insert into Invoice(date) values('" + _Date + "');
i am using the DataGrid to Display the table in same page,
i tried to give the date in both the format 1/2/2013 and 2013/01/02 but the error.
plz help me to solve this Problem. plz provide the simple code to datetime conversion.
|
|
|
|
|
Member 10437599 wrote: '" + _Date + "'
THAT is a string. Cast it as datetime. Like this ... CAST('"+_Date+"' AS [datetime])
|
|
|
|
|
You are doing a number of things wrong. You should use a datepicker instead of a textbox, this will help you control the format of your data, ALWAYS use the DATETIME data type, NEVER use varchar/string for datetime data.
Learn to use parameterised queries[^] they will make you life a lot simpler and eliminate the data type issue (oh and make your code more secure).
Instead of editing into a datagrid I recommend you use a detail control or a dialog to do your data entry, this give you much greater control easier to manage.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a project with MySQL server database. unfortunately, MySQL doesn't provide a feature to password protect my database (unlike Microsoft SQL Server).
So what's your advise?
Shall I install my application on a separate MySQL instance so I will only have the password?
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
but this will protect the server!
What I want is to give my database a password so their administrator who'll access the same server won't be able to access my entire database even if he has a root for the same server because my database is password protected.
This option is available in Microsoft SQL Server and it's working great
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: This option is available in Microsoft SQL Server and it's working great
Are you sure about that? Any administrator of the domain / local machine can easily give themselves unrestricted access[^] to a SQL instance, even if they're not set up as a user in SQL.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Jassim Rahma wrote: So what's your advise?
Webservices, and hosting the server yourself. Easy argumentation; you'll only be the database-admin if the server is yours.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Update: This did turn out to be a pretty simple issue, so I am marking it now as an Answer. See the end of the message for the Answer...
I suspect I am making a very simple error, because I have a Windows Form application where I have added an Entity Framework DataSource bound to controls via a BindingSource/BindingNavigator that is not saving changes to the underlying SQL database.
I started by dragging the Entity Framework DataSource (as Details) onto a form.
This automatically created the form controls, the BindingSource and BindingNavigator.
When I first brought up the form, no records where being returned from the Entity Framework DataSource, so I created the following variable to initially load data that I was then able to view:
Private TblList As ObjectQuery(Of tblMyTableRec) = From tbl In ObjCntxt.tblMyTableRecs
Then I set the DataSource property of the appropriate BindingSource to this ObjectQuery:
MyBindingSource.DataSource = TblList
So far, so good ... The BindingNavigator now lets me move through the various records in the 'tblMyTableRecs' table.
But, when I make a modification of the data in any of the records (via one of the data bound controls), my changes are not being saved to the underlying SQL database the Entity Framework is setup to use.
I would greatly appreciate any suggestions about what steps I might need to take so that I am able to have changes saved in my SQL database.
Answer: The code generated for the Windows Form BindingSource and BindingNavigator do not automatically perform the Entity Framework ObjectContext SaveChanges operation. Once the SaveChanges function was executed in the Save button Event logic for the appropriate Entity Framework ObjectContext, the changes were saved to the SQL database.
Thanks, Dean
modified 5-Dec-13 21:18pm.
|
|
|
|
|
Hi friends. Can you tell me a perfect and simple website or book to know about the basic concept of OOPS in VB.net. Dont suggest simply google it. Specify some link or e-book.
|
|
|
|
|
You do know you posted in the Database forum?
|
|
|
|
|
I do know.But I believed that there will be someone to help me.
|
|
|
|
|
You don't think you would be more likely to receive relevant advice from the VB forum?
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
