Link to home
Start Free TrialLog in
Avatar of pborregg
pborreggFlag for United States of America

asked on

Cannot get objConn.execute sql to insert into database

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
Avatar of exx1976
exx1976
Flag of United States of America image

We'd need to see the rest of the code, see how you implemented the objConn object, etc etc..
Do you have an error or it's just that your data is not inserted into the table?
Avatar of pborregg

ASKER

'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...
                        
                        
MikeQc: No error... the data simply isn't going into the table....
ASKER CERTIFIED SOLUTION
Avatar of Adam Menkes
Adam Menkes
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works!!!! By golly gee willikers it works...

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

Thanks everyone