problem in updating DB using console application

[Claudia Fong]

Hi,

I'm using console application to connect to a db and I managed to use
the SELECT statement to display records in my DB but I also need to
UPDATE some fields in my table but after I run the code below, it didn't
update the record..

Can someone tell me?

Cheers

Claudi

SqlConnection cnn1 = new SqlConnection() ;
cnn1.Connection String = "Integrated Security=SSPI;" +
"Initial Catalog=Library DB;" +
"Data Source= .\\SQLExpress";
try
{

cnn1.Open();

SqlCommand cnn1Command = new SqlCommand();
cnn1Command.Con nection = cnn1;
cnn1Command.Com mandText = "UPDATE RESERVED SET ISBN = '"
+ isbn_number + "' , UserID = '" + userID + "'";

SqlDataReader dataReader = cnn1Command.Exe cuteReader();--What should I
put in here in order the UPDATE to work?

dataReader.Clos e();
cnn1.Close();

}
catch (Exception ex)
{
Console.WriteLi ne("Error accessing the database: " +
ex.Message);
}
Cheers!

Claudi

*** Sent via Developersdex http://www.developersdex.com ***

[zacks]

On Jun 11, 10:33 am, Claudia Fong <cdolphi...@yah oo.co.ukwrote:

Hi,

I'm using console application to connect to a db and I managed to use
the SELECT statement to display records in my DB but I also need to
UPDATE some fields in my table but after I run the code below, it didn't
update the record..

Can someone tell me?

Cheers

Claudi

SqlConnection cnn1 = new SqlConnection() ;
cnn1.Connection String = "Integrated Security=SSPI;" +
"Initial Catalog=Library DB;" +
"Data Source= .\\SQLExpress";
try
{

cnn1.Open();

SqlCommand cnn1Command = new SqlCommand();
cnn1Command.Con nection = cnn1;
cnn1Command.Com mandText = "UPDATE RESERVED SET ISBN = '"
+ isbn_number + "' , UserID = '" + userID + "'";

SqlDataReader dataReader = cnn1Command.Exe cuteReader();--What should I
put in here in order the UPDATE to work?

dataReader.Clos e();

cnn1.Close();

}
catch (Exception ex)
{
Console.WriteLi ne("Error accessing the database: " +
ex.Message);
}

Instead of:
SqlDataReader dataReader = cnn1Command.Exe cuteReader();

use:

cnn1Command.Exe cuteNonQuery();

[Claudia Fong]

Hi,

Thanks for the tip! What if I want to change to INSERT INTO

cnn1Command.Com mandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_num ber)"; --userID and isbn_number are
variable and I can't use inside the context.. what should I put instead
of variables? The values are input by the user...
Cheers!

Claudi

*** Sent via Developersdex http://www.developersdex.com ***

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

You want to use a parameterized query:

cnn1Command.Com mandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, @userID, @isbn_number)";
cnn1Command.Par ameters.AddWith Value("@userID" , myLiveUserId variable)
.... etc
cnn1Command.Con nection.Open();
cnn1Command.Exe cuteNonQuery();
cnn1Command.Con nection.Close() ;

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Claudia Fong" wrote:

Hi,

Thanks for the tip! What if I want to change to INSERT INTO

cnn1Command.Com mandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_num ber)"; --userID and isbn_number are
variable and I can't use inside the context.. what should I put instead
of variables? The values are input by the user...
Cheers!

Claudi

*** Sent via Developersdex http://www.developersdex.com ***

[zacks]

On Jun 11, 12:25 pm, Claudia Fong <cdolphi...@yah oo.co.ukwrote:

Hi,

Thanks for the tip! What if I want to change to INSERT INTO

cnn1Command.Com mandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_num ber)"; --userID and isbn_number are
variable and I can't use inside the context.. what should I put instead
of variables? The values are input by the user...

First off, INSERT is like UPDATE, execute it with the ExecuteNonQuery
method call.

As one responder mentions, you could accomplish that with a
parameterized query, but, IMHO, the main reason to use a parameterized
query is when one or more of the columns is a Text datatype. You can
accomplish the same thing with:

cnn1Command.Com mandText = string.Format(" INSERT INTO RESERVED
(ReservedID, UserID,
ISBN) VALUES (1, {0},{1})", userID, isbn_number);

Also, bear in mind this will only work if both columns are a non-
string datatype. Any column that is a string datatype would require
single quotes areound the value. For example, if userID is a VarChar,
then the statement would be:

cnn1Command.Com mandText = string.Format(" INSERT INTO RESERVED
(ReservedID, UserID,
ISBN) VALUES (1, '{0}',{1})", userID, isbn_number);

[Jeremy Shovan]

Wow, You should always use parameterized queries!!!
<za***@construc tion-imaging.comwrot e in message
news:11******** *************@q 66g2000hsg.goog legroups.com...

On Jun 11, 12:25 pm, Claudia Fong <cdolphi...@yah oo.co.ukwrote:

>Hi,

Thanks for the tip! What if I want to change to INSERT INTO

cnn1Command.Co mmandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_num ber)"; --userID and isbn_number are
variable and I can't use inside the context.. what should I put instead
of variables? The values are input by the user...

First off, INSERT is like UPDATE, execute it with the ExecuteNonQuery
method call.

As one responder mentions, you could accomplish that with a
parameterized query, but, IMHO, the main reason to use a parameterized
query is when one or more of the columns is a Text datatype. You can
accomplish the same thing with:

cnn1Command.Com mandText = string.Format(" INSERT INTO RESERVED
(ReservedID, UserID,
ISBN) VALUES (1, {0},{1})", userID, isbn_number);

Also, bear in mind this will only work if both columns are a non-
string datatype. Any column that is a string datatype would require
single quotes areound the value. For example, if userID is a VarChar,
then the statement would be:

cnn1Command.Com mandText = string.Format(" INSERT INTO RESERVED
(ReservedID, UserID,
ISBN) VALUES (1, '{0}',{1})", userID, isbn_number);