Update one table and insert into another table in SQL Server 2008

Question

I've two tables company with CompanyID {auto increment primary key} and group with GroupID {auto increment primary key}. In the group table I want to insert group details on submit button click along with updating company table's groups column with the GroupID just inserted in group table. Please suggest me how should I do this either with separate commands or through stored procedure. I'm little bit weak with SQL server queries so need some help with explanation. What am I doing wrong it gives "Incorrect syntax near the keyword 'inner'". Thanks in advance.

Submit button click code:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    string group = GroupName.Text;
    string dept = Department.Text;
    string company = Session["com"].ToString();

    if ((HttpContext.Current.User != null) && HttpContext.Current.User.Identity.IsAuthenticated && company != null)
    {
        string strcon = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
        SqlConnection con = new SqlConnection(strcon);
        con.Open();
        DataSet ds = new DataSet();
        SqlDataReader rdr = null;
        SqlCommand cmd1 = new SqlCommand("Insert into Group(GroupName, Department, CompanyID) Values(@groupName, @department, 'Select CompanyID from Company1 where Company.CompanyName=@companyName')", con);
        SqlCommand cmd = new SqlCommand("Update Company1 set Company1.Groups=Group.GroupID inner join Group on Group.CompanyID=Comapny1.CompanyID"
                        +" where Company.CompanyName=@companyName", con);
        cmd1.Parameters.Add("@groupName", SqlDbType.NVarChar).Value = group;
        cmd1.Parameters.Add("@department", SqlDbType.NVarChar).Value = dept;
        cmd1.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = company;
        cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = company;
        //cmd1.Parameters.AddWithValue("@groupName", group);
        //cmd1.Parameters.AddWithValue("@department", dept);
        //cmd.Parameters.AddWithValue("@companyName", company);

        rdr = cmd1.ExecuteReader();
        rdr = cmd.ExecuteReader();
        con.Close();
    }
    else
    {
        Response.Redirect("Account/Login.aspx");
    }
}

Answer 1

With reference to this comment:

the GroupID in company is taken as NVARCHAR(30) to facilitate multiple GroupIDs separated by comma, how can I achieve this any idea?

The answer is don't. You are duplicating data, storing relationships as a delimited string is never a good idea in a relational database. It defeats the purpose.

I think all you need to do is insert the CompanyID at the same time as inserting the group:

INSERT dbo.Group (GroupName, Department, CustomerID)
SELECT  @GroupName, @Department, c.CompanyID
FROM    dbo.Company1 AS c
WHERE   c.CustomerName = @CustomerName;

So your c# would end up looking like:

string sql = @"INSERT dbo.[Group] (GroupName, Department, CustomerID)
                SELECT  @GroupName, @Department, c.CompanyID
                FROM    dbo.Company1 AS c
                WHERE   c.CompanyName = @CompanyName;";
string strcon = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

using (var connection = new SqlConnection(strcon))
using (var command = new SqlCommand(sql, connection))
{
    connection.Open();
    command.Parameters.Add(@GroupName, SqlDbType.NVarChar, 50).Value = group;
    command.Parameters.Add(@Department, SqlDbType.NVarChar, 50).Value = dept;
    command.Parameters.Add(@CompanyName, SqlDbType.NVarChar, 50).Value = company;
    command.ExecuteNonQuery();
}

Note the use of ExecuteNonQuery() instead of ExecuteReader() since you aren't actually reading anything, and the use of using blocks to ensure your objects are disposed of correctly. I have also included the schema prefix in your queries (assumed to be dbo) as it is usually a good idea to include this as standard

If you need to get the groups associated with a particular company do this in a query to avoid recalculating the Groups column with every INSERT/UPDATE/DELETE:

SELECT  c.CompanyID,
        c.CompanyName,
        Groups = STUFF((SELECT ',' + CAST(g.GroupID AS VARCHAR(10))
                        FROM    dbo.[Group] AS g
                        WHERE   g.CompanyID = c.CompanyID
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    dbo.Company AS c;

For more information on concatenating rows into a column see this answer;