CODEWITHSUNDEEP
vb.netms-access
OcelotcR
OcelotcR

Reputation: 97

Adding records to fields which get data from another table

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

Upvotes: 2

Views: 4514

Answers (1)

Fabio
Fabio

Reputation: 32445

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.

Upvotes: 3

Related Questions