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.

Recommended Answers

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 & " …
Jump to Post

All 2 Replies

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.