HELP! , Vb OLEDB INSERT record with table relations!

Question

0.00/5 (No votes)

See more:

Hope Someone can help me....

so simply , i have 2 Table in access Database, and i wanna Insert New Record into DBChild C_Name & C_Code via my VB.Net Project. >_<

DBParent
P_ID = PK (Autonumber)
P_Name = string
P_Info = string

DBChild
P_ID = Relation / Reflection From P_ID of DBParent (So this AutoNumber too somehow)
C_ID = PK (AutoNumber)
C_Name = string
C_Code = string

*Table Relations Info (P_ID)
- Type : One To Many
- Checked : Enforce Referential Integrity
- Checked : Cascade Update Related Fields
*Note: In DBParent View mode it has dropdown/collapsible DBChild, and i can easily edit and add new record directly from there via Access database

*How it Looks in Access Database (Each Parent Item has it's own Child and it's Collapsible)

[-] P_ID | P_Name | P_Info 
    6    | PName6 | PInfo6
----------------------------
    C_ID | C_Name | C_Code
    5    | Child1 | Code1
    7    | Child2 | Code2
    8    |????? <- Add new row inside this P_ID /C_ID to fill C_Name & C_Code via VB?
----------------------------
[+] 7    | PName7 | PInfo7
----------------------------

*What i Prefer:
- keep DBChild - C_ID to be PK (AutoNumber) - I really needs this on my project)
- Keep P_ID = Relation / Reflection From P_ID of DBParent - Another Important Thing
- Using Conditional "WHERE" as my project needs this to compare the value with UniqeID that i mentioned below.
- God help me to find the answer, or at least Chuck Norris...

What I have tried:

I have tried this

VB

Try
dim UniqeID = 6 'Let's say this number as uniqe for now
DB.OpenConnection() 'Open Connection
DB.SQLQuery = "INSERT INTO DBChild (C_Name,C_Code) VALUES (@Name,@Code) " & 
              "SELECT * FROM DBParent WHERE P_ID = " & UniqeID & ";"
DB.Command = New OleDbCommand(DB.SQLQuery, DB.Connection)
With DB.Command
    .Parameters.AddWithValue("@Name", Txt_Snippet_Title.Text)
    .Parameters.AddWithValue("@Code", Txt_Editor_Tag.Text)
    .ExecuteNonQuery()
End With

MsgBox("Record Saved!.", MsgBoxStyle.Information) 'Never Reach this Line...
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error " & GetCurrentMethod().Name & " !")
DB.CloseConnection()
Finally
DB.CloseConnection()
End Try

But Error Say : Missing SemiColon (;) at end of SQL Statement
and When i try Edit/Change directly to DBChild (Both C_Name or C_Code) via VB then Error Say :
"You Cannot Add or Change a record because related record is required in table DBParent"

just Stuck by this over days (a lot surfing on internet, though) , but still can't found the answers...

Thanks in advance.
J

Posted 9-Mar-18 23:03pm

InfinityJoe

Updated 10-Mar-18 11:01am

v11

Add a Solution

2 solutions

Solution 1

Take a look at your query:

VB

"INSERT INTO DBChild (C_Name,C_Code) VALUES (@Name,@Code) " & 
"SELECT * FROM DBParent WHERE P_ID = " & UniqeID & ";"

You did concatenate 2 queries in one. You can't do this , becuase .ExecuteNonQuery() accepts only INSERT | UPDATE | DELETE statements.
You have to use INSERT in one statement and SELECT in the other (ExecuteReader).
See: How to: Create and Execute an SQL Statement that Returns Rows[^]

BTW: Never use concatenate strings as a query!

C#

"SELECT * FROM DBParent WHERE P_ID = " & UniqeID & ";

This is the most reason of SQL Injection[^]!

Posted 10-Mar-18 7:41am

Maciej Los

Comments

InfinityJoe 10-Mar-18 16:06pm

Thanks for your valuable Response Mate, that's really brighteen my way :), yes i thought that about concatenate 2 queries into one on this case (and thanks for confirm that it's impossible with direct join of query), but i really needs to make this works as for some reason i needs this engine for my projects most >_<, so would be perfect if you give some quick example for the Query that should i use for make that happen ? (i have checked the link you gave to me but still can't figure it out), i mean just insert new Row to DBChild with new record of C_Name & C_Code in exact location of P_ID after comparing that via VB/oledb form, or another mean to reach "MsgBox("Record Saved!.", MsgBoxStyle.Information) " Line ,and yes will use proper parameters to avoid SQL Injection later on :) , God Bless you man...

InfinityJoe 10-Mar-18 16:20pm

CPallini 10-Mar-18 16:46pm

5.

InfinityJoe 11-Mar-18 3:20am

Hello Sir thanks for pointing out, if you dont mind, maybe you can drop a magic for this one within an code/Query sample ? i really needs to wrap up my project asap, but i'm still can't figure it out :)

thanks in advance,
J

Maciej Los 11-Mar-18 5:03am

Thank you, Carlo.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

InfinityJoe · Accepted Answer · 2018-03-10T22:57:00

Ok Finally I Figure it Out Myself!!, just like i thought before, this problem should be resolved with an simple way somehow :)

For those who looking the same answers here's my Working Solutions

VB

Try
dim UniqeID = 6 'Let's say this number as uniqe for now
DB.OpenConnection() 'Open Connection
DB.SQLQuery = "INSERT INTO DBChild (P_ID, C_Name, C_Code) VALUES (@PID, @Name, @Code)" 
DB.Command = New OleDbCommand(DB.SQLQuery, DB.Connection)
With DB.Command
    .Parameters.AddWithValue("@PID", UniqeID) 'This is The Key!
    .Parameters.AddWithValue("@Name", Txt_Snippet_Title.Text)
    .Parameters.AddWithValue("@Code", Txt_Editor_Tag.Text)
    .ExecuteNonQuery()
End With

MsgBox("Record Saved!.", MsgBoxStyle.Information) 'Finally! :)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error " & GetCurrentMethod().Name & " !")
DB.CloseConnection()
Finally
DB.CloseConnection()
End Try

So, just make sure the "P_ID" value is Exist in DBParent when adding the new Record into DBChild, and yes just using "INSERT INTO" only we can wrap this up, that's All!

Thanks before to Maciej Los for brighten my way and CPallini for pointing the way! \ m /

Cheers,
J