Two database tables: 1)Client (ClientID, Name) 2)Purchase (ClientID, Name)
Situation: Login page --> online purchase page (Session with username)
Problem: How can i make the SQL over different tables? I want to insert Clientno in Purchase tables when clients make purchase.
Fail SQL: ( The session is okay )
string id = Convert.ToString(Session["UserID"]);
string query1 = "insert int Purchase ([Clientno]) values('"+@clientno+"') where [select Clientno where name ='"+id+"')";
You can insert the results of a select into a table without a VALUES clause, so the straight SQL would look something like this (I changed your query from Clientno to ClientID based on your table definitions up top):
INSERT INTO Purchase ([ClientID])
SELECT ClientID
FROM Client
WHERE Name = @ID
You really want to use parameterized queries though, and not build your SQL inline, like you are doing.
I don't know what other fields you might want to be inserting, but this is the general format of that sort of query.
DECLARE @ClientID int; SELECT @ClientID = ClientID FROM Client WHERE Name = @ID