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.ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=LibraryDB;" +
"Data Source= .\\SQLExpress";
try
{

cnn1.Open();

SqlCommand cnn1Command = new SqlCommand();
cnn1Command.Connection = cnn1;
cnn1Command.CommandText = "UPDATE RESERVED SET ISBN = '"
+ isbn_number + "' , UserID = '" + userID + "'";

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

dataReader.Close();


cnn1.Close();

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


Cheers!

Claudi

 

[zacks]

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.ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=LibraryDB;" +
"Data Source= .\\SQLExpress";
try
{

cnn1.Open();

SqlCommand cnn1Command = new SqlCommand();
cnn1Command.Connection = cnn1;
cnn1Command.CommandText = "UPDATE RESERVED SET ISBN = '"
+ isbn_number + "' , UserID = '" + userID + "'";

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

dataReader.Close();

cnn1.Close();

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

Instead of:
SqlDataReader dataReader = cnn1Command.ExecuteReader();

use:

cnn1Command.ExecuteNonQuery();

 

[Claudia Fong]

Hi,

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

cnn1Command.CommandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_number)"; --> 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

 

[Guest]

You want to use a parameterized query:

cnn1Command.CommandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, @userID, @isbn_number)";
cnn1Command.Parameters.AddWithValue("@userID", myLiveUserId variable)
.... etc
cnn1Command.Connection.Open();
cnn1Command.ExecuteNonQuery();
cnn1Command.Connection.Close();

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

 

[zacks]

Hi,

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

cnn1Command.CommandText = "INSERT INTO RESERVED (ReservedID, UserID,
ISBN) VALUES (1, userID,isbn_number)"; --> 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.CommandText = 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.CommandText = string.Format("INSERT INTO RESERVED
(ReservedID, UserID,
ISBN) VALUES (1, '{0}',{1})", userID, isbn_number);

 

[Jeremy Shovan]

Wow, You should always use parameterized queries!!!