1

I'm unable to find a solution online for my question. If it is even possible, how do I write an SQL Insert statement that uses parameter values as well as selecting a value from another table.

Example:

"INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team)
                 VALUES (@username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, @userteam = (SELECT team_id FROM teaminfo WHERE team_name = '" & ddlAddTeam.SelectedValue & "'))"

I understand that the example is wrong, just trying my best to represent what I'm looking for in code.

Also another question would be regarding aliasing and datareaders. I seem to be unable to do "reader("column_name")" for aliased column names?

Example: 

query = "SELECT u.*, t.team_name FROM Users u
    JOIN teaminfo t ON u.user_team = t.team_id WHERE user_csn = '" & GV.userCSN & "'"

I tried to use 

reader("u.user_name")

but failed as well.

Improve this question
2
  • Are you using it in some code? Like C#? Commented Aug 8, 2018 at 5:35
  • Using it in VB.NET Commented Aug 8, 2018 at 5:43

3 Answers 3

Reset to default
1

You need other syntax of insert operation: INSERT INTO ... SELECT ... FROM ...:

INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team) 
SELECT @username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, team_id --<--not here's your column
FROM teaminfo 
WHERE team_name = @param

Also, it looks like it's .NET (C# or VB code), so you you are prone to SQL injection concatenating you string with parameters!

In my SQL I already put @param in proper place, then with SqlCommand you are probably using, you have to call method Addon SqlCommand.Paramteres collection, and then supplly it with value of ddlAddTeam.SelectedValue.

Try this code:

Using connection = New SqlConnection("connString")
    Using com = New SqlCommand
        com.Connection = connection
        com.CommandText = "INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team) 
                           Select @username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive, team_id --<--Not here's your column
                           From teaminfo
                           Where team_name = @param"
        com.Parameters.Add("@param", SqlDbType.VarChar).Value = ddlAddTeam.SelectedValue
        connection.Open()
    End Using
End Using

And for column alises: in data reader you use column aliases without table name (u before the dot in ou example). Try to give aliases to all your columns to avoid such problems.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

What about the JOIN portion? I saw similar examples to what you provided but didn't know how it worked. Would the Select portion not mean that the parameter values are obtained from teaminfo table?
@LimYX I told, that you should add aliases to your columns to avoid errors (it's optional, if you are sure column names ar unique, then, as I said, use only column name, without table name!).
This particular error appears, '"The parameterized query '(@username varchar(7), @usercsn nvarchar(10),@useradid nvarchar(' expects the parameter '@param', which was not supplied"'. Using it as how you stated in your updated answer and the way i did it 'cmdInsert.Parameters.AddWithValue("@param", ddlAddTeam.SelectedValue)' both didnt seem to work
Because you have lot more parameters! You have to add everyone of it using Parameters.Add (AddWithValue isn't recommended, since it make SQL "guess" the type).
Ah ok got it, thanks for the help and clarification! Also if you don't mind me asking as well, what about in the case of an update? Do I follow similarly like "Update Users SET user_name = @username, user_team = (SELECT team_id FROM teaminfo WHERE team_name = @teamname) WHERE (condition)"
|
0

The data source for an INSERT statement can be a SELECT statement—see the <dml_table_source> part of the statement definition at the linked page—and a SELECT statement can include parameters in the select list. Here's a simple example:

declare @Target table (Id bigint, Datum char(1));
declare @Source table (Id bigint);
declare @Datum char(1) = 'X';
insert @Source values (1);

insert @Target
select
    Id = S.Id,       -- Value from another table
    Datum = @Datum   -- Parameter
from
    @Source S;

There are more examples at the page linked above; scroll down to the "Inserting Data From Other Tables" section header.

Also, if you're going to build a query in (C#?) code as you've shown in your example, you should really pass any arguments as parameters rather than trying to build them directly into the query text. Read up on SQL injection attacks to see why.

Improve this answer

Comments

0

Your INSERT query should be like 

"INSERT INTO Users (user_name, user_csn, user_adid, user_contact, user_adminpriviledge, user_datestart, user_active, user_team)
                 VALUES (@username, @usercsn, @useradid, @usercontact, @userauth, @userstart, @useractive,  (SELECT team_id FROM teaminfo WHERE team_name = @userteam ))"

Second when fetching from reader it should be like : 

reader("user_name") // I am not sure about this. You can put break point and open the object in watch window
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.