Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1026
  • Last Modified:

Classic ASP & MSSQL Insert Statement doens't execute.

The Insert statement below, doesn't execute.

If I add bad syntax for example:

rs2.Open "Insert  FAILONPURPOSE into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn4

The page doesn's fail. nothing happens.  if I add bad syntax for example.

rs2.Open FAILONPURPOSE "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn4


It fails as it should. why isn't it running inside the "" on the Insert statement?

<%
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

*****This insert doesn't execute **************
rs2.Open "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn


   rs.MoveNext
  loop
End If
%>
0
ParisBP
Asked:
ParisBP
  • 17
  • 16
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
right above your rs2.Open line, put this in:

Response.Write "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
Response.End

this will write the sql statement to the screen. When it does, try running it directly in sql and see if it does the insert.

also, you never create a second recordset, it should be:
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")
0
 
ParisBPAuthor Commented:
Thanks I previously had second recordsset. I tried alot of things before reaching out here.

anyways. it ignored everythig inbetween the""

<%
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

Response.Write  "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
Response.End

rs2.Open "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn4

   rs.MoveNext
  loop
End If
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
not sure what you mean by

anyways. it ignored everythig inbetween the""

did the statement write out to the screen?

i also notice you have my_conn4 in your open statement, is that a typo? you are also not closing the 2nd recordset after each use in your loop, so lets change it to:

sql = "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
set rs2 = my_conn.Execute( sql )
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ParisBPAuthor Commented:
Sorry I meaning.. in between the " "  no matter what I put in there. it doens't matter. bad syntax or good its just ignored.

The 4 was a type.
This is what I just tried to run. got nothing.
Appreciate the help.


<%
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

sql = "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
set rs2 = my_conn.Execute( sql )

rs2.Open "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn

   rs.MoveNext
  loop
End If
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try this for your code, if it works, then it's your IF statement at the very beginning that is returning false:

<%
If Choice="Select Promo To Duplicate" or 1 = 1 then     '-- this will make the if statement true all the time

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset") 

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

sql = "Insert into Funding  Values('193','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
set rs2 = my_conn.Execute( sql ) 

   rs.MoveNext
  loop
End If
%>

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
also, try putting this right before the if statement, this will turn on error messages:

on error goto 0
0
 
Dave BaldwinFixer of ProblemsCommented:
You're not using a field list before your 'Values'.  I realize it is not necessary... if your values list exactly matches your fields.  You might want to check that.
0
 
ParisBPAuthor Commented:
Added error goto.. NO errors
Confirmed values numbers are accurate. tested sql statement directly on SQL server.

Fi I do this it fails
rs2.Open FAIL "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn

Fi I do this it doens't fail
rs2.Open "Insert into FAIL Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn



Here is the latest. Very Strange.

<%
on error goto 0
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

rs2.Open "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn

   rs.MoveNext
  loop
End If
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
and did you try changing

rs2.Open "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn

to

sql = "Insert into FAIL Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
set rs2 = my_conn.Execute( sql )
0
 
ParisBPAuthor Commented:
Yes, .when I do that. it page runs with no errors.

Its' acting like it isn't executing the insert statement.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you paste the exact sql being run?
0
 
ParisBPAuthor Commented:
This runs with no results

rs2.Open "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn


this runs with no results
rs2.Open "Insert FAIL into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn

This Fails
rs2.Open Fail "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change

sql = "Insert into FAIL Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
set rs2 = my_conn.Execute( sql )

to

sql = "Insert into FAIL Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
Response.Write sql
Response.End
set rs2 = my_conn.Execute( sql )

and post whatever appears on the screen. I'm guessing you have error suppression turned on at the server level. so i want to see if the actual sql statement contains any errors.
0
 
ParisBPAuthor Commented:
Ran with no results  
I have triple checked the SQL statement on tne SQL server. Its a pretty simple statement.

<%
on error goto 0
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Funding where promoID='" & promoID & "'", my_conn
do While not rs.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

sql = "Insert into FAIL Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')"
Response.Write sql
Response.End
set rs2 = my_conn.Execute( sql )


   rs.MoveNext
  loop
End If
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
did you ever try it without the if statement at the top?

