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
Solved

problem with insert, sql server and classic asp

Posted on 2011-02-14
16
570 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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