how to select the name of one column from one table and insert into other.

[shahai.ali]

hi m getting the following exception:
System.Data.SqlClient.SqlException: Incorrect syntax near '4'.
the following is what i was trying:

public void hostel_cpacity(ManageHostelRoomsBE hmr)
        {
            string str;
            string conString =                           System.Configuration.ConfigurationManager.ConnectionStrings["Hosteluog"].ConnectionString;
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            str = "select HostleName from tbl_Hostel where (HostelID ='" + hmr.HostelID + "')";
            cmd.CommandText = "insert into hostel_info(hostelName,rooms,capacity) values ('" + str + "','" + hmr.RoomNO + "','" + hmr.CapacityID + "')";
            cmd.ExecuteNonQuery();


            con.Close();

        }

plz help me in this regard.

[AleMonteiro]

I'd do it like this:

"insert into hostel_info(hostelName,rooms,capacity) select HostleName, '" + hmr.RoomNO + "','" + hmr.CapacityID + "'  from tbl_Hostel where (HostelID ='" + hmr.HostelID + "')";

Clean example:

INSERT INTO hostel_info(hostelName,rooms,capacity) 
SELECT HostleName, 'rooms', 'capacity'  
FROM tbl_Hostel 
WHERE (HostelID = 'id')

[shahai.ali]

but i think when we have to insert into the table we can work only with a single table. there is a problem while using a sub query for making insertion into table...

[AleMonteiro]

No, there is not a problem. There is only methods to do it. You can make a insert from a select, like I posted.

[JorgeM]

In AleMonteiro's example, he is only showing you how to insert into one table, but from the results of a query from another.

[shahai.ali]

thnx