Insert query in code behind will work in Prod

Question

2.20/5 (3 votes)

See more:

Hi ,

I would like to know the Insert query in the VB.Net code behind will work in Production environment because which is working perfectly in other environments(DEV,QA) but not in production so I want to know is that because of any restriction that insert query in code behind is not working in production.

Code :-

VB

Dim _sqlDET As String = _
               "INSERT INTO dbo.tablename1 " & _
               "SELECT * FROM dbo.tablename2 " & _
               "WHERE ID=" & _ID & ""

Please help me to fix this ..

Posted 26-Jun-13 5:01am

prasy123

Updated 26-Jun-13 5:10am

CHill60

v3

Add a Solution

Comments

[no name] 26-Jun-13 11:07am

Other than not using a proper parameterized query to guard against SQL injection attacks, you have not described anything that needs to be "fixed"....

prasy123 26-Jun-13 11:31am

This is the query which I am using in code behind but I don't need any fix in this query .I want to know is there any restriction that will block this query to run in production environment.

[no name] 26-Jun-13 11:35am

How exactly would you think the we would know that? If your company, IT department, DBAs have not imposed any restrictions, then there none.

prasy123 26-Jun-13 11:40am

Ok.. Thanks for your replay and your time.

CHill60 26-Jun-13 11:09am

You probably do not have the appropriate permissions on the database to update the production data.

prasy123 26-Jun-13 11:28am

I have the permission to update,insert and delete data from production Db directly but I want to know if I am using the same query through code behind is there any restriction to run that code in production environment.

CHill60 26-Jun-13 11:47am

Unlikely. What error message do you get

prasy123 27-Jun-13 10:06am

I am getting the errors like SQL STATE_CTRL error: Conversion failed when converting the value 'RIS9' to data type int.
System .Data.SqlClient.SqlException(0x80131904):An explicit value for the identity column in table 'dbo.Tablename'can only be specified when a cloumn list is used and ISENTITY_INSERT is ON .

These are the error messages which I got from Production .This issue is happening in production so I am not able to recreate this issue to give a full error description sorry for that.

CHill60 27-Jun-13 11:03am

Sounds like there may be a schema difference between dev/qa and production. Instead of using select * from dbo.tablename2 try listing the columns that explicitly match dbo.tablename1.
[Edit] making sure that you don't include any id column that has the IDENTITY attribute on tablename1

prasy123 27-Jun-13 11:07am

Ok..Let me try that.. Thanks.

prasy123 27-Jun-13 12:09pm

Hi Chill60,

Your answer helped me to figure out the issue.The issue was no.of fields in the production table is different from the no.of fields in the dev and QA tables. That's why the insert query worked fine DEV and QA but not in Production.Now I removed the extra field from production table and the insert query is working fine in production also.

Thanks.

Balimusi 26-Jun-13 14:11pm

Just step through your code and update us with the error message.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-26T07:34:00

You need to admit that issue described as: "(insert query) is working perfectly in other environments(DEV,QA) but not in production" is not informative at all.

There are several reasons of above behavior...

As ThePhantomUpvoter has mentioned, rather than using query in code behind, use stored procedures[^] to avoid sql injection[^].

More:
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
Dynamic SQL & SQL injection[^]
How to: Execute a Stored Procedure that Returns Rows[^]
Using Stored Procedures with a Command[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]
How to: Create and Execute an SQL Statement that Returns No Value[^]

As Chill60 has mentioned, there could be an issue conflict with permissions (there are few levels), privileges, rules, etc.
Permissions (Database Engine)[^]
Permissions Hierarchy (Database Engine)[^]
Script to determine permissions in SQL Server 2005[^]
sql server difference between grant and with grant/[^]
beginner sql tutorial: sql grant, revoke, privileges and roles[^]

prasy123 · Answer 2 · 2013-06-27T06:13:00

I figured out the issue and fixed..

The issue was no.of fields in the production table is different from the no.of fields in the dev and QA tables. That's why the insert query in code behind worked fine DEV and QA but not in Production and now I removed the extra field from the production table then the insert query is working fine in production also.

Insert query in code behind will work in Prod

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0