Sunday, February 12, 2012

Beginner @IDENTITY question

Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.

Is @.IDENTITY somehow saved in the session and my remote servers session settings?? are off?

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @.ID = @.@.IDENTITY";

Thanks in advance for any responses.
-WileyHi Wiley,

First, you should have a ; between the statements:

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "'); select @.ID = @.@.IDENTITY";

Second, you should probably be using SCOPE_IDENTITY instead of @.@.IDENTITY. The two are similar, but SCOPE_IDENTITY returns values inserted only within the current scope. The problem with @.@.IDENTITY is that you might get the ID value from another insert operation. Check out SQL Server Books Online for a more complete description of the issue.

But that doesn't explain your problem. My best guess is that the ID field isn't defined as an identity field in the database on the remote server. That's the first thing to check.

There is a slight chance that SET IDENTITY_INSERT is off in the database. I don't recall that you can set that as the normal setting, but something in the connection might be setting it. Definitely a longshot.

Third,DON'T USE DYNAMIC SQL THIS WAY!!!!! It opens up SQL injection attacks, particularly for fields that are obviously user input. If you don't know the issues, just ask. You are setting yourself up to have your app hacked and attacked.

Don|||Hello,
Part of my problem was that I created the database locally from a .dat file in a database called Resume. Everthing worked great. When I upoaded the tables to my remote assigned database which is called "mydatabase" not Resume I ran into permission problems becuase locally the owner was dbo and remotely the owner was "mydatabase". Also some of the ID fields were not set to IDENTITY.

I basically have my application working(sort of ) now but I have to allow NULLS on every column that allows them in every table for anything to (sort of ) work -which I DID NOT have to do locally and I can't figure out why, maybe someone can shed some light on that for me.

Also, in reference to SQL injection attacks that you mentioned above , maybe you can point me to a link on ms sql security and explain to me what is wrong with using the dynamic sql statement in the way i did. I definitely don't want to start using asp.net and start off on the wrong foot opening myself up for attacks.

thanks,
-Wiley|||there's a lot of info about sql inject that you can find on the web. some of them are:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=533341

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

google rocks!|||wrong text on the first link. my mistake.

No comments:

Post a Comment