Paramerterized OleDB Query - C# Discussion Boards

Re: using System.Diagnostics.Stopwatch in an Button_Click Event: unusual results in specific test case

26-Jul-13 7:00

My machine is a bit slower than yours. I can't do 50MB in 30 second.

But, you're correct. I lost my head had on this one.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

BillWoodruff30-Jul-13 4:57

BillWoodruff

30-Jul-13 4:57

A man who knows he lost his head, and gracefully admits it, is a man who has, not only a head, but a heart Smile | :)

appreciatively, Bill

~
“This isn't right; this isn't even wrong." Wolfgang Pauli, commenting on a physics paper submitted for a journal

Re: using System.Diagnostics.Stopwatch in an Button_Click Event: unusual results in specific test case

Dave Kreskowiak30-Jul-13 5:46

Re: using System.Diagnostics.Stopwatch in an Button_Click Event: unusual results in specific test case

30-Jul-13 5:46

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Alan N25-Jul-13 4:27

Alan N

25-Jul-13 4:27

Ooh, that's difficult to spot. The mistake is subtle, easy to make and I'm sure I've done the same thing.

Stopwatch.Elapsed is a TimeSpan and TimeSpan.Milliseconds is just the millisecond component. If the measured value is less than 1 second then the result will be correct but either Stopwatch.Elapsed.TotalMilliseconds or Stopwatch.ElapsedMilliseconds would be what you really wanted.

Alan.

Re: using System.Diagnostics.Stopwatch in an Button_Click Event: unusual results in specific test case

BillWoodruff25-Jul-13 21:13

BillWoodruff

25-Jul-13 21:13

Alan, you nailed it ! And, how gently you broke the news, thanks Smile | :)

I was relieved to search all my code for the last several years, and find not one instance where I had made the same mistake made here: but, not relieved enough to get over the chagrin of making such a stupid mistake now.

I owe you an elephant-ride in the jungle in northern-Thailand.

yours, Bill

~
“This isn't right; this isn't even wrong." Wolfgang Pauli, commenting on a physics paper submitted for a journal

Paramerterized OleDB Query

gautamn199025-Jul-13 0:21

25-Jul-13 0:21

To suppress the warning CA2100 i am trying to create a parameterized query. By the following code.

string sheet_name=listBox_Select_a_sheet.SelectedItem.ToString();

OleDbCommand cmd = MyConnection.CreateCommand();

cmd.CommandText = "SELECT count(*) FROM [?]";

cmd.Parameters.AddWithValue("?", sheet_name);

cmd.ExecuteNonQuery();

The value of the ? is not replaced.

I have also tried the following

 OleDbCommand cmd = MyConnection.CreateCommand();

OleDbParameter para = new OleDbParameter("?", sheet_name);

cmd.Parameters.Add(para);

cmd.CommandText = "SELECT count(*) FROM [?]";

and

string strSql = "SELECT count(*) FROM [@Name]";
cmd.Parameters.AddWithValue("@Name", sheet_name);

My aim is to replace the ? with the parameter.

And one more doubt is can we use parameterized query to replace a table name?

Dave Kreskowiak25-Jul-13 1:17

25-Jul-13 1:17

Parameters do not work for table identifiers. They only work for columns or other identifiers that use data types, such as stored procedure parameters.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

gautamn199025-Jul-13 19:28

25-Jul-13 19:28

Thank you for your kind reply. That was very helpful. One more thing need to be cleared. How can we suppress the warning CA2100 : Microsoft.Security for the following code?

string sheet_name=listBox_Select_a_sheet.SelectedItem.ToString();
string strSql = "SELECT count(*) FROM sheet_name";
using (OleDbCommand cmd = new OleDbCommand(strSql, MyConnection))//CA2100 Warning:Microsoft.Security
{
   cmd.ExecuteNonQuery();
}

Dave Kreskowiak26-Jul-13 2:52

26-Jul-13 2:52

Turn off Code Analysis when you compile. It's not a compiler warning but a code analysis warning. This is to remind you that you should not use user input directly when building your SQL statement as a string.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

gautamn199028-Jul-13 19:03

28-Jul-13 19:03

Thank you so much for your kind support. I have learned new things on parameterized OleDB Query. I hope to help help other in future.

PIEBALDconsult25-Jul-13 14:19

PIEBALDconsult

25-Jul-13 14:19

gautamn1990 wrote:
To suppress the warning CA2100

Never heard of it, never seen it. I do something like:

string strSql = System.String.Format ( "SELECT count(*) FROM [{0}]" ,sheet_name) ;

for table names there's really no substitute. Unsure | :~

gautamn199025-Jul-13 19:45

25-Jul-13 19:45

Thank you for your reply. I tried your concept. the name is getting replaced but during code analysis CA2100: Microsoft.Security: warning is still appearing. Sting sheet_name is value in obtained from users input by the following code.

string sheet_name=listBox_Select_a_sheet.SelectedItem.ToString();
string strSql = "SELECT count(*) FROM sheet_name";
using (OleDbCommand cmd = new OleDbCommand(strSql, MyConnection))//CA2100 Warning:Microsoft.Security
{
   cmd.ExecuteNonQuery();
}

How to supress the CA2100 Warning:MicroSoft.Security ?