[Newbie Question] Updating a record problem.

[DC]

My update code seems to execute without error but doesnt write the data
back to my database, am I leaving someting out? Do I need to tell the
DataAdapter to update the database somewhere?



private void btnUpdateSeminar_Click(object sender, System.EventArgs e)
{
string strConnectionString;
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='C:\\Program Files\\Common Files\\ODBC\\Data
Sources\\seminars.mdb';";

string UpdateSemSql = "UPDATE SeminarList SET Speaker = '" +
tbSpeaker.Text + "', SpeakerEmail = '" + tbSpeakerEmail.Text +
"', SpeakerInstitution = '" + tbSpeakerInstitution.Text +
"',SeminarTitle = '" + tbSeminarTitle.Text + "', SeminarDate = #" +
tbSeminarDate.Text + "#, SeminarTime = '" + tbSeminarTime.Text +
"',SeminarLocation = '" + tbSeminarLocation.Text + "', SeminarDetails =
'" + tbSeminarDetails.Text + "', SeminarHost = '" + tbSeminarHost.Text +
"',SeminarHostEmail = '" + tbSeminarHostEmail.Text + "', SeminarType =
'" + tbSeminarType.Text + "', OrganisationNotes = '" +
tbOrganisationNotes.Text +
"' WHERE ID = " + tbID.Text;

OleDbConnection Conn = new OleDbConnection(strConnectionString);
OleDbCommand cmd = new OleDbCommand(UpdateSemSql, Conn);

try
{
cmd.Connection.Open();
int numRecordsAffected = cmd.ExecuteNonQuery();
if (numRecordsAffected == 0)
{
Response.Write("Didnt update the record, an error has occured<BR><BR>");
}
else
{
Response.Redirect("main page url");
}
}

catch(Exception ex)
{
Response.Write("There was an exception error" + ex + "<BR><BR>");
}

finally
{
cmd.Connection.Close();
}

}
--
_______________________________________________

DC

"You can not reason a man out of a position he did not reach through reason"

"Don't use a big word where a diminutive one will suffice."

"A man with a watch knows what time it is. A man with two watches is
never sure." Segal's Law

 

[Nicholas Paldino [.NET/C# MVP]]

DC,

Is there a reason that you are not using a data set and a data adapter?
Creating SQL like this is a REALLY bad idea. If I entered the following
into the tbID field:

1; drop table seminarlist

You would be in big trouble.

As for why it is not updating, are you sure that the id is valid that
you are updating?

I would recommend creating a typed data set and let it generate the
parameterized query for you. It will be safer, and quite honestly, it will
reduce any errors you might get from trying to dynamically generate the SQL
yourself.

Hope this helps.

 

[DC]

DC,

Is there a reason that you are not using a data set and a data adapter?

No. Is that a beter way of running an update?

Creating SQL like this is a REALLY bad idea. If I entered the following
into the tbID field:

1; drop table seminarlist

You would be in big trouble.

Ok are ther any decent article about making updates more secure?

As for why it is not updating, are you sure that the id is valid that
you are updating?

Yep, its a perfectly valid ID

I would recommend creating a typed data set and let it generate the
parameterized query for you. It will be safer, and quite honestly, it will
reduce any errors you might get from trying to dynamically generate the SQL
yourself.

If I wasnt a total newbie at this I might understand what you mean, but
unforunately its all clear as mud at the moment. Whats the difference
between a normal and a typed Data Set?

Hope this helps.

Thanks for trying anyway. Ill slog onwards

--
_______________________________________________

DC

"You can not reason a man out of a position he did not reach through reason"

"Don't use a big word where a diminutive one will suffice."

"A man with a watch knows what time it is. A man with two watches is
never sure." Segal's Law

 

[Steve Walker]

Creating SQL like this is a REALLY bad idea. If I entered the
following into the tbID field:
1; drop table seminarlist
You would be in big trouble.

Ok are ther any decent article about making updates more secure?[/QUOTE]

That's an SQL injection attack, and you lay yourself open to the risk of
it anywhere you create SQL commands by concatenating user input into
strings of SQL. The obvious alternative is to use stored procedures
instead, so the user input is treated explicitly as parameters and not
embedded in the SQL code.

Alternatively you can try to ensure that the input is validated such
that it's much more difficult to do. Personally I'd always go the stored
procedure route because your SQL is then explicit. Easier to debug and
less subject to bugs in the first place.

If you google for "SQL injection attack" you'll find loads of articles
explaining the problem and ways around it.

 

[Chad Z. Hower aka Kudzu]

strings of SQL. The obvious alternative is to use stored procedures
instead, so the user input is treated explicitly as parameters and not
embedded in the SQL code.

SP's are fine, but you dont have to resort to SP's. Paramterized queries are not subject to SQL
injection either.


--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Empower ASP.NET with IntraWeb
http://www.atozed.com/IntraWeb/

 

[Nathan Kovac]

Put a breakpoint on
OleDbCommand cmd = new OleDbCommand(UpdateSemSql, Conn);

Then in your command window type "? UpdateSemSql"
Paste the results into query analyzer and see if you get an error there, or
if it says any records are modified.

-Nathan

 

[DC]

Should a subroutine like this run on the textboxes before submission to
the SQL query do the job?

public string StripQuotes(String strMainString)
{
String strStripQuotes;

strStripQuotes = strMainString.Replace("'", "");
strStripQuotes = strStripQuotes.Replace("select", "");
strStripQuotes = strStripQuotes.Replace("drop", "");
strStripQuotes = strStripQuotes.Replace(";", "");
strStripQuotes = strStripQuotes.Replace("--", "");
strStripQuotes = strStripQuotes.Replace("insert", "");
strStripQuotes = strStripQuotes.Replace("drop", "");
strStripQuotes = strStripQuotes.Replace("delete", "");
strStripQuotes = strStripQuotes.Replace("xp_", "");

return strStripQuotes;

}

DC,

Is there a reason that you are not using a data set and a data adapter?
Creating SQL like this is a REALLY bad idea. If I entered the following
into the tbID field:

1; drop table seminarlist

You would be in big trouble.

As for why it is not updating, are you sure that the id is valid that
you are updating?

I would recommend creating a typed data set and let it generate the
parameterized query for you. It will be safer, and quite honestly, it will
reduce any errors you might get from trying to dynamically generate the SQL
yourself.

Hope this helps.

--
_______________________________________________

DC

"You can not reason a man out of a position he did not reach through reason"

"Don't use a big word where a diminutive one will suffice."

"A man with a watch knows what time it is. A man with two watches is
never sure." Segal's Law