1

Question as above. The SQL code I am currently using is as follows:

"INSERT INTO [tblClasses/Students] (StudentID, ClassID) " & _
"SELECT StudentID FROM tblStudent WHERE Username = @StudentUser " & _
"SELECT ClassID FROM tblClasses WHERE ClassName = @ClassName ;"

When I try to run this query, I receive the following error message:

'Number of query values and destination fields are not the same'

Clearly, the method I am using does not work, so does anyone know the correct way of writing the query?

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
walnut39
  • 23
  • 1
  • 5
  • You want to insert in 1 table ? How do you know that StudentID is related to a specific ClassID ? – Daniel E. Mar 27 '19 at 16:07
  • The code is to allow a teacher to add a student into a specific class, both of which have already been selected prior to the query being executed. – walnut39 Mar 27 '19 at 16:09

1 Answers1

1

You need a single SELECT .. FROM .. statement as source. Normally you'd use a JOIN, but if the tables aren't related, use a cartesian product.

INSERT INTO [tblClasses/Students] (StudentID, ClassID)
SELECT tblStudent.StudentID, tblClasses.ClassID 
FROM tblStudent, tblClasses 
WHERE tblStudent.Username = @StudentUser 
  AND tblClasses.ClassName = @ClassName

Not sure if this will create duplicate records. If necessary, use SELECT DISTINCT.

How to use SQL parameters in VB.Net

To test the query in Access, use [StudentUser] and [ClassName] for the parameters and supply the values when asked for them.

For some reason, Access trips over [ClassName] unless explicitly specified as parameter. Use this:

PARAMETERS StudentUser Text ( 255 ), ClassName Text ( 255 );
INSERT INTO [tblClasses/Students] ( StudentID, ClassID )
SELECT tblStudent.StudentID, tblClasses.ClassID
FROM tblStudent, tblClasses
WHERE tblStudent.Username = [StudentUser] 
  AND tblClasses.ClassName = [ClassName];
Andre
  • 26,751
  • 7
  • 36
  • 80
  • I guess that depends on how you run the SQL statement and how you pass the parameters. To see if the query works in principle, run it in Access (use `[StudentUser]` and `[ClassName]` for the parameters and supply the values when asked for them). – Andre Mar 27 '19 at 16:58
  • This was air code, but I just verified that it works. Double-check all table and column names, make sure you have a comma in `FROM tblStudent, tblClasses`. @walnut39 – Andre Mar 27 '19 at 17:18
  • Odd. Try switching the query from SQL view to Design view. If that doesn't throw an error, please make a screenshot and add a link to it. – Andre Mar 27 '19 at 17:24
  • Hm. This looks correct. It should ask you for both parameters and insert one record. I'm curious why not. If you want, zip and upload the database to e.g. http://www.tinyupload.com/ and post the link. – Andre Mar 27 '19 at 17:46
  • See the link in my answer. – Andre Mar 27 '19 at 18:40