Adding records to fields which get data from another table

Question

I am currently trying to add a new record to a table, lets call it 'tbl1'. This contains the fields

ID | SID | Payment | PaymentDate | TID

The SID Field currently gets its values from another table, lets call this 'tbl2' This contains the fields

SID | Forename | Surname | Location

I have a form, which adds payment records to tbl1, it sucessfully adds the fields ID, Payment and PaymentDate. This is all well and good, but the form does not contain a submit box for SID, this is because it would be hard for the user to remember the SID. So instead they enter a forename and username into the form.

How would I go about retrieving the SID From tbl2 given the Forename and Surname from the form, then apply the SID into tbl1

I have tried double SQL Statements like :

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

From my understanding this should get the record that is associated with it, then from that I should be able to retrieve the SID?

But this hasnt worked, if you understand where I am coming from, can you help me?

I have tried :

'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

Well, to begin with your query there doesn't select the SID, it selects Forename and Surname. The query won't return any data you don't tell it to return. That aside, how are you querying the database? Can you show us the code you're using to build and execute this query? — Adrian
As I noted before, your second SQL statement does nothing to retrieve the SID from tbl2. You need to add that column to your SELECT before you'll be able to make use of it. I'd also suggest you swap the position of your statements if you want to INSERT the SID into tbl1 — Adrian

Fabio Fabio · Accepted Answer · 2014-01-16T02:31:29

Use INSERT INTO with SELECT statement

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

Then add forename and surname parameters to your command

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

Notice that if Forename and Surename not found in tbl2, then new row will not be added to tbl1 On the other hand as @Adrian mentioned in comments - multiply rows will be created if in tbl2 more then one users with same names.

Adding records to fields which get data from another table

1 Answers