Re: Update Command - Please Help - C# Discussion Boards

Update Command - Please Help

12-Oct-04 11:12

You can P/Invoke SendMessage and send TCM_SETPADDING (0x132b) to increase the tab a little, or there's other messages documented in the Windows Common Controls (since almost every Windows Forms control encapsulates the Common Controls).

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

amdacc12-Oct-04 3:16

amdacc

12-Oct-04 3:16

Please Help.

I have a very small inventory database, where i call up details of an item in stock that i want to book out. All i need to do to book it out is add a Serial number, store and IP number. I can call up the record, but i can update it. When i look at Quick watch, the program has the new information, but i cant send it back to the database. Im using SQL 2000 and C# 2003. It compiles all ok, but it breaks when it is run. Any help would really be appreciated. Thanks

My Code is as follows:

private void btnBkOutSave_Click(object sender, System.EventArgs e)
{

SqlCommand DAUpdateCommand;

string sType = "";
string sBkInMake="";
string sBkInModel= "";
string sBkInSN= "";
string sIPAddress ="";
string sStore = "";
string sUssage = "";

string myConnectString = "Data Source=devserver2;Persist Security Info=False;Initial Catalog=storeInventory;User ID=sa;password=;";
SqlConnection myConnection = new SqlConnection(myConnectString);
myConnection.Open();

sType= txtBkInType.Text;
sBkInMake= txtBkInMake.Text;
sBkInModel= txtBkInModel.Text;
sBkInSN= txtBkInSN.Text;
sIPAddress =txtBkOutIP.Text;
sStore = cboBkOutStoreNo.Text;
sUssage = txtBkOutUssage.Text;

MyData.Tables[0].Rows[0]["IpAddress"] = sIPAddress;
MyData.Tables[0].Rows[0]["Store"] = sStore;
MyData.Tables[0].Rows[0]["Ussage"] = sUssage;

da = new SqlDataAdapter("Select * From Items", myConnectString);

DAUpdateCommand = new SqlCommand("UPDATE Items set IPAddress = '" + sIPAddress + "',Store = " + sStore + "',Ussage = " + sUssage + "' where id=" + MyData.Tables[0].Rows[0]["id"] + ")", myConnection);

DAUpdateCommand.Parameters.Add(new SqlParameter("@Store", SqlDbType.Int, 4));
DAUpdateCommand.Parameters["@Store"].SourceVersion= DataRowVersion.Original;
DAUpdateCommand.Parameters["@Store"].SourceColumn = sStore;

DAUpdateCommand.Parameters.Add(new SqlParameter("@IpAddress", SqlDbType.NVarChar,50));
DAUpdateCommand.Parameters["@IPAddress"].SourceVersion= DataRowVersion.Original;
DAUpdateCommand.Parameters["@IPAddress"].SourceColumn = sIPAddress;

DAUpdateCommand.Parameters.Add(new SqlParameter("@Ussage", SqlDbType.Text,16));
DAUpdateCommand.Parameters["@Ussage"].SourceVersion= DataRowVersion.Original;
DAUpdateCommand.Parameters["@Ussage"].SourceColumn = sUssage;

da.UpdateCommand = DAUpdateCommand;
da.Fill(MyData,"Items");
da.Update(MyData,"Items");

myConnection.Close();
MessageBox.Show("Saved");
Clear();
}

Thanks again

Angela

Colin Angus Mackay12-Oct-04 3:43

Do you want to know more?

12-Oct-04 3:43

Angela Dick wrote:
User ID=sa;password=;";

Please tell me that you deleted the password for security and are not actually running your SQL Server's sa account without at least the protection of a password. You know that dialog when you installed SQL Server and it asked for an initial password for the sa account it had a checkbox for a blank password with the words "not recommended" next to it, it really really isn't recommended.

