|
As far as you can!!!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
|
I'm with the other 2, their arguments are perfectly valid, and here is another, triggers spit are a support nightmare, digging out an error in a nested trigger can drive you nuts.
Triggers spit have their place, they are excellent for auditing and logging changes to your data ONLY.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Team,
I need to compare two tables and do the add/update functionality.If the Table2 have any new records it should be added in the Table1 and if the Table1 have same records which is in Table2, its should be updated.
Table1
ID Name Mark
1 Kishor 56
2 Amit 78
4 Vinay 58
Table2
ID Name Mark
2 Amit 67
6 Raj 89
1 Kishor 98
Output
ID Name Mark
1 Kishor 98
2 Amit 67
4 Vinay 58
6 Raj 89
|
|
|
|
|
It would be useful to know which database you are using. Assuming SQL Server then here is some google foo[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
as we all know that the correct format for date and time data in sqlite is :
2014-06-18 01:00:00
my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks
|
|
|
|
|
You should be storing the data as a datetime TYPE not a string, therefore convert (TryParse) your string input to a datetime and pass it to the database in the correct data type using a paramaterised query.
If it fails the tryparse it is invalid
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You should be storing the data as a datetime TYPE not a string
SQLite does not have a DATETIME datatype. I'd recommend storing it as an integer, as recommend on their website[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: SQLite does not have a DATETIME datatype
Now that I did not know.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Eddy Vluggen wrote: SQLite does not have a DATETIME datatype
Hard to believe, but true. Poor guys who have to deal with such crap!
|
|
|
|
|
Bernhard Hiller wrote: Hard to believe, but true. Poor guys who have to deal with such crap!
I prefer this crap over SqlCe, MS Access, XML and Excel as a single-file database.
A DateTime is usually merely a double; a boolean is usually merely a bit. SQLite is lightweight; you won't find a XML, GUID nor a Spatial datatype either - because all those types can be derived from what is already there.
The only thing that seemed weird is that the bool is associated with a numeric type, and not an integer-type.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want.
And have you looked at Sql Server LocalDB?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: And have you looked at Sql Server LocalDB?
I tried, but failed at downloading[^] it.
PIEBALDconsult wrote: I'll stick with SqlCe when given a choice; better to have features I don't use
than miss features I want.
So you'll be replacing all those SqlCe's with Sql-express versions?
SqlCe and SQLite are a close match; but I like to run my code as well on my OpenSUSE desktop and there's no SqlCe there 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: replacing all those SqlCe's with Sql-express versions
No. The other way around. 
I use Express for a few things, but for one of my projects I wanted to be able to have the app on a flash drive and not require a server/service/daemon running. That way it can be carried around and used whereever. So now I allow the app to use Ce or regular Sql Server, whichever the user wants to use. All the tables and SQL statements are the same (including DATEs and GUIDs), only the Provider is different.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
..and might even work relative happily using a different provider[^] on a Raspberry Pi
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Bernhard Hiller wrote: have to deal with such crap
Seems a choice to me.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
No, there isn't. You validate it in code before inserting/updating.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Steps:
Choose the data type that it is actually going to be stored as.
Take a value and use on of the sqllite functions to convert it to a different type.
Take that value and convert it back to the original type.
If they are the same then it is valid.
So if you start with text, convert it to a number, then convert the number back to text and compare the original text and the final text.
|
|
|
|
|
rahmat1985 wrote: as we all know
We don't all know that.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have the following table in MySQL:
table name : contacts
Field:
contact_name
company_name
service_offered
I want the user to be able to search the contact table in this way:
if the user entered
John Dell
then it should search all fields for any of the given words
so if John is the first name and Dell is company
or John Dell is the name (contains)
or John is the name and Dell is the service
etc...
can anyone tell me how can I do such SQL?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
Dear Jassim,
If you know the exact column list to search data
For Example :
1. company_name
2. first_name
3. last_name
Then you can write down your query as below :
SELECT * FROM contacts WHERE (company_name like '%'+@searchText+'%' OR first_name like '%'+@searchText+'%' OR last_name like '%'+@searchText+'%')
Secondly, If you don't know about the column list then let me know the I can write a dynamic query for you.
|
|
|
|
|
It would be something like this:
SELECT *
FROM contacts
WHERE (contact_name LIKE '%John%' OR contact_name LIKE '%Dell%')
OR (company_name LIKE '%John%' OR company_name LIKE '%Dell%')
OR (service_offered LIKE '%John%' OR service_offered LIKE '%Dell%')
Basically, you'd split the search text into separate words and dynamically build the query above to include all the words.
|
|
|
|
|
the text might be more than two or less...
so user might type something like
John
or
John Dell
or
John Dell international
so I want to be able to split all and search
Technology News @ www.JassimRahma.com
|
|
|
|
|
Yes, and that's what I said. You'll have to build this sql query dynamically on the client by looping through every word in the search text. Something like this (code just out of my mind, not tested).
string BuildQuery(string searchText) {
string sqlQuery = "SELECT * FROM contacts WHERE ";
string[] words = searchText.Split(" ".ToCharArray());
foreach(string word in words) {
sqlQuery+= string.Format("(contact_name LIKE '%{0}%' OR company_name LIKE '%{0}%' OR service_offered LIKE '%{0}%' ) OR ", word);
}
sqlQuery = sqlQuery.SubString(1, sqlQuery.Length-2);
return sqlQuery;
}
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
