Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 227 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by mikeybware
Member Avatar for 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.

Member Avatar for mikeybware

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.