|
Add a where clause with the conditions and user LOWER(Education) = 'masters'
You are screwed with the 6 Years, this should have been stored as a numeric and then you could use Duration > 6
You may also want to look into SOUNDEX filtering for the text fields
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select TblA.*,TblB.*,TblC.*,TblD.* from persons TblA
inner join Education TblB on TblB.PersonID = TblA.PersonID
inner join Skills TblC on TblC.PersonID = TblA.PersonID
inner join JobHistory TblD on TblD.PersonID = TblA.PersonID
|
|
|
|
|
Hi all,
I have a table called Singer and another called Song in Access. The Singer table has a primary key of SingerID and the Song table has a primary key of SongID and a foreign key of SingerID. I'm trying to add data to the Song table and I'm getting the error, "You cannot add or change a record because a related record is required in table 'Singer'. Why am I getting that error? Any help will be greatly appreciated, thanks in advance for your help.
|
|
|
|
|
A Singer must exist before a Song can be sung by the singer.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
modified 25-Aug-12 12:48pm.
|
|
|
|
|
Hi Eddy, thanks for replying.
So you are saying that I will get the error message I mentioned if I try to add a song into the Song table before I add an singer for that song in the Singer table?
|
|
|
|
|
Yes. The song you try to enter does not have a (valid) SingerId. Create a singer, like "Fat Lady", give her number 1 and enter your song with her SingerId. You'll find that the song will be accepted.
If you want to "point" to other tables from your Song-table, then the data in the other table has to exist before the Foreign Key is entered.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
When I wrote something similar a few years back, I created Artist and Title tables and a PlayList table to allow many-to-many relationships between them because a song may be performed by multiple artists.
C:\Projects\KCDXlogger>dbt kcdx "describe Artist ; describe Title ; describe Playlist
DBT V6.0 -- Simple SQL interface Sir John E. Boucher 2003
describe Artist
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Title
Name Type1 Type2 Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id System.Guid DBTYPE_GUID True False False
Name System.String DBTYPE_WVARCHAR(255) True False False
2 records affected.
describe Playlist
Name Type1 Type2 Nullable Unique Read only
-------- --------------- -------------------- -------- ------ ---------
CreTim System.DateTime DBTYPE_DATE True False False
Text System.String DBTYPE_WVARCHAR(255) True False False
PlaTim System.DateTime DBTYPE_DATE True False False
ArtistId System.Guid DBTYPE_GUID True False False
TitleId System.Guid DBTYPE_GUID True False False
5 records affected.
|
|
|
|
|
Thank you all for replying. I have solved this problem. It is as someone had said, you can't create a song before you have a singer.
|
|
|
|
|
Yes, which is very limiting, no?
|
|
|
|
|
PIEBALDconsult wrote: Yes, which is very limiting, no?
Yes, it is. But that was not his original question, it was about referential integrity. So his statement can be rewritten as
you can't create a song before you have singers.
|
|
|
|
|
Shameel wrote: you can't create a song before you have singers. Don't be shocked too much, but I lied (just a little bit). Of course it's possible with the correct schema;
Id | SongWriter |
---|
1 | Paul McCartney | 2 | Mick Jones |
Song | WrittenBy |
---|
1 | A world without love |
You can't have a singer sing a song before the song is written .
Creating a table-structure can be hard; a schema that has already been tried and used by a coworker, is a schema that has probably been tested by the co-workers user-base. Meaning, there's little chance that his code contains errors.
..and sometimes, you have to answer beyond the scope of the question. That must be what I enjoy most of the forums; reading strange questions and strange answers that make you go "hmz". Sometimes one can add a helpful answer, sometimes it's merely a waste of time. The most interesting discussions often start with "care to explain" or "why do you.."
Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
ok, I get you. I made a pretty bad assumptions that the singer is the writer.
Eddy Vluggen wrote: Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.
I agree. I started learning many things when I started answering questions here. Helping others is a great way of learning.
|
|
|
|
|
Shameel wrote: the singer is the writer
I don't think the OP is concerned with the writer at all.
And if he wanted to track writers he would still be best served by a many-to-many schema.
|
|
|
|
|
Right, but the cause of the problem is a limiting schema. He wanted to create a song before he had singers, therefore the schema was the problem, not the code.
A better schema allows one to add either first and then associate them later. And furthermore, his way requires you to add duplicates of a song that is performed by many singers -- which is bad.
|
|
|
|
|
Hi
string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
MySqlConnection con = new MySqlConnection(conn);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "Insert_Image";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?description", SqlDbType.VarChar).Value = txtdesc.Text;
cmd.Parameters.AddWithValue("?fullstr", SqlDbType.VarChar).Value = path;
con.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
Session.RemoveAll();
My code is giving error at executenonquery, "Input string not in correct format". The values in path and txt dec are correct.
Can you please tell where i am wrong?
Regards
Inderjeet Kaur
Inderjeet Kaur
Sr. Software Engg
|
|
|
|
|
The AddWithValue method expects the actual value to be passed rather than the data types, so you should write something like:
cmd.Parameters.AddWithValue("?description", txtdesc.Text);
|
|
|
|
|
Thanks alot!
I am new to MY SQL database and my problem is solved.
Inderjeet Kaur
Sr. Software Engg
|
|
|
|
|
You're welcome.
However, this problem isn't related to My SQL in any way, rather it is to do with ADO.NET.
|
|
|
|
|
When I use MySQL I use an at (@), not a question mark (?) -- I thought that was the problem, does it work with the question mark?
|
|
|
|
|
Dunno, but it works without any mark too. I only use the @-sign within the Sql-statement, the AddWithValue method always without.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
i am new to databse design. i want to design a schema for my bug tracking project . dont know how to start !!
plz help
Himanshu.
himanshu_1002@yahoo.com
Himanshu
|
|
|
|
|
This[^] may help you to start.
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
thanks Niladri.. this was helpful
|
|
|
|
|
|
Learn to Normalize. A simple set of rules that can be applied to any collection of facts, and which results in a relational and normalized dataset.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|