?
Solved

problem with insert, sql server and classic asp

Posted on 2011-02-14
16
Medium Priority
?
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 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