We help IT Professionals succeed at work.

Cannot get objConn.execute sql to insert into database

498 Views
Last Modified: 2012-06-21
Here's my problem... I have an insert statement within a LOOP... it works in other code I have but this peice of code (INSERT INTO ONLY) doesn't work....

everything before the INSERT INTO statement and AFTER works PERFECTLY!!!!

I just can't get the sqlXfer(i) to INSERT. I've uncommented out the Response.Write and viewed the SQL Statement for validity and it's formed correctly and even works in Enterprise Manager ISQL window.

Any thoughts please???

-------------------------------
for i = 1 to intRecCnt
                  
      'Get the records to be transferred FIRST ISSUES
      sqlA(i) = "SELECT ir_name, ir_desc, ir_due_date FROM meetings_issues_risks WHERE meeting_id = '" & intMtgID & "' AND exported_flg = '0' AND ir_type = '1'"
                                                'Response.Write(sqlA(i)) & "<br />"
      Set      objRS = objConn.Execute(sqlA(i))

      iNm(i) = objRS.Fields.Item("ir_name").Value
      iDs(i) = objRS.Fields.Item("ir_desc").Value
      iDd(i) = objRS.Fields.Item("ir_due_date").Value
            
      sqlXfer(i) = "INSERT INTO issues (issue_id,pid,issue_name,issue_description,date_due,date_assigned) " & _
      "VALUES ('" & intMaxVal + i & "','" & Session("SV_PID") & "','" & iNm(i) & "','" & iDs(i) & "','" & iDd(i) & "','" & Date & "')"
            
      'Response.Write(sqlXfer(i)) & "<br />"
      objConn.Execute sqlXfer(i)
                  
      'set the exported date and flag to 1
      sqlRS(i) = "UPDATE meetings_issues_risks SET date_exported = '" & Date & "', exported_flg = '1' WHERE meeting_id = '" & intMtgID & "' AND ir_type = '1' and exported_flg = '0'"
            
      'Response.Write(sqlRS(i)) & "<br />"
      objConn.Execute sqlRS(i)
                                          
next

Thanks, Peter
Comment
Watch Question

Commented:
We'd need to see the rest of the code, see how you implemented the objConn object, etc etc..

Commented:
Do you have an error or it's just that your data is not inserted into the table?

Author

Commented:
'Create the connection
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS       = Server.CreateObject("ADODB.Recordset")      
                  
objConn.Open "Provider=sqloledb; Data Source=324.235.454.345; Initial Catalog=test; Source=XXX; userid and password of course... .....<-- the rest is hidden for security reasons...
                        
                        

Author

Commented:
MikeQc: No error... the data simply isn't going into the table....
C# ASP.NET Developer
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It works!!!! By golly gee willikers it works...

I removed the single quotes from around the Numbers and Dates.... and WALA!!!!!!

Thanks everyone
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.