How can i solve a sql nested query problem

Question

    Dim Cmd As SqlCommand = New SqlCommand("INSERT INTO feesPayments VALUES('" 
            & Fees_Payments_Dashboard.txtAmountTendered.Text & "','" 
            & Date.Now.ToShortDateString & "', 'studentID' ,'" 
            & Fees_Payments_Dashboard.selectTerm.SelectedItem & "','" 
            & Facility & "','" 
            & Fees_Payments_Dashboard.selectPaymentMethod.Text 
            & "'studentID = SELECT students.studentID 
                            FROM students WHERE 
                            name='" & Fees_Payments_Dashboard.txtName.Text 
                               & "' AND surname='" & Fees_Payments_Dashboard.txtSurname.Text 
                               & "')  ", Conn)
   Cmd.ExecuteNonQuery()

What is your "problem"? Which dmbs do you use? Have you read [ask]? — Reporter, Sep 24 '21 at 09:07
i use sql. The problem is , i have two tables right and i want to insert data in my payments table but one of the column is in the other table. the tables are Students and Payments in which i want to select only the studentsID from the students table and insert it into the payments where studentID is the foreign key — Kculz, Sep 24 '21 at 09:58
You are wide open to sql injection. **Always use parameters**. — Mary, Sep 24 '21 at 18:42

score 0 · Answer 1 · answered Sep 28 '21 at 19:12

INSERT INTO feesPayments VALUES('Fees_Payments_Dashboard.txtAmountTendered.Text',
                                'Date.Now.ToShortDateString', 
                                'studentID' ,'Fees_Payments_Dashboard.selectTerm.SelectedItem',
                                'Facility','Fees_Payments_Dashboard.selectPaymentMethod.Text'

I have not seen syntax for a SQL query written this way before so I apologize if I'm incorrect, but it appears this statement will insert the string value 'studentID' instead of the value held in a parameter called studentID.

I am not sure how your ending portion is supposed to work syntactically either.

Generally, inserts are single rows although they can be multiple like so:

INSERT INTO MyTable
  ( Column1, Column2, Column3 )
VALUES
  ('John', 123, 'Lloyds Office'), 
  ('Jane', 124, 'Lloyds Office'), 
  ('Billy', 125, 'London Office'),
  ('Miranda', 126, 'Bristol Office');

Source: Inserting multiple rows in a single SQL query?

But even with that, it doesn't match how you are attempting.

If you want to insert a brand new row into feesPayments based on some condition, I recommned writing a loop based on that condition in your code base, so that it can call the insert in each loop. Therefore, allowing a simple insert with the values you need.

We will need more information about what you are trying to accomplish though, because it's not very clear based on what you provided.

How can i solve a sql nested query problem

1 Answers1