Insert Query with Foreign Key

Question

I have three tables in a database and they are interlinked to each other with foreign keys. I want to do a INSERT query so that I can enter the data. I can insert data to each tables individually but when using foreign key constraint I am getting errors.Here are table relationship and VBA form through which i am trying to enter the data VBA Form,Relationships & Data Types

I am using these two queries as of now but the second one is throwing errors.

sql = "insert into T1(ID,FName,Email)values('" & TextBox1.Text & "', '" & TextBox2.Text & "','" & TextBox3.Text & "')"
sql = "insert into T2(ID,PhoneNumber,City) Values (Select(ID FROM T1 WHERE FName=" & TextBox2.Text & "),'" & TextBox4.Text & "','" & TextBox5.Text & "')"

Please note ID is the main field in my tables and I am to do search,update and delete with that field

score 0 · Answer 1 · answered Jun 29 '15 at 00:59

0

The synatx you used in the second query is invalid, you have to write it with INSERT INTO .... SELECT .... and remove the VALUES keyword like this:

INSERT INTO T2(ID,PhoneNumber,City) 
SELECT ID,'" & TextBox4.Text & "','" & TextBox5.Text & "'
FROM T1 
WHERE FName=" & TextBox2.Text & "

The values coming from '" & TextBox4.Text & "','" & TextBox5.Text & "' will be selected as string literals with the values coming from the ID column, this is how you can mix the values selected from a query with string literals and insert them in another table.

answered Jun 29 '15 at 00:59

Mahmoud Gamal

78,257
17
139
164

Okay so I tried with something like this `sql = "INSERT INTO T2(ID,PhoneNumber,City) SELECT ID,'" & TextBox4.Text & "','" & TextBox5.Text & "' FROM T1 WHERE FName=" & TextBox2.Text & " " ` But now it throws a datatype mismatch error – Sensay Jun 29 '15 at 01:24
@Sensay - This means that the query is fine, but you have to ensure that the data types of the values you pass to `TextBox4.Text & TextBox5.Text` are of the same data type of `phonenumber, city`, you have to validate the values of those two text boxs before passing them to the query. – Mahmoud Gamal Jun 29 '15 at 01:34
I have tried this query out of curiosity `sql = "INSERT INTO T2(ID,PhoneNumber,City) SELECT ID FROM T1 WHERE FName=" & TextBox2.Text & ",'" & TextBox4.Text & "','" & TextBox5.Text & "'"` but now it throws Number of query values and destination fields are not same – Sensay Jun 29 '15 at 01:36
PhoneNumber is number and City is text data type. ID is number data type in both T1 & T2. Interesting is if i write a simple query to add PhoneNumber and City it gets added..but as i told ID is an important cell in the tables for me – Sensay Jun 29 '15 at 01:39
@Sensay - This is query is invalid, you can't use this syntax this way, as I said the problem is not in the syntax, the syntax I wrote in my answer is the correct syntax but the data types between the columns and the values you pass are mismatched. What is the data type of the `ID` column in both tables? – Mahmoud Gamal Jun 29 '15 at 01:42
ID is Number data type in T1 and T2. It is primary key in T1 table – Sensay Jun 29 '15 at 01:47
@Sensay What do you mean by Number data type? Which data numberic data type`INTEGER, SMALLINT, DECIMAL, and NUMERIC` and in both tables is it the same data type?? – Mahmoud Gamal Jun 29 '15 at 01:50
LongInt in both the cases. I tried to keep everything pretty basic and tried to use the defaults in access – Sensay Jun 29 '15 at 01:53
@Sensay - there is no `LongInt` data type in mysql. Can you please check the data type of `id` column in both tables? – Mahmoud Gamal Jun 29 '15 at 01:56
[LongInt Data Type](http://i.stack.imgur.com/sTUjn.jpg). Its from access, I had no idea that mysql dont have longint..sorry – Sensay Jun 29 '15 at 02:10

score 0 · Answer 2 · answered Jun 29 '15 at 02:01

0

Mahmoud Gamal is right, but you must ensure that the fields in the list has the same type, for example, you can't use single colon (') when the field is numeric, database admit it but not is correct. And you can't send a alphanumeric string to a numeric field. Finally you must ensure that the order of queries is correct, because foreign key validates references in cascade. Excuse me for my english.

answered Jun 29 '15 at 02:01

Uriel

11
2

I completely agree with you he is right. But my question is if datatypes are wrong then it wouldnt have worked at all. Moreover I havent set any data validation in the form yet so even if you enter a number in a textfield it will be stored as string. – Sensay Jun 29 '15 at 02:18

SimplyInk · Answer 3 · 2015-06-29T02:52:39.377

0

For your second query, I'm using VB.NET to as I have not done VBA programming before:

    Dim sql As String = String.Empty
    sql = "insert into T2(ID,PhoneNumber,City) Values ("

    '// T2.ID(Number)
    sql += "Select(ID FROM T1 WHERE FName=" & TextBox2.Text & "),"

    '// T2.PhoneNumber(Number)
    '// instead of "'" & TextBox4.Text & "'," remove the single quotes
    sql += TextBox4.Text & ","

    '// T2.City(Text) only text need to be enclosed in 'single quotes'
    sql += "'" & TextBox5.Text & "'"

    sql += ")"

Your second query is throwing an error because you are trying to insert text into a number column (T2.PhoneNumber) in your database.

Including your image:

enter image description here

edited Jun 29 '15 at 02:52

answered Jun 29 '15 at 02:45

SimplyInk

5,832
1
18
27

Its ok you are VB.net. I will try that. But aren't there any other solutions to write a sql query? And thanks for helping :) – Sensay Jun 29 '15 at 03:08
In VB.NET you can use parameterized queries ([SqlCommand](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx)) to make your queries safer. Your current code is vulnerable to SQL injection attacks. You can see a sample of how to do it in VB.NET [here](http://stackoverflow.com/questions/31061604/updating-sql-server-datetime-columns-in-vb-net/31062958#31062958) – SimplyInk Jun 29 '15 at 03:12
Thanks, I never thought about the security till this point. I will try to modify my code with VB.NET – Sensay Jun 29 '15 at 03:31
Here's a [xkcd comic](https://xkcd.com/327/) about the severity of the vulnerability... :D – SimplyInk Jun 29 '15 at 03:36

Insert Query with Foreign Key

3 Answers3