How to Insert data into self reference table in sql server?

Question

What is the the query for inserting data into a self-referencing table. My table has 4 columns: SlNo, Name , ParentId , CurrentBanlance.

I tried this SQL query but it doesn't execute, is there any another way?

INSERT INTO Ptr_AcntInfo 
   SELECT 
       '" + txtAcName.Text + "',
       (SELECT [SlNo] FROM Ptr_AcntInfo WHERE [Ac_Nm] = '" + cbxAcntGrpName.Text + "'),"+0.00+""

In this query I am getting the below error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

your (select [SlNo] from Ptr_AcntInfo where[Ac_Nm]='" + cbxAcntGrpName.Text + "') query will always return one SlNo? — Rahul Vasantrao Kamble, Feb 19 '13 at 10:44
it shows error like Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ')'. — , Feb 19 '13 at 10:57
If you have changes you'd like to make to your question, you should *edit* the existing question, not post a new one. — Damien_The_Unbeliever, Feb 19 '13 at 11:36

Darren · Accepted Answer · 2013-02-19T11:12:52.070

3

The query should look like:

"INSERT INTO PTR_ACNTINFO (COL1, COL2)
(SELECT " + txtAcName.Text + ", SINo FROM
PTR_ACNTINFO
WHERE [Ac_Nm]='" + cbxAcntGrpName.Text + "')"

Note this query is vulnerable to SQL Injection attacks.

http://en.wikipedia.org/wiki/SQL_injection

You should use parameterised queries or a stored procedure.

Example:

SQLCommand sqlCommand = new SQLCommand(connection); 
sqlCommand.CommandText =  "INSERT INTO PTR_ACNTINFO (SELECT $name, SINo FROM
                           PTR_ACNTINFO WHERE [Ac_Nm]='$accNo')"

sqlCommand.Parameters.AddWithValue("$name", txtAcName.Text);
sqlCommand.Parameters.AddWithValue("$accNo", cbxAcntGrpName.Text);

http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/

edited Feb 19 '13 at 11:12

answered Feb 19 '13 at 10:44

Darren

68,902
24
138
144

Thanks for your reply but it shows error as Incorrect syntax near the keyword 'SELECT'. – Feb 19 '13 at 11:11
@NareshJ I have updatedd my post. Replace COL1 and COL2 with your column names. – Darren Feb 19 '13 at 11:13

How to Insert data into self reference table in sql server?

1 Answers1

Linked