Database

cannot add or change a record

28-Aug-12 21:45

This looks great but what about the conditions? For instance I want to find someone who meets the following conditions:

Education = Masters
Skills = Computer Networking 
Experience = 6 years

Also I would like the search to match lower case spelling of Masters and Computer Networking, how are those 2 things I've just mentioned achieved. Thanks for your help.

Re: Joining 4 Tables

Mycroft Holmes28-Aug-12 23:15

Mycroft Holmes

28-Aug-12 23:15

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

Re: Joining 4 Tables

Karthik J, Coimbatore28-Aug-12 23:33

Karthik J, Coimbatore

28-Aug-12 23:33

SQL

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

ASPnoob24-Aug-12 16:52

24-Aug-12 16:52

SQL

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.

Eddy Vluggen24-Aug-12 22:08

24-Aug-12 22:08

A Singer must exist before a Song can be sung by the singer.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

modified 25-Aug-12 12:48pm.

ASPnoob24-Aug-12 22:16

24-Aug-12 22:16

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?

Eddy Vluggen24-Aug-12 22:21

24-Aug-12 22:21

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 Suspicious | :suss:

if you can't read my code, try converting it here[^]

PIEBALDconsult25-Aug-12 4:57

25-Aug-12 4:57

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.

ASPnoob26-Aug-12 10:44

26-Aug-12 10:44

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.

PIEBALDconsult26-Aug-12 17:10

26-Aug-12 17:10

Yes, which is very limiting, no?

Shameel27-Aug-12 23:44

27-Aug-12 23:44

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.

Eddy Vluggen28-Aug-12 0:17

28-Aug-12 0:17

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 Suspicious | :suss:

if you can't read my code, try converting it here[^]

Shameel28-Aug-12 0:35

28-Aug-12 0:35

ok, I get you. I made a pretty bad assumptions that the singer is the writer. Smile | :)

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.

PIEBALDconsult28-Aug-12 3:16

28-Aug-12 3:16

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.

PIEBALDconsult28-Aug-12 3:14

My SQl error while sending to database

28-Aug-12 3:14

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. Big Grin | :-D

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.

Inderjeet Kaur23-Aug-12 20:25

Inderjeet Kaur

23-Aug-12 20:25

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

Shameel23-Aug-12 21:05

23-Aug-12 21:05

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

Inderjeet Kaur23-Aug-12 21:21

Inderjeet Kaur

23-Aug-12 21:21

Thanks alot!
I am new to MY SQL database and my problem is solved.

Inderjeet Kaur
Sr. Software Engg

Shameel23-Aug-12 23:31

23-Aug-12 23:31

You're welcome.

However, this problem isn't related to My SQL in any way, rather it is to do with ADO.NET.

PIEBALDconsult24-Aug-12 3:41

24-Aug-12 3:41

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?

Eddy Vluggen24-Aug-12 5:22