Error Inserting String Value Into Table

[bullfrog83]

I'm trying to execute this line of code:

cn.Execute "INSERT INTO StdDegReqCourse VALUES (" & lngClassId & ", """ & strStdDegReqCourse & """, " _
& "1, Null," & lngIncludeDegGPA & ", " & lngTransDetailId & ", 0, 0" & lngCourseSortOrder & ")", , adExecuteNoRecords

However, I've found that if the variable strStdDegReqCou rse is THE* I get the error: Invalid Column name 'THE*'. I did not have this problem when working in DAO. How do I fix it?

[nico5038]

Try to specify the column names first like:

cn.Execute "INSERT INTO StdDegReqCourse (ClassId, StdDegReqCourse, num1, Empty, IncludeDegGPA, TransDetailId, num2, num3, CourseSortOrder) VALUES (" & lngClassId & ", """ & strStdDegReqCourse & """, " _
& "1, Null," & lngIncludeDegGPA & ", " & lngTransDetailId & ", 0, 0" & lngCourseSortOrder & ")", , adExecuteNoRecords

Nic;o)

[bullfrog83]

Try to specify the column names first like:

cn.Execute "INSERT INTO StdDegReqCourse (ClassId, StdDegReqCourse, num1, Empty, IncludeDegGPA, TransDetailId, num2, num3, CourseSortOrder) VALUES (" & lngClassId & ", """ & strStdDegReqCourse & """, " _
& "1, Null," & lngIncludeDegGPA & ", " & lngTransDetailId & ", 0, 0" & lngCourseSortOrder & ")", , adExecuteNoRecords

Nic;o)

That didn't work.

[improvcornartist]

Maybe wrap the column names in []?

[nico5038]

Besides specifying the column list the only difference I see is that I use surrounding "(" and ")" after the .execute

Another option might be to have the string printed in the immediate window and executed under MS SQL to check the error message from the database engine.

Nic;o)

[NeoPa]

bullfrog83: That didn't work.

What might be helpful is posting the actual SQL executed that doesn't work. We can probably work it out with some effort, but that's no substitute for having the correct data to work with.

[bullfrog83]

What might be helpful is posting the actual SQL executed that doesn't work. We can probably work it out with some effort, but that's no substitute for having the correct data to work with.

It occurred to me over the weekend that since I'm working in ADO it'd probably be best to write a sproc that does the inserting and execute it in vba. I did that and it works.

[Jim Doherty]

It occurred to me over the weekend that since I'm working in ADO it'd probably be best to write a sproc that does the inserting and execute it in vba. I did that and it works.

Not only is it a better idea you are keeping it server side and centralised should you so wish to edit the thing in future. Most CRUD operations should be done using that methodology if you are working unbound using an ADP