CODEWITHSUNDEEP
c#asp.netsqlsql-server
Ronaldinho Learn Coding
Ronaldinho Learn Coding

Reputation: 13824

SQL server: insert into using select and other parameter

I got 3 tables: projects, managers and project_manager

managers

projects

project_manager

Assume that a manager is currently logged-in (so we know what manager_id is) and he want to create a new Project, by entering a name into txtName textbox and hit "create" button. I tried to write:

(1) a query to insert a new project (name) into projects table and

(2) a single query that insert project_id & manager_id into project_manager table

but got syntax error at the third query string and this is not a good way since inserting into projects table (query 1) may be succeeded but inserting into other table (query 2) may be failed, I dont know much "advanced stuff" of SQL, definitely need help:

string CS = ConfigurationManager.ConnectionStrings["CTECS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
    string q1 = "SELECT COUNT(*) FROM [projects] WHERE project_name = @Name";
    SqlCommand cmd = new SqlCommand(q1, con);
    cmd.Parameters.AddWithValue("@Name", txtName.Text);
    con.Open();
    int i = (int)cmd.ExecuteScalar();
    if (i < 1)
    {
        string Command = "INSERT INTO [dbo].[projects] (project_name) VALUES (@Name)";
        cmd = new SqlCommand(Command, con);
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.ExecuteNonQuery();

        Command = "INSERT INTO [project_manager] (project_id, manager_id) VALUES (SELECT project_id FROM [projects] WHERE project_name = @Name, @ID)";
        cmd = new SqlCommand(Command, con);
        // Provide the value for the parameter
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@ID", currentUser.userID.ToString());
        cmd.ExecuteNonQuery();
    }
    else
    {
        Panel1.Visible = true;
        lblMessage.CssClass = "alert alert-error";
        lblMessage.Text = "This project's name has been already used.";
        return;
    }
}

Upvotes: 0

Views: 6551

Answers (4)

Ajay2707
Ajay2707

Reputation: 5798

First of all, you transfer your whole logic in stored procedure which is safe and easily maintainable. Also there is only one hit to sqlserver rather than 3 in your case.

Also good as use @@identity or SCOPE_IDENTITY() to get id of inserted project-id.

So make one sp and code like @Hamlet-Hakobyan suggested. Try to make world simple from complex, not complex from simple.

Your code is good to understand as basic things, but not in professional scenario.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Do you use the server in single_user mode? I don't think so. At first you must do it in the transaction. The best way to do it - SP. Checking the existence of project doesn't guarantee that it not exists when you insert it.

CREATE PROC usp_InsertProject(@project_name varchar(255), @manager_id int)
AS
SET NOCOUNT ON
DECLARE @project_id int
BEGIN TRY
  BEGIN TRAN
    INSERT INTO [dbo].[projects] (project_name) VALUES (@project_name)
    SELECT @project_id = SCOPE_IDENTITY()
    INSERT INTO [project_manager] (project_id, manager_id)
                                  VALUES(@project_id, @manager_id)
  COMMIT TRAN
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
       ,ERROR_MESSAGE() AS ErrorMessage
   ROLLBACK TRAN
END CATCH

GO

then

string CS = ConfigurationManager.ConnectionStrings["CTECS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
    SqlCommand cmd = new SqlCommand("usp_InsertProject", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@project_name", txtName.Text); // you must validate text value before passing
    cmd.Parameters.AddWithValue("@manager_id", currentUser.userID);

    con.Open();
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        if(reader.Read())
        {
           // the error occured in SP
           // reader[0] is error number
           // record[1] is error message
        }
    }
}

Upvotes: 2

Ehsan
Ehsan

Reputation: 32681

Your logic is not correct here in the first place

SELECT project_id FROM [projects] WHERE project_name = @Name

may return multiple results. You may have checks in place that ensure that there can be exactly one project with one name, but system doesn't knows that and you will end up inserting multiple records into the table

A work around can be that you change it to (just to make it work)

INSERT INTO [project_manager] (project_id, manager_id) SELECT project_id, @ID FROM [projects] WHERE project_name = @Name

But, not recommended.

You need to save the id that you are generating for the project name and pass them to your query here.

INSERT INTO [project_manager] (project_id, manager_id) VALUES (@ProjectID, @ID)

Upvotes: 1

Mike Hixson
Mike Hixson

Reputation: 5189

I think the third sql statement should be:

INSERT INTO [project_manager] (project_id, manager_id) SELECT project_id, @ID FROM [projects] WHERE project_name = @Name

Upvotes: 4

Related Questions