Tuesday, February 14, 2012

Database UPDATE problem

Just a question. Im really

wondering why my sample database program wont update. It will say

that it has updated the actual database but when i double check, it

didnt. I am not using the command builder coz i want my own sql

codes. When I test using the query builder, i will see the data being

updated or added but when i use my own program, it really wont.

Actually i have this one sample database program that updates ok. I

just dont know why I cant do it again. Im really pissed off thinking

abt it the whole night.

I ommitted the INSERT and

DELETE commands since they dont work too. here is my program. PLS

HELP.

using

System;

using

System.Collections.Generic;

using

System.ComponentModel;

using

System.Data;

using

System.Data.SqlClient;

using

System.Drawing;

using

System.Text;

using

System.Windows.Forms;

namespace

test3_database

{

public

partial class

Form1 : Form

{

public

Form1()

{

InitializeComponent();

}

private

void Form1_Load(object

sender, EventArgs e)

{

da_conn.Fill(ds_addressbook1, "book1");

}

private

void

book1BindingNavigatorSaveItem_Click(object

sender, EventArgs e)

{

SqlCommand

sql_UPDATE = new SqlCommand(

"UPDATE

book1 SET "

+ "Name

= @.txtName, "

+ "Mobile

= @.txtMobile, "

+

"Landline = @.txtLandline, "

+ "Note

= @.txtNote"

+ "WHERE

"

+ "(Name

= @.OldName) AND "

+ "(Mobile

= @.OldMobile) AND "

+

"(Landline = @.OldLandline) AND "

+ "(Note

= @.OldNote)");

//

assigning connection

sql_UPDATE.Connection

= conn;

//

UPDATE parameters

sql_UPDATE.Parameters.Add("@.txtName",

SqlDbType.NChar, 30, "Name");

sql_UPDATE.Parameters.Add("@.txtMobile",

SqlDbType.NChar, 12, "Mobile");

sql_UPDATE.Parameters.Add("@.txtLandline",

SqlDbType.NChar, 12, "Landline");

sql_UPDATE.Parameters.Add("@.txtNote",

SqlDbType.NChar, 200, "Note");

sql_UPDATE.Parameters.Add("@.OldName",

SqlDbType.NChar, 30,

"Name").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldMobile",

SqlDbType.NChar, 12,

"Mobile").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldLandline",

SqlDbType.NChar, 12,

"Landline").SourceVersion =

DataRowVersion.Original;

sql_UPDATE.Parameters.Add("@.OldNote",

SqlDbType.NChar, 200,

"Note").SourceVersion =

DataRowVersion.Original;

//

assigning UPDATE command

da_conn.UpdateCommand

= sql_UPDATE;

//

update attempt

try

{

da_conn.Update(ds_addressbook1);

MessageBox.Show("update

ok!");

}

catch

{

MessageBox.Show("update

ERROR!");

}

}

}

}

what exactly is your result, a messagebox that displays "update ERROR!"? You should add error handling to capture the error returned by sql server.|||i get "update ok!" message. and i wonder why because when u check the databse it is not updated.

thanks for the reply |||

Run profiler or debug to see what Update statement is fired againt the server, perhaps you have some condition why didn′t sort out the specified rows you wanted to update. It could be that 0 rows are being returned /getting updated but the Update is still valid and executed. Perhaps you have the chance to check the sql_update.Parameters["@.RowCount"].Value as well ?!

HTH, Jens Suessmeyer.

|||i set no condition to my proram. ill try your suggestion. thanks so much

how do u run that profile? im sorry. im very new to visual studio and sqlserver. im just a student.|||

Look in this post,there are some walkthroughs:

http://groups.google.de/group/microsoft.public.sqlserver.tools/browse_frm/thread/cceb1ebec48370b

HTH, JensSuessmeyer.

|||SQL Express does not have Profiler. any alternative?

thanks.|||first i thank God for what happened last night.


second to all of

you guys, thank you for all your inputs. i found the bug. it was

hiding. hehehe. all your inputs have helped me finding it.



again, THANK YOU GUYS:


cgraus


JocularJoe


Jay Hickerson MS


KraGiE


Greg Yvkoff


Jens Suessmeyer