If Choice="Select Promo To Duplicate" then
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
also, i asked for the sql statement because it's very easy to overlook smaill things in a sql statement, especially the way you're doing it (concatenating a sql statement instead of using a parameterized query). since you're in a loop, have you tested each set of data in the loop?
0
 
ParisBPAuthor Commented:
Commented out If Statement

Tested both sets of date in the loop.

Select * pulls data from SQL

insert puts data into SQL
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, so there is something wrong with your if statement. where do you set the Choice variable? that's the cause of your problem
0
 
ParisBPAuthor Commented:
Its passed from a form on a page prior

I have this same if statement that executes fine just before the posted one on the page.

<%
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Insert into Promotions Values('" & proID & "','" & promoDescription & "','" & promoCreateDate & "', '" & promoEnteredBy & "', '" & promoVehicle & "', '" & promoStart & "','" & promoEnd & "','" & idPeriod & "', '" & idType & "', '" & repeat & "', '" & exact & "', '" & promoLinked & "', '" & promoBillDebit & "', '" & promoXCost & "', '" & promoCap  & "', '" & promoCapAmount & "', '" & promoComfirmed & "', '" & promoComfirmedBy & "', '" & promoComments & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn
End If
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I have this same if statement that executes fine just before the posted one on the page.

that's irrelevant. it's obviously not working now, as we just proved it by commenting out the if statement and having it work.

Since I don't know your page structure, I can't say WHY it's not working, all I can do is prove to you it isn't, which I've done. What I would do is put the following in right before the non-working if statement:

Response.Write "The CHOICE variable = " & Choice
Response.End
If Choice="Select Promo To Duplicate" then

what does that write out to the screen?
0
 
ParisBPAuthor Commented:
I know not an easy task.
it reads choice
Select+Promo+To+DuplicateThe CHOICE variable = Select Promo To Duplicate
0
 
ParisBPAuthor Commented:
Top to Bottom Here is the logic of the page.

Basically. We have a couple of sets of tables. we just want to pull from one promoID and copy to another promoID

The part of pulling promotion info and putting into new table under new ID works fine.

Then puliing items from Funding and inserting them into Funding under the NewpromoID is the goal. I will try to comment in.


Pull info from previous page.
<%
choice=Request.QueryString("choice")
proID=Request.QueryString("proID")
promoID=Request.QueryString("promoID")
promoDescription=Request.QueryString("promoDescription")
promoCreateDate=Request.QueryString("promoCreateDate")
promoEnteredBy=Request.QueryString("promoEnteredBy")
promoVehicle=Request.QueryString("promoVehicle")
promoStart=Request.QueryString("NewpromoStart")
promoEnd=Request.QueryString("NewpromoEnd")
idPeriod=Request.QueryString("idPeriod")
idType=Request.QueryString("idType")
repeat=Request.QueryString("repeat")
exact=Request.QueryString("exact")
promoLinked=Request.QueryString("promoLinked")
promoBillDebit=Request.QueryString("promoBillDebit")
promoXCost=Request.QueryString("promoXCost")
promoCap=Request.QueryString("promoCap")
promoCapAmount=Request.QueryString("promoCapAmount")
promoComfirmed=Request.QueryString("promoComfirmed")
promoComfirmedBy=Request.QueryString("promoComfirmedBy")
promoComments=Request.QueryString("promoComments")
proName=Request.QueryString("proName")
%>

Insert this info into table creating a new promoID

<%
If Choice="Select Promo To Duplicate" then

set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Insert into Promotions Values('" & proID & "','" & promoDescription & "','" & promoCreateDate & "', '" & promoEnteredBy & "', '" & promoVehicle & "', '" & promoStart & "','" & promoEnd & "','" & idPeriod & "', '" & idType & "', '" & repeat & "', '" & exact & "', '" & promoLinked & "', '" & promoBillDebit & "', '" & promoXCost & "', '" & promoCap  & "', '" & promoCapAmount & "', '" & promoComfirmed & "', '" & promoComfirmedBy & "', '" & promoComments & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn
End If
%>

Digging out the New Promo ID
 <%
set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select promoID from Promotions where proId='" & proId & "' and promoDescription='" & promodescription & "' and promoStart='" & promoStart & "' and promoEnd='" & promoEnd & "'", my_conn
NewpromoID=rs("promoID")
%>

No pull items from OLD promoID
<%
If Choice="Select Promo To Duplicate" then
set my_Conn2 = Server.CreateObject("ADODB.Connection")
      my_Conn2.Open strConnString
set rs2 = Server.CreateObject("ADODB.recordset")
promoID=request.querystring("promoID")

rs2.Open "Select * from Funding where promoID='" & promoID & "'", my_conn2
do While not rs2.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")


Insert into Funding with newPromoID
set my_Conn3 = Server.CreateObject("ADODB.Connection")
      'my_Conn.Errors.Clear
      my_Conn3.Open strConnString
set rs3 = Server.CreateObject("ADODB.recordset")

rs3.Open "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn3

   rs2.MoveNext
  loop
End If
%>





 <%
' proId=Request.QueryString("proID")
set my_Conn = Server.CreateObject("ADODB.Connection")
      my_Conn.Open strConnString
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Profiles where proId='" & proId & "'", my_conn
proName=rs("ProName")
%>
<Font color="Black" size="+2" > Promotion <% Response.Write(NewpromoID) %> for <% Response.Write(proName) %>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok i want you to get rid of the previous code I gave you and add in the code in bold text below and tell me what it spits out to the screen:

No pull items from OLD promoID
<%
If Choice="Select Promo To Duplicate" then
set my_Conn2 = Server.CreateObject("ADODB.Connection")
      my_Conn2.Open strConnString
set rs2 = Server.CreateObject("ADODB.recordset")
promoID=request.querystring("promoID")

rs2.Open "Select * from Funding where promoID='" & promoID & "'", my_conn2
Response.Write rs2.BOF & rs2.EOF
Response.End

do While not rs2.EOF
0
 
ParisBPAuthor Commented:
True True
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, that means that the statement below is not returning any results

rs2.Open "Select * from Funding where promoID='" & promoID & "'", my_conn2

check the promoID variable to make sure it's getting the value you expect
0
 
ParisBPAuthor Commented:
That helped alot. I has passing promoID twice. I cleaned that up. now I am getting
Item cannot be found in the collection corresponding to the requested name or ordinal.
but all those items exist in Funding.


<%
set my_Conn2 = Server.CreateObject("ADODB.Connection")
      my_Conn2.Open strConnString
set rs2 = Server.CreateObject("ADODB.recordset")
promoID=Request.QueryString("promoID")
response.write(promoID)
rs2.Open "Select * from Funding where promoID='" & promoID & "'", my_conn2


do While not rs2.EOF
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")

set my_Conn3 = Server.CreateObject("ADODB.Connection")
      'my_Conn.Errors.Clear
      my_Conn3.Open strConnString
set rs3 = Server.CreateObject("ADODB.recordset")

rs3.Open "Insert into Funding Values('"  & proID & "','" & NewpromoID & "','" & funWho & "', '" & funAmount & "', '" & funCost  & "', '"  & subTime & "', '" & subDate & "', '" & subWho & "')", my_conn3

   rs2.MoveNext
  loop
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
what line number does the error refer to?
0
 
ParisBPAuthor Commented:
funID=rs("funID")
funWho=rs("funWho")
funAmount= rs("funAmount")
funCost=rs("funCost")
proID=rs("proID")


each one of I commented out the one in first in line.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post a screenshot of what it looks like when you run it directly in sql server? i want to see what columns are returned

I have to head out, I'll try to respond later tonight if you post back, otherwise it'll be in the morning. hang in there, we;ll get it working!
0
 
ParisBPAuthor Commented:
Table is definately correct.  attached wi word doc. and thanks again.
New-Microsoft-Office-Word-Docume.docx
0
 
ParisBPAuthor Commented:
was missing rs2 ok.. on to next steps.
0
 
ParisBPAuthor Commented:
That was it.. aswesome thanks..
0
 
ParisBPAuthor Commented:
Great back and forsth problem solving.

Thanks Man.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
My pleasure :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 17
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now