0

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.

2
Contributors
2
Replies
14
Views
3 Years
Discussion Span
Last Post by mikeybware
1

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.

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.