Hi,

I m using VS 2005 - ASP.net with C#
i have declared a sqldatasource to insert values in a table, lets say table1. One of the attribute i wanna insert is an FK, which is the PK of some other table, lets say table2. Now the problem is that, to get the value of this FK to insert in table1, i have to run other sql SELECT query, which will fetch/select that FK value from table2 using given info.
How can i do this, as nested queries are not supported by datasource.I have already tried this.

What i hav tried till now is that declared a separate datasource & fetch that FK value by datasource.select() method, but its return type is IEnumerable.
Here is short pic of my code:

IEnumerable locId = datasource.Select(DataSourceSelectArguments.Empty) ;
IEnumerator locationId = locId.GetEnumerator();
locationId.MoveNext();
DataSource.InsertParameters.Add("LocId", locationId.Current.ToString());
DataSource.Insert();


It gives error while executing DataSource.Insert() line, & says INPUT STRING IS NOT IN CORRECT FORMAT. I have already checked & found that everything else is fine .. the problem only occurs while converting IEnumerator into string .. & then into int(while inserting into table)
My FK's datatype is Int32.

Plz Help.

Regards,
Shazia.

In your InsertCommand of your SqlDataSource, you can do an INSERT INTO... SELECT FROM in one statement, where you can pass the values you want to insert to your table from one of your controls and the FK value from another table:

INSERT INTO Table1 ( Col1, COl2, FKCol )
SELECT @Col1, @Col2, PKCol
FROM Table2

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp