Click here to Skip to main content
15,033,147 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 10-Mar-18 11:01am
v11

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
   
Comments
Maciej Los 11-Mar-18 5:06am
   
Super, you solved it yourself! My 5 to you!
If my answer was helpful in finding final solution, you can accept it too.
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[^]!
   
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
   
oh a bit imagination that what i looking for (this sample of how it looks in Access):

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

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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900