将记录添加到从另一个表获取数据的字段中

OcelotcR 发表于 Dev

洋猫

我目前正在尝试向表中添加新记录，我们称其为'tbl1'。这包含字段

ID | SID | Payment | PaymentDate | TID

SID域中目前得到它的值从另一个表，让我们称之为“TBL2”这包含的字段

SID | Forename | Surname | Location

我有一个表单，它将付款记录添加到tbl1，它成功添加了字段ID，Payment和PaymentDate。一切都很好，但是该表单不包含SID的提交框，这是因为用户将很难记住SID。因此，他们改为在表单中输入一个姓氏和用户名。

我应该如何从tbl2检索SID（从表单中获得给定的姓氏和姓氏），然后将SID应用于tbl1

我已经尝试过像这样的双重SQL语句：

SELECT tbl2.Forename, tbl2.Surname FROM tbl2 WHERE tbl2.Forename = (FromInputForename) , tbl2.surname = (FOrmInputSurname)

根据我的理解，这应该获得与其关联的记录，然后据此我应该能够检索SID？

但这没有用，如果您了解我的来历，您能帮我吗？

我试过了：

'Declaring the connection route
Public connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data TestDB.accdb"
'The cursor
Public conn As New OleDbConnection(connstring)

Private Sub Addrecordbtn_Click(sender As Object, e As EventArgs) Handles Addrecordbtn.Click
    SPaid = Paidtxt.Text
    'Checking if connection is open.
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    'SQL Query command for ADDING
    Dim sqlquery1 As String = "INSERT INTO tbl1 (Payment,PaymentDate) VALUES (@SPaid,@todaysdate)"
    'Creating the command itself.
    Dim sqlcommand As New OleDbCommand
    With sqlcommand
        'Telling what query
        .CommandText = sqlquery
        '  Paramaters to add with values.
        .Parameters.AddWithValue("@SPaid", SPaid)
        .Parameters.AddWithValue("@todaysdate", Today.Date)
        '   Selecting the connection
        .Connection = conn
        '  Executing the non query
        .ExecuteNonQuery()
    End With
    Dim sqlquery2 As String = "SELECT tbl2.Forename, tbl2.Surname FROM tbl2 WHERE tbl2.Forename = Forenametxt.text , tbl2.surname = Surnametxt.text"
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlquery2, conn)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "Payments")
    Dim dt As DataTable = ds.Tables("Payments")
    'Will then go on to display data.
    conn.Close()
End Sub

法比奥

INSERT INTO与SELECT语句一起使用

INSERT INTO tbl1 (SID,Payment,PaymentDate) 
(SELECT SID, @SPaid, @todaysdate 
FROM tbl2
WHERE Forename = @Forename AND Surname = @Surname)

然后在您的命令中添加forename和surname参数

.Parameters.AddWithValue("@SPaid", SPaid)
.Parameters.AddWithValue("@todaysdate", Today.Date)
.Parameters.AddWithValue("@Forename ", Forenametxt.text)
.Parameters.AddWithValue("@Surname", Surnametxt.text)

请注意，如果Forename并Surename没有发现tbl2，那么新行将不会被添加到tbl1另外的@Adrian在评论中提到-乘行将如果创建tbl2一个以上的用户有相同的名字。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。