Your code seems to be incredibly confused. You seem to have meshed a variety of different techniques together. For instance, you are adding parameters to your update command, but the SQL itself takes no parameters as you are directly injecting the values from your controls into the database which leaves you open to a SQL Injection Attack[^]. Finally, you are filling your dataset, overwriting the values you diligently inserted into the table at the top of your method, then you call update.

Angela Dick wrote:
It compiles all ok, but it breaks when it is run.

So, how does it actually break? Is an exception thrown? Does the information not get updated? or...

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

amdacc12-Oct-04 9:22

amdacc

12-Oct-04 9:22

hi, Thanks for getting back to me. Dont wory about the password, its only set up like that for beginners like me to play. Its not used for anything important.

I have been looking at code from all over the internet, and since i started this, non of the examples i have used have helped. Thats why its mis-matched. i have now managed to blag some help here and i have this

SqlCommand cmd;
string sType = "";
string sBkInMake="";
string sBkInModel= "";
string sBkInSN= "";
string sIPAddress ="";
string sStore = "";
string sUssage = "";

string myConnectString = "Data Source=devserver2;Persist Security Info=False;Initial Catalog=storeInventory;User ID=sa;password=;";
SqlConnection myConnection = new SqlConnection(myConnectString);

if (txtBkInSN.Text.Length != 0)
{
myConnection.Open();

sType= txtBkInType.Text;
sBkInMake= txtBkInMake.Text;
sBkInModel= txtBkInModel.Text;
sBkInSN= txtBkInSN.Text;
sIPAddress =txtBkOutIP.Text;
sStore = cboBkOutStoreNo.Text;
sUssage = txtBkOutUssage.Text;

MyData.Tables[0].Rows[0]["IpAddress"] = sIPAddress;
MyData.Tables[0].Rows[0]["Store"] = sStore;
MyData.Tables[0].Rows[0]["Ussage"] = sUssage;

cmd = new SqlCommand("UPDATE Items " +
"set IPAddress = " + sIPAddress + " ,Store = " + sStore + " ,Ussage = " + "'" + sUssage + "' where SerialNo = '" + txtBkInSN.Text.Trim() + "'", myConnection);

da.UpdateCommand = cmd;
da.Fill(MyData,"Items");
da.Update(MyData,"Items");

myConnection.Close();
MessageBox.Show("Saved");
Clear();
}
else
MessageBox.Show("Please Find a Serial Number and complete relevant infromation before pressing save.");

I dont know if it is the best way to do it, but........ it seems to be working ok.

Thanks Again

Angela

Colin Angus Mackay12-Oct-04 11:02

12-Oct-04 11:02

In addition to what Heath has said, there is still redundant code here.

I would get rid of the DataSet altogether, unless you need it for something else later, and if you do then I'd still clean up a lot of this code.

First, they way you are performing the update does not require a SqlDataAdapter. So instead of setting the UpdateCommand to the SqlCommand you just created, why not just use SqlCommand.ExecuteNonQuery()

So,
da.UpdateCommand = cmd;
da.Fill(MyData,"Items");
da.Update(MyData,"Items");

is replaced with

cmd.ExecuteNonQuery();

If you do not required the dataset for anything else then I would zap the following lines also:
MyData.Tables[0].Rows[0]["IpAddress"] = sIPAddress;
MyData.Tables[0].Rows[0]["Store"] = sStore;
MyData.Tables[0].Rows[0]["Ussage"] = sUssage;

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Heath Stewart12-Oct-04 7:36

12-Oct-04 7:36

It's good that you're trying to used parameterized queries since SQL string concatenation leaves you wide open for SQL injection attacks - which are especially bad in your case since your executing commands as the 'sa' - NEVER do this; always use least required privileges, especially when invoked from the most dangerous place on the planet: the Internet.

