Select /Insert SQL

Last Post 12 Sep 2008 06:51 AM by Bijal. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mypolo
New Member
New Member

--
11 Sep 2008 07:44 AM
I am trying to combine Select and Insert

I am creating some questions site by ASP, by filling in the answers in a table on SQL 2005. and passing to the next site.
I'm giving the user a kind of tracking (identity), wich the user is passing over from on Question site to the other by Request.Form.

On the first Site the user is filling the name, first name, time and the kind of identity

sql= "INSERT INTO web(first name ,name,identity,time)VALUES('" & first name & "','" & name & "','" & identity & "','" & time & "')"

this works fine!

No I would like to combine Insert and Select:


sql= "INSERT INTO table(answer1)VALUES('" & answer1 & "')"
sql= "select* from web where identity=" & identity

This I can't manage yet.



SQLUSA
New Member
New Member

--
12 Sep 2008 12:05 AM

You can combine literals and selected columns at free will in SELECT:

sql= "INSERT INTO table(answer1)
sql= "select '" & answer1 & "', * from web where identity=" & identity

You have to make sure the INSERT and SELECT column lists match.

Let us know if helpful.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
mypolo
New Member
New Member

--
12 Sep 2008 04:56 AM
it still gives an error message:

sql= "INSERT INTO web(answer1)select'" & answer1 & "', * from web where identity=" & identity
error message:


Microsoft][ODBC SQL Server Driver][SQL Server]The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
Bijal
New Member
New Member

--
12 Sep 2008 06:51 AM
Try This:

"INSERT INTO web(answer1) select'" & answer1 & "' from web where identity=" & identity
mypolo
New Member
New Member

--
12 Sep 2008 07:01 AM
thanks a lot
mypolo
New Member
New Member

--
16 Sep 2008 02:41 AM
I have missed it:
It doesnt write the insert into the right row, it starts a new row.

Probably the insert isn't working properyl. :

INSERT INTO web(answer1)select 'gerste' from web where identity='7373@10P136P12P125'

It seems that the identity doesnt match with the identity in the table column.
7373@10P136P12P125



mypolo
New Member
New Member

--
16 Sep 2008 06:17 AM
Uups,
Sure, it is an update:

Trying to find the SQL statement fo the update
sql = Update web Set answer1='" & answer1 & "' where identity='" & identity

It gives just an general error

mypolo
New Member
New Member

--
17 Sep 2008 02:23 AM

sql = "Update web Set answer1='" & answer1 & "' where identity='" & identity & "'"

Gives the error message with
Response.Write (SQL)
Response.End


Update web Set answer11='gerste' where identity=''3597@10P136P12P125''

Wich is not yet working.
mypolo
New Member
New Member

--
17 Sep 2008 02:36 AM
I found the right SQL Statement.
sql = "Update web Set answer1='" & answer1 & "'" & " where identity=" & identity

Thanks a lot for your replies.
SQL seems to be very delicate.
You are not authorized to post a reply.

Acceptable Use Policy