Solved

problem with insert, sql server and classic asp

Posted on 2011-02-14
16
565 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

15 Experts available now in Live!

Get 1:1 Help Now