But you're not using parameters correctly. You should read the documentation for the SqlCommand.Parameters property in the .NET Framework SDK so you know what you're doing. Your SQL expression should actually look like this (based on your parameter names you're using):

DAUpdateCommand = new SqlCommand(@"
  UPDATE Items SET IPAddress = @IPAddress, Store = @Store, Ussage = @Ussage
  WHERE id = @ID");

BTW, it's "usage" with one "s", not "ussage".

Additionally, you'll need to add @ID to your parameters list. Always use parameterized queries everywhere possible.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

amdacc12-Oct-04 9:27

amdacc

12-Oct-04 9:27

Hi, thanks for your help. Im new at this so a bit confused, and no I cant spell Smile | :)

Never mind.

I have managed to get it working, with some help.. i have put the code in my last reply, it isnt using the parameters as the girl helping me couldnt work it out either. Dont know if its the best way to do things.....

Thanks again

Angela

Colin Angus Mackay12-Oct-04 10:47

Do you want to know more?

12-Oct-04 10:47

Angela Dick wrote:
no I cant spell Never mind

Actually, it might sound daft but it is kind of important when you are working in a team environment - because if one member of the team creates code that is has spelling mistakes through it then others will either have to clean it up, or the mistake propagates causing confusion constantly between whether to use the correct spelling or not. I have to admit that I am not a saint in the spelling department, but I do my best and ensure that errors in spelling, which the compiler doesn't care about if you're consistent about it, are corrected as soon as possible.

Angela Dick wrote:
it isnt using the parameters as the girl helping me couldnt work it out either. Dont know if its the best way to do things.....

It isn't. You should always use parameters if you need to send in variable information rather than concatenate strings together. If you clicked the link[^] I gave you before there is some sample code showing a before and after senario with concatenation changing to a parameterised query. That might help you understand how to make a perametarised query.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

2Scoops13-Oct-04 22:57

2Scoops

13-Oct-04 22:57

All I can say is that I would hate to be a developer with no experience asking you for help. I used to frequent this web site quite alot as the help was always useful and straight to the point. These days though, the site has definately lost it's edge.

If Angela was looking for preventative measures again SQL injection attacks, then she would ask for them. Angela's question was obviously from a beginner, and for you to start mentioning preferred security measures was totally pointless.

If your aim was to be patronising and demorallising then you have succedded. But on the other hand, if your aim was to help an in-expreienced developer then you have failed miserably.

I myself make frequent spelling mistakes, even when emailing my client's I make mistakes. They don't point them out because they know at the end of the day I probably spent more productive time delivering a system free of bugs, rather than spending time spell checking every line of code I write.

BTW, have I spelt everything right Colin?

Heath Stewart12-Oct-04 10:56

Re: Crystal Reports Formulas

12-Oct-04 10:56

As Colin said, under no circumstances should you ever use string concatenation. Read the .NET Framework SDK documentation that got installed with Visual Studio or the .NET Framework SDK, or that's available on http://msdn.microsoft.com/library[^]. It's important to write correct code, not just write whatever you can get to compile. SQL injection attacks are a very serious and easily exploitable threat, and since your compiled C# source is in Intermediate Language (not machine code) it's very easy to read. The .NET Framework SDK itself even includes a disassembler named ildasm.exe and for those of us that can read IL (or those that cop-out and use decompilers like the famous .NET Reflector) it would be easy to compromise your code.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

Crystal Reports Formulas

NormDroid12-Oct-04 1:38

NormDroid

12-Oct-04 1:38

This there any way to access data from a command dynamically in a formula:

eg
formula = {Command.SalesFiguresP1}

(was the 1 is dynmic}

eg.

formunal = {"Command.SalesFiguresP" + ToText(var)}

?

Heath Stewart12-Oct-04 7:19

Re: Crystal Reports Formulas

12-Oct-04 7:19

This forum is for questions related to C# that are not specifically addressed by other forums on CodeProject (like ASP.NET questions - even though written in C# - should be asked in the ASP.NET forum). This is not a forum for Crystal Reports.

If you need help with Crystal Reports, I suggest you use their forums: http://support.businessobjects.com/forums/default.asp?ref=default.asp_wwwjump[^].

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

NormDroid12-Oct-04 20:36

NormDroid

12-Oct-04 20:36

Thanks you been most helpful, BTW: is "Developer Division Sustained Engineering" just a fancy name for the bug fixes team Poke tongue | ;-P

Select Statement

pat27088112-Oct-04 1:26

12-Oct-04 1:26

hello,

my method for retrieving data from the sql server ce database and display it in a data grid looks as follows:

dataSet = new DataSet("Jobs");

daptJobs = new SqlCeDataAdapter("Select Priority, Problem, EmailClient From JobDescription Order By Priority", strConn);
daptJobs.Fill(dataSet,"Jobs");
DataTable dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;

But i want to modify the Select Statement that Priority, Problem and EmailClient is only selected when UserEngineerId - field has a special value and the StartDate field is equal or after the Today Date.

The thing with the UserEngineerId works when i quote directly the value in the Select Statement but I want to quote a variable with the certain value..? And the thing with the Date is not really clear for me.

thx

patrick

Stanciu Vlad12-Oct-04 2:24

Stanciu Vlad

12-Oct-04 2:24

try the following

<br />
string selectString = <br />
   " Select " +<br />
   "    Priority, Problem, EmailClient " +<br />
   " From JobDescription " +<br />
   " Where UserEngenieerId = " + yourValueVariable + <br />
   "       And StartDate >= " + DateTime.Now.Date + <br />
   " Order By Priority ";<br />
<br />
daptJobs = new SqlCeDataAdapter(selectString);<br />
....<br />

and if the code is not acurate (mening that the date si not precise) onvert it with the sql convert function (you can find it in the sql bible or manual Wink | ;)

)

