Trouble with SQL INSERT Statement

[mikeybware]

I am trying to insert some values into a database and I have the following SQL:

insertString = "INSERT INTO SocialHistory (ExamID, Occupation, SafetyYN, ComputerYN, ComputerHrs) Values('" & CurrentExamID.ToString & "', (SELECT Occupation, SafetyYN, ComputerYN, ComputerHrs FROM SocialHistory WHERE ExamID = '" & LastExamID & "'))"

When I execute the SQL statement, I get the error message: "Number of query values and destination fields are not the same."

Any suggestions or help on this matter is greatly appreciated.

[hericles]

The parentheses is the problem. Your statement is trying to provide everything in the select statement as one column which means the total number of columns doesn't match.

Something like this will work:

insertString = "INSERT INTO SocialHistory (ExamID, Occupation, SafetyYN, ComputerYN, ComputerHrs) 
SELECT " & CurrentExamID.ToString & " AS ExamID, Occupation, SafetyYN, ComputerYN, ComputerHrs FROM SocialHistory 
WHERE ExamID = '" & LastExamID & "'"

Feed everything you want to insert into the SELECT statement, use AS to match discrete values to their column in the destination table and everything should work out OK.

[mikeybware]

Thank you very much! I have never used AS before, so I definitely learned something new.