救命！ ，Vb OLEDB INSERT记录与表关系！ [英] HELP! , Vb OLEDB INSERT record with table relations!

问题描述

希望有人可以帮助我....



这么简单，我在访问数据库中有2个表，我想将新记录插入DBChild C_Name& C_Code通过我的VB.Net项目。 > _<



DBParent

P_ID = PK（自动编号）

P_Name = string

P_Info = string



DBChild

P_ID =关系/来自DBParent的P_ID的反射（所以这个AutoNumber也是某种方式）

C_ID = PK（自动编号）

C_Name = string

C_Code = string





*表格关系信息（P_ID）

- 类型：一对多

- 已检查：实施参考完整性

- 已检查：级联更新相关字段

*注意：在DBParent视图中模式它有下拉/可折叠DBChild，我可以通过Access数据库直接从那里编辑和添加新记录



*它在Access数据库中的外观（每个父项有它自己的孩子，它是可折叠的）

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

*我更喜欢：

- 保持DBChild - C_ID为PK（ AutoNumber） - 我的项目确实需要这个）

- 保持P_ID =关系/反射来自DBParent的P_ID - 另一个重要的事情

- 使用条件WHERE作为我的项目需要这个来比较我下面提到的UniqeID值。

- 上帝帮我找到答案，或者至少是查克诺里斯......


我尝试过：



我试过这个

*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

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

但错误说：在SQL结束时缺少SemiColon（;）声明

当我尝试通过VB直接编辑/更改为DBChild（C_Name或C_Code）时错误说：

你无法添加或更改记录，因为表DBParent中需要相关记录





这几天就被困住了（很多虽然在网上冲浪，但仍然无法找到答案...



提前致谢。

J

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

推荐答案

查看您的查询：

Take a look at your query:

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

您将2个查询连接在一起。你不能这样做，因为 .ExecuteNonQuery（）只接受 INSERT |更新|删除语句。

您必须在一个语句中使用 INSERT 并且 SELECT 在另一个（ ExecuteReader ）。

请参阅：如何：创建并执行返回行的SQL语句 [ ^ ]



BTW：永远不要使用连接字符串作为查询！

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!

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

这是最大的原因 SQL注入 [ ^ ]！

好吧最后我想出了我自己!!，就像我以前想的那样，这个问题应该用一种简单的方式解决了。



对于那些谁在这里寻找相同的答案是我的工作解决方案

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

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

因此，在将新记录添加到DBChild时，只需确保P_ID值在DBParent中存在，并且只是使用INSERT INTO只有我们可以把它包起来，就是全部！



感谢 Maciej Los 为我照亮我的方式和 CPallini 指明方向！ \ m /



干杯，

J

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

这篇关于救命！ ，Vb OLEDB INSERT记录与表关系！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！