By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Error Inserting String Value Into Table

100+
P: 124
I'm trying to execute this line of code:

Expand|Select|Wrap|Line Numbers
  1. cn.Execute "INSERT INTO StdDegReqCourse VALUES (" & lngClassId & ", """ & strStdDegReqCourse & """, " _
  2. & "1, Null," & lngIncludeDegGPA & ", " & lngTransDetailId & ", 0, 0" & lngCourseSortOrder & ")", , adExecuteNoRecords
  3.  
However, I've found that if the variable strStdDegReqCourse 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?
Jul 9 '10 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Try to specify the column names first like:

Expand|Select|Wrap|Line Numbers
  1.  
  2. cn.Execute "INSERT INTO StdDegReqCourse (ClassId, StdDegReqCourse, num1, Empty, IncludeDegGPA, TransDetailId, num2, num3, CourseSortOrder) VALUES (" & lngClassId & ", """ & strStdDegReqCourse & """, " _
  3. & "1, Null," & lngIncludeDegGPA & ", " & lngTransDetailId & ", 0, 0" & lngCourseSortOrder & ")", , adExecuteNoRecords
  4.  
Nic;o)
Jul 9 '10 #2

100+
P: 124
@nico5038
That didn't work.
Jul 9 '10 #3

Expert 100+
P: 303
Maybe wrap the column names in []?
Jul 9 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 9 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 11 '10 #6

100+
P: 124
@NeoPa
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.
Jul 12 '10 #7

Jim Doherty
Expert 100+
P: 897
@bullfrog83
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
Jul 12 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.