Solved

problem with insert, sql server and classic asp

Posted on 2011-02-14
16
566 Views
Last Modified: 2012-05-11
here is my problem: i have two classic asp pages on the same server on the same website, both perform an identical INSERT operation on the same db (MSSQL 2005 Express). however, one performs it perfectly and the other does not. the one that fails does not generate any errors, rather it does create the new row, but it does not populate the row with all the values specified in the query.

the query generated by the ASP pages is as follows:

INSERT INTO SomeTable (column1,column2,column3) VALUES (value1,'value2',value3)

column1 and column3 are integer and column 2 is varchar. on the failing operation, the integer columns work fine, but the varchar column shows up blank in the DB. not NULL, mind you, but blank.

why would a query (identical!!!) work on one page and not another? both use the same connection string...

anyway, any help would be greatly appreciated.
0
Comment
Question by:kommgroup
  • 7
  • 4
  • 2
  • +2
16 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890682
Just trying to show some light in darkness...

Is this query a example ?

INSERT INTO SomeTable (column1,column2,column3) VALUES (value1,'value2',value3)

Is value2 is a parameter or value ?
If parameter, you need to remove single quotes around it, right?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890699
Compare the two page's relevant coding ? Any difference ?
0
 

Author Comment

by:kommgroup
ID: 34890814
no coding differences to speak of. i did a response.write on the sql statements and they are identical

yes, that is an example query that is structured the same way as my query, and yes, value2 is a value and not a parameter...
0
 
LVL 18

Expert Comment

by:chrismc
ID: 34894856
We would probably need to see the actual SQL code generated.

But if you are not getting a failure then however 'value2' is being passed to SQL is not working on one page and I suggest you try tracking backwards to see where it's being lost.

If the value is truly passed to SQL, it won't lose it, it has to be earlier on.
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34896345
I agree with chrismc,
If the new row is being created but the values are not present (empty fields), then the SQL statement isn't the issue, it is most likely the empty variables.

How are you passing the data to the failed page?

-Slim
0
 

Author Comment

by:kommgroup
ID: 34897404
the data is being passed from a form (post) and collected into one big variable that holds the sql statement... once i started having issues, i did a response.write of the variable that holds the sql statement and it looked right... i even pasted it directly into sql server and it executed properly, all fields populated.

does anyone have any suggestions on how to trace this?
0
 
LVL 4

Expert Comment

by:Slim81
ID: 34897623
I think we will need to see some code to better help you.

Can you paste your code and your response.write results?
0
 

Author Comment

by:kommgroup
ID: 34897690
okey-doke. here goes (a couple things are scrubbed, i hope you don't mind):

addacctsql = "INSERT INTO TableName (id,account,accountvalue) VALUES (" &
        request.form("user") & ",'" & encrypt(ucase(request.form("addacct")))) & "',0)"
response.write addacctsql
conn.execute addacctsql

addacctsql writes: INSERT INTO TableName (id,account,accountvalue) VALUES (27,'scrubbed',0)
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 18

Expert Comment

by:chrismc
ID: 34898186
I understand you might need to hide sensitive info. So that's ok.

The encrypt function would be my suspect.
Can you try it without encrypting to prove whether that is the culprit or not?
0
 

Author Comment

by:kommgroup
ID: 34898198
that's a good idea (although the encrypt function works everywhere else!)

i'll post back shortly
0
 

Author Comment

by:kommgroup
ID: 34898294
no dice!

i even just hard coded the whole query into the variable (no encryption, no form data, just static values) and it did the same thing!

i tried changing the variable name, that didn't work...

i am pretty confused. and exasperated. i worried about the safety of my hardware and the people around me...
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 34899276
Is there some particular reason you're building your sql query and passing it from page to page instead of just passing variable values around and then building your sql statement only right before executing it?

That would make your debug go a little easier, because you'd be able to see what your variable values are right then and there.
0
 
LVL 18

Accepted Solution

by:
chrismc earned 250 total points
ID: 34901024
Best practice for SQL Server is to create a stored procedure to do the insert and your screen would pass the variables as parameters into it.

One of the reasons it's best practice is because it allows you to debug the SQL side much more easily.
The other is that it's far more efficient as no compiling of SQL statements needs to be done when you run a screen.

Can you check there is no weird control character getting into the second script?
0
 

Author Comment

by:kommgroup
ID: 34901065
@muffybunny - i don't think i am doing what you describe... I am building the statement using data from a form, but i am not passing the sql from page to page... at least, i don't think i am.

@chrismc - good advice. let me have a look and see what i can accomplish. as far as any weird characters getting in there, i don't think there are... i mean, i copied the exact query into sql server console and it ran fine!
0
 

Author Closing Comment

by:kommgroup
ID: 35386651
i ended up rebuilding the page and the code starting working, so i really have no idea. however, i have started implementing stored procedures and it is working out, so kudos and many thanks to all.
0
 
LVL 18

Expert Comment

by:chrismc
ID: 35386709
Thanks, appreciated.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

22 Experts available now in Live!

Get 1:1 Help Now