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
– Adrian, Commented Jan 15, 2014 at 22:47
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
– Adrian, Commented Jan 15, 2014 at 23:33

Fabio · Accepted Answer · 2014-01-16 08:08:13Z

3

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.

edited Jan 16, 2014 at 8:08

answered Jan 16, 2014 at 2:31

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adrian Over a year ago

And if there's more than one person with a combination of Forename and Surname you'll get multiple rows.

Fabio Over a year ago

@Adrian yes you're right, BUT If he searching SID by Forename and Surbname, then In his application, I assume, logic of creating new user must prevent to creating user with same Surname and Forename(may be in tbl2 Forename and Surname are primary keys). If not, then he never find out which SID must be used in payment if in tbl2 two or three users with same names...

Adrian Over a year ago

Which would generally be a safe assumption, I agree. Doing it slightly differently, by selected the SID outside of the insertion statement, would allow you to put validation in place to ensure you're only ever inserting nothing, one row, or potentially throwing an error due to duplicated data. As this seems to be a system handling financial transaction data, it's better to be safe than sorry

Collectives™ on Stack Overflow

Adding records to fields which get data from another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related