Solved

Classic ASP & MSSQL  Insert Statement doens't execute.

Posted on 2013-11-11
34
899 Views
Last Modified: 2013-11-11
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
Comment
Question by:ParisBP
  • 17
  • 16
34 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639283
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
 

Author Comment

by:ParisBP
ID: 39639332
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639344
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
 

Author Comment

by:ParisBP
ID: 39639378
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639385
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639389
also, try putting this right before the if statement, this will turn on error messages:

on error goto 0
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39639428
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
 

Author Comment

by:ParisBP
ID: 39639548
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639567
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
 

Author Comment

by:ParisBP
ID: 39639583
Yes, .when I do that. it page runs with no errors.

Its' acting like it isn't executing the insert statement.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639594
can you paste the exact sql being run?
0
 

Author Comment

by:ParisBP
ID: 39639729
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639747
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
 

Author Comment

by:ParisBP
ID: 39639776
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639795
did you ever try it without the if statement at the top?

If Choice="Select Promo To Duplicate" then
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639801
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
 

Author Comment

by:ParisBP
ID: 39639806
Commented out If Statement

Tested both sets of date in the loop.

Select * pulls data from SQL

insert puts data into SQL
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 32

Expert Comment

by:Big Monty
ID: 39639813
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
 

Author Comment

by:ParisBP
ID: 39639829
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639850
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
 

Author Comment

by:ParisBP
ID: 39639866
I know not an easy task.
it reads choice
Select+Promo+To+DuplicateThe CHOICE variable = Select Promo To Duplicate
0
 

Author Comment

by:ParisBP
ID: 39639931
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639948
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
 

Author Comment

by:ParisBP
ID: 39639964
True True
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39639973
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
 

Author Comment

by:ParisBP
ID: 39640023
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39640031
what line number does the error refer to?
0
 

Author Comment

by:ParisBP
ID: 39640035
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
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39640049
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
 

Author Comment

by:ParisBP
ID: 39640077
Table is definately correct.  attached wi word doc. and thanks again.
New-Microsoft-Office-Word-Docume.docx
0
 

Author Comment

by:ParisBP
ID: 39640100
was missing rs2 ok.. on to next steps.
0
 

Author Comment

by:ParisBP
ID: 39640105
That was it.. aswesome thanks..
0
 

Author Closing Comment

by:ParisBP
ID: 39640107
Great back and forsth problem solving.

Thanks Man.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39640113
My pleasure :-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now