I hope you understand...
By the way... visit http://nehe.gamedev.net[^]

Heath Stewart12-Oct-04 7:13

12-Oct-04 7:13

NEVER use string concatenation to build SQL queries. ADO.NET supports parameterized queries and you should use them. What you're doing allows for easy SQL injection attacks.

Search previous comments for more reasons in the billions of times I've had to say this. Your code - and similar code you may write - is extremely insecure and does not follow good coding practices. Mike's reply right below yours mentions as much, too.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

Mike Dimmick12-Oct-04 2:47

Mike Dimmick

12-Oct-04 2:47

It's best to use parameterised queries, for example:

SqlCeCommand cmd = 
   new SqlCeCommand(
      "SELECT Priority, Problem, EmailClient " +
      "FROM JobDescription " +
      "WHERE UserEngineerId = ? AND StartDate >= ? " +
      "ORDER BY Priority" );
       
SqlCeParameter param1 = 
   new SqlCeParameter( "@userID", SqlDbType.Int );
param1.Value = userEngineerID;
cmd.Parameters.Add( param1 );
 
SqlCeParameter param2 =
   new SqlCeParameter( "@date", SqlDbType.DateTime );
param2.Value = DateTime.Today;
cmd.Parameters.Add( param2 );
 
cmd.Connection = new SqlCeConnection( strConn );
 
daptJobs = new SqlCeDataAdapter( cmd );

The two parameters substitute for the ? in the query string, in the order they're added to the Parameters collection.

Stability. What an interesting concept. -- Chris Maunder

pat27088112-Oct-04 3:14

12-Oct-04 3:14

That is impossible it does still not work, now I wrote it in that way, again without the date property:

dataSet = new DataSet("Jobs");
SlCeCommand cmd =
new SqlCeCommand(
"SELECT Priority, Problem, EmailClient " +
"FROM JobDescription " +
"WHERE UserEngineerId = ? " +
"ORDER BY Priority" );

string userEngineerId = "hattl70";
SqlCeParameter param1 =
new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar );

param1.Value = userEngineerId;
cmd.Parameters.Add( param1 );

cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter( cmd );

daptJobs.Fill(dataSet,"Jobs");

DataTable dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;

The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar.

Does anybody have anymore ideas what went wrong here? Frown | :(

best regards

patrick

Colin Angus Mackay12-Oct-04 3:21

Do you want to know more?

12-Oct-04 3:21

pat270881 wrote:
The SqlCeException occured again.
Does anybody have anymore ideas what went wrong here?

What are the details of the exception? There will be a message at least, some details about the state of the database and so on. These really help to track down problems.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

pat27088112-Oct-04 3:45

12-Oct-04 3:45

Okay the error notification looks as follows:

Unexpected Error in
MobileHelpDesk.exe
SqlCeException

SqlCeCommand::InitializeCommand+0xf9
SqlCeCommand::ExecuteCommand+0xa
SqlCeCommand::ExecuteReaderInternal+0x30
SqlCeCommand::ExecuteReader+0x13
SqlCeCommand::System.Data.IDbCommand.ExecuteReader+0x7
DbDataAdapter::Fill+0x37
DbDataAdapter::Fill+0x85
DbDataAdapter::Fill+0x11

Are anybody able to see with the help of this error what went wrong?? Frown | :(

best regards

patrick

Heath Stewart12-Oct-04 7:17

12-Oct-04 7:17

SQL Server supports named paramters. Use ? for OleDbCommand.

Your expression should look like this:

SqlCeCommand cmd = new SqlCeCommand(@"
  SELECT Priority, Problem, EmailClient
  FROM JobDescription
  WHERE UserEngineerId = @UserEngineerId
  ORDER BY Priority");

When you specify a variable-width field type like SqlDbType.NVarChar, you should also use the overloaded SqlCeParameter constructor (or SqlCeCommand.Parameters.Add method) to specify the number of characters:

SqlCeParameter id = cmd.Parameters.Add("@UserEngineerId", SqlDbType.NVarChar, 40);
id.Value = "hattl70";

Read the documentation for either SqlCeCommand.Parameters or SqlCommand.Parameters for more information and examples of how to use parameterized queries with SQL Server (CE).

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

pat27088112-Oct-04 7:36

12-Oct-04 7:36

Mr. Stewart i already tried the version with parameter, but the same error occurs again which i mentined in a previous posting. I wrote the code in that way: (UserEngineerId is EngineerUserId).

SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE EngineerUserId = @EngineerUserId
ORDER BY Priority");

SqlCeParameter id = cmd.Parameters.Add("@EngineerUserId", SqlDbType.NVarChar, 50);
id.Value = "hattl70";

dataSet = new DataSet("Jobs");

cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter(cmd);
daptJobs.Fill(dataSet,"Jobs");
dtabJobs = dataSet.Tables["Jobs"];

dgridDisplay.DataSource = dtabJobs;

The problem ist that i could no other error then the Where statement because if i don't use this statement the code works without problems.

I do not understand this phenomen... Frown | :(

regards

patrick

Heath Stewart12-Oct-04 7:52

12-Oct-04 7:52

Whether your statement works with or without parameters doesn't matter: NEVER, EVER use concatenated SQL expressions unless you like have credit card numbers stolen, employee hours changed, wages and salaries emailed throughout the company, and entire tables and even databases dropped. One guy who posted shortly after you makes it even easier for crackers since he's running the commands as the 'sa' account (which is even worse since he doesn't have a password for 'sa'). Always use the least amount of privileges required to run a particular command, even with SQL Server CE.

What exception is occuring and on what line? Does the user you're running under have permissions to the database objects required? Are you sure that EngineerUserId is a string (in most database designs anything with "id" is typically an integer or GUID). Are you sure that it's 50 characters wide and that it's an NVarChar (and not a VarChar, "N" means Unicode or "nationalized").

There could also be a problem since your DataSet and DataTable share the same name. If you don't care about the DataSet name, then don't name it (just use default instantiation, i.e. new DataSet()).

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles] [My Blog]

pat27088112-Oct-04 8:43