sql commands don't update tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

sql commands don't update tables

Author

Topic

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 03:45:25

Visual Studio 2005
i have insert and update statements. they don't produce any errors but no
changes happen in my tables. are there any possible reason for this. i used
sqlcommand and even stored procedures but still the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 03:49:46

We can do this either way.

1) We can guess outself bored, until we get the solution sometime next week
2) You can post the actual code you use, and we will have a look at it

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 03:54:40

quote:
Originally posted by yshie

Visual Studio 2005
i have insert and update statements. they don't produce any errors but no
changes happen in my tables. are there any possible reason for this. i used
sqlcommand and even stored procedures but still the same.

Yes..that happens when you make changes in db1 and check for the results in db2.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 05:10:46

this is my insert command.. let's take this first!
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Mydb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
conn.Open();

using (SqlCommand cmd = new SqlCommand("SPCust",conn))
{
string autoPW = GeneratePassword();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID",SqlDbType.VarChar).Value=textBoxID.Text;
cmd.Parameters.Add("@Passwd", SqlDbType.VarChar).Value = autoPW;

labelRegiStat.Text = "Registration Successful! \nPassword is " + autoPW;

}
}

CREATE PROCEDURE dbo.SPCust

(
@ID VARCHAR(50),
@Passwd VARCHAR(20),

)

AS
INSERT INTO Customers
VALUES(@ID, @Passwd)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 05:23:24

Where do you actually EXECUTE or CALL the stored procedure?
I think you need a "cmd.EXECUTE" somewhere...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 05:23:42

And some error checking code, of course...

Problem found

quote:
Originally posted by yshie

they don't produce any errors
no changes happen in my tables

Peter Larsson
Helsingborg, Sweden

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 05:58:04

i added cmd.ExecuteNonQuery(); after the parameters but still nothing happened

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 06:02:08

And still no errors?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 06:04:43

Add this temporarily to the dbo.SPCust SP.

CREATE PROCEDURE dbo.SPCust
(
@ID VARCHAR(50),
@Passwd VARCHAR(20)
)
AS

INSERT INTO Customers
VALUES(@ID, @Passwd)

INSERT INTO Customers
VALUES('Dummy record', 'No password')
GO

If you do this, and now when you have added the .ExecuteNonQuery method, do any record at all gets inserted into the Customers table?

And one more thing!
In the customers table, are these two the only columns? Are the other, mandatory (NOT NULL), columns?

Peter Larsson
Helsingborg, Sweden

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 07:07:59

yes!
still no changes.
i think this is not a usual problem... because i already tried all alternatives!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 07:10:07

If you run SP from the Query Analyser, do you get records inserted in the table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 07:18:38

Maybe some USER permission error?

Peter Larsson
Helsingborg, Sweden

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 07:44:53

i tried QA and they are working good

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 07:46:37

And you use the same USER privileges in the application when connecting to the SAME server?

Peter Larsson
Helsingborg, Sweden

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 08:44:08

im sorry, im not familiar with this. how will i check?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 08:48:01

You are using INTEGRATED SECURITY when connection from your application. That means you are logging on to the SQL Server with your network account. This account most probably do not have INSERT permissions to the table.

When you are using SSMS or QA, you are probably using the SA account, right?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 08:48:36

Are you using same login credentials (user name & password) when running SP from Query Analyser and front-end?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 08:52:07

Oh man! This topic can go on forever...

Peter Larsson
Helsingborg, Sweden

yshie
Starting Member

28 Posts

Posted - 2007-03-28 : 09:33:16

ok, i'll work on that. tnx 4 the idea and i'm sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources