Re: parameterized query - C# Discussion Boards

nidhelp25-Aug-05 21:19

nidhelp

25-Aug-05 21:19

i emailed u. not sure if u will be looking at my email. if u are, i've an access db attached to the email. please open table named Patient.

feel free to ask me if you are unsure about anything.

thank you really much! Big Grin | :-D

jdkulkarni25-Aug-05 23:48

25-Aug-05 23:48

Hi, I did not receive the database. But the code looks ok. I'm sending sample code with this answer. I'm simple code snippet.
/*****************FORM1********************************/
using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

namespace dono

{

///

/// Summary description for Form1.

///

public class Form1 : System.Windows.Forms.Form

{

private System.Windows.Forms.Button button1;

private System.Windows.Forms.TextBox textBox1;

private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;

private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;

private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;

private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;

private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;

private System.Data.OleDb.OleDbConnection oleDbConnection1;

///

/// Required designer variable.

///

private System.ComponentModel.Container components = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

///

/// Clean up any resources being used.

///

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{
this.button1 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(256, 112);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(96, 112);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(160, 20);
this.textBox1.TabIndex = 1;
this.textBox1.Text = "textBox1";
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Patients", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ContactID", "ContactID"),
new System.Data.Common.DataColumnMapping("FirstName", "FirstName")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = "DELETE FROM Patients WHERE (ContactID = ?) AND (FirstName = ? OR ? IS NULL AND Fi" +
"rstName IS NULL)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ContactID", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactID", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Data Source=""G:\database.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO Patients(ContactID, FirstName) VALUES (?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ContactID", System.Data.OleDb.OleDbType.VarWChar, 50, "ContactID"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT ContactID, FirstName FROM Patients WHERE (ContactID = ?)";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
this.oleDbSelectCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ContactID", System.Data.OleDb.OleDbType.VarWChar, 50, "ContactID"));
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = "UPDATE Patients SET ContactID = ?, FirstName = ? WHERE (ContactID = ?) AND (First" +
"Name = ? OR ? IS NULL AND FirstName IS NULL)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ContactID", System.Data.OleDb.OleDbType.VarWChar, 50, "ContactID"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, "FirstName"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ContactID", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactID", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_FirstName1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null));
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(544, 230);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);

}

#endregion

///

/// The main entry point for the application.

///

[STAThread]

static void Main()
{
Application.Run(new Form1());
}

private void button1_Click(object sender, System.EventArgs e)
{
Form2 frm = new Form2();
frm.textBox1.Text = "I got the value";
this.Hide();
frm.Show();
}
}

}
/*************************************************/

/*****************FORM2********************************/

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

namespace dono

{

///

/// Summary description for Form2.

///

public class Form2 : System.Windows.Forms.Form

{

public System.Windows.Forms.TextBox textBox1;

private System.Windows.Forms.TextBox textBox2;

private System.Windows.Forms.Label label1;

private System.Windows.Forms.Label label2;

///

/// Required designer variable.

///

private System.ComponentModel.Container components = null;

public Form2()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

///

/// Clean up any resources being used.

///

protected override void Dispose( bool disposing )

{

if( disposing )

{

if(components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.textBox1 = new System.Windows.Forms.TextBox();

this.textBox2 = new System.Windows.Forms.TextBox();

this.label1 = new System.Windows.Forms.Label();

this.label2 = new System.Windows.Forms.Label();

this.SuspendLayout();

//

// textBox1

//

this.textBox1.Location = new System.Drawing.Point(216, 72);

this.textBox1.Name = "textBox1";

this.textBox1.TabIndex = 0;

this.textBox1.Text = "textBox1";

//

// textBox2

//

this.textBox2.Location = new System.Drawing.Point(216, 120);

this.textBox2.Name = "textBox2";

this.textBox2.TabIndex = 1;

this.textBox2.Text = "textBox2";

//

// label1

//

this.label1.Location = new System.Drawing.Point(112, 72);

this.label1.Name = "label1";

this.label1.TabIndex = 3;

this.label1.Text = "contactID";

//

// label2

//

this.label2.Location = new System.Drawing.Point(112, 120);

this.label2.Name = "label2";

this.label2.TabIndex = 4;

this.label2.Text = "name";

//

// Form2

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(440, 246);

this.Controls.Add(this.label2);

this.Controls.Add(this.label1);

this.Controls.Add(this.textBox2);

this.Controls.Add(this.textBox1);

this.Name = "Form2";

this.Text = "Form2";

this.ResumeLayout(false);

}

#endregion

}
}
/*************************************************/

It is as simple as the code in button click.

Jayant D. Kulkarni
Brainbench Certified Software Engineer in C#, ASP.NET, .NET Framework and ADO.NET

nidhelp26-Aug-05 5:42

nidhelp

26-Aug-05 5:42

hi im sure glad u replied. sorry for the late reply. just got home.

anyway, i just had a look at the code snippet.
over here i think im not sure:
private void button1_Click(object sender, System.EventArgs e)
{
Form2 frm = new Form2();
frm.textBox1.Text = "I got the value"; << the textBox1 is in my form2 so i get errors something like this --> textbox1 not found in this form (form1) and cant get access to textbox1 in form2.
this.Hide();
frm.Show();

thanks friend once again! =)

jdkulkarni28-Aug-05 18:19

28-Aug-05 18:19

Hi,
Ok, I thought u were waiting for my answer. It seems that Form2 does not contain txtBox1. What u need to do is create a simple textbox in Form2 and make it public. If u do not make it public it will not work.

There is another solution. Define a public property which will set the valus of text box.
Something like this..
in Form2:
private string setValue = string.Empty;
public string SetMyValue
{
set
{
this.txtBox1.Text = value;
}
}

in Form1's button click:

Form2 frm = new Form2();
frm.SetMyValue = "I got the value";
this.Hide();
frm.Show(); Big Grin | :-D

Jayant D. Kulkarni
Brainbench Certified Software Engineer in C#, ASP.NET, .NET Framework and ADO.NET

Luis Alonso Ramos25-Aug-05 19:43

25-Aug-05 19:43

jdkulkarni wrote:
string sql = "select * from tabel1 where column1 = '" + txtCol1.Text.Trim() + "'";

Very bad idea to form SQL statements by concatenating strings. It's a huge security vulnerabilty. See this article[^]. It explains parameterized queries, that avoid this and other problems.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

jdkulkarni25-Aug-05 19:54

25-Aug-05 19:54

Ya, I know. It is not a secure way if used on WEB. but for winform application, i dont think it is a big issue.
In general it is bad practice i agr:-Dee.

Jayant D. Kulkarni
Brainbench Certified Software Engineer in C#, ASP.NET, .NET Framework and ADO.NET

Daniel Turini26-Aug-05 2:12

Daniel Turini

26-Aug-05 2:12

jdkulkarni wrote:
Ya, I know. It is not a secure way if used on WEB. but for winform application, i dont think it is a big issue.
So, anyone may login on your winform application by using this password ' OR 1=1 --

I see dead pixels
Yes, even I am blogging now!

-- modified at 8:14 Friday 26th August, 2005

Not Active26-Aug-05 3:08

Not Active

26-Aug-05 3:08

jdkulkarni wrote:
i dont think it is a big issue.

This is the attitude that creates insecure software. The only reason to dynamically create sql statements is if your rdbms doesn't support stored procs, but even then there are ways to mitigate security vulnerabilities.

jdkulkarni26-Aug-05 3:23

26-Aug-05 3:23

The person is working on ACCESS. And anyway I'm not in support of Dynamic query. I strictly use SP's and encryptions while bulding dynamic query. I think it should end this discussion now. Big Grin | :-D

Jayant D. Kulkarni
Brainbench Certified Software Engineer in C#, ASP.NET, .NET Framework and ADO.NET

Not Active26-Aug-05 3:33

Not Active

26-Aug-05 3:33

jdkulkarni wrote:
I'm not in support of Dynamic query.
I strictly use SP's and encryptions while bulding dynamic query

Seems a bit contradictory to me. D'Oh! | :doh:

Luis Alonso Ramos26-Aug-05 3:54

26-Aug-05 3:54

Just type something with a ' in there and you code will blow up. If you have a text box where I enter a date, and I change the culture of the operating system, you'll get erroneous date.

It's really easy to have parameterized queries, and even access has them. Compare this:

string sql = "SELECT * FROM Users WHERE UserName = '" + txtUserName.Text "' AND Password = '" + txtPassword.Text + "'";
OleDbCommand cmd = new OleDbCommand(cmd, conn);

to this:

string sql = "SELECT * FROM Users WHERE UserName = ? AND Password = ?";
OleDbCommand cmd = new OleDbCommand(cmd, conn);
cmd.Parameters.Add("", txtUserName.Text);  // In Access, parameter name doesn't
cmd.Parameters.Add("", txtPassword.Text);  // matter, it's by position

Easier to read in my opinion, way much more secure and robust, and easier to maintain.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

Re: TEXT BOX TEXT TO DOUBLE?

nidhelp26-Aug-05 6:10

nidhelp

26-Aug-05 6:10

hi there,
thanks for replying!

good, just learnt something from u =) --> Just type something with a ' in there and you code will blow up

however, what i need help in is i cant load my data well if i were to SEPARATE "Search Controls" (textbox and button for query --> to get data belonging to person with a certain ID no.) with "Textbox Controls" (that is, data is to be loaded into these "Textbox Controls" placed in form2).

im able to retrieve selected data when i place "Search Controls" and "Textbox Controls" on the same form but this is not what i want.

thanks!

TEXT BOX TEXT TO DOUBLE?

...---...25-Aug-05 14:56

...---...

25-Aug-05 14:56

1.I have two text boxes. The text boxes have numbers in them.
2.I have an "Add" button.
3.The two values in the text boxes correspond to the generic class I have added to my project->
Lets say double x and y (private) with Set Get methods (public).

1. How do I get the text to convert to double?

2.Where do I put the code?

3.Any other advice I'd appreciate. THanks!

Luis Alonso Ramos25-Aug-05 16:49

Re: TEXT BOX TEXT TO DOUBLE?

25-Aug-05 16:49

double d = 0.0;
 
try
{
    d = Convert.ToDouble(textBox.Text);
}
catch(FormatException)
{
    // Error parsing the double... invalid characters maybe?
}
 
// Do whatever with d (that will contain 0 if an error occurred.)

I hope this helps!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

-- modified at 22:49 Thursday 25th August, 2005

...---...25-Aug-05 17:02

...---...

25-Aug-05 17:02

As we used to say in Boston (1975)

Luis! Luis! Luis!

many thanks.......

Re: TEXT BOX TEXT TO DOUBLE?

Luis Alonso Ramos25-Aug-05 18:03

Retrieve the value of Inout parameter

25-Aug-05 18:03

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

tsramkumar25-Aug-05 11:35

25-Aug-05 11:35

Hi
I am calling a DB2 stored procedure with 3 parameters. The last one is an inout parameter in which a value is returned (SQLCODE). I am using the DB2DataAdapter object's fill method to return a dataset. I am getting the results fine in the dataset. However, how can I retrieve the inout parameter's value?

Any ideas?

miah alom25-Aug-05 11:42

miah alom

25-Aug-05 11:42

Just use this while calling the SP,

DB2Parameter param = new DB2Parameter("@param", DB2Type.Int);
param.Direction = System.Data.ParameterDirection.InputOutput;

tsramkumar25-Aug-05 11:46

25-Aug-05 11:46

Yeah, I am doing all that. But how do I retrieve the parameter's value after the call? I only see the result tables in the dataset. When I checked the db2parameter object for this parameter, I see the old value. I am sure my stored procedure passes back the sqlcode in that parameter. I don't see it.
Any more suggestinos?

miah alom25-Aug-05 11:52

miah alom

25-Aug-05 11:52

Buddy I tested the same for SqlDataAdapter, it works just fine.
Can you please post the code. May be you missed out something?

tsramkumar25-Aug-05 12:56

25-Aug-05 12:56

Hi
Here is my code.
================

<br />
...<br />
DB2Parameter[] db2params = new DB2Parameter[3];<br />
<br />
BuildParameters(db2params, new object[] {"LSFEEDCD", DB2Type.Char, 4,"RABF"} , new object[]{"LSINVDT", DB2Type.Date, 8,"20041100"},new object[]{"LSSQLCD", DB2Type.SmallInt, 2,20});<br />
<br />
DB2Helper.FillDataset(txtConnectionString.Text,"TTEZQ.ZQSPNS4", ds, new string[] {"Gateway_Test"},db2params);<br />
...<br />
..<br />

BuildParameters Method definition
==================================

<br />
  public static void BuildParameters(DB2Parameter[] DB2Params, params object[] parameters)<br />
   {<br />
	   for (int i=0; i <parameters.Length;i++)<br />
	   {<br />
		   object a = ((object[])(object)parameters[i])[0];<br />
		   object b = ((object[])(object)parameters[i])[1];<br />
   		   object c = ((object[])(object)parameters[i])[2];<br />
   		   object d = ((object[])(object)parameters[i])[3];<br />
		   DB2Params[i] = new DB2Parameter((string)a,(IBM.Data.DB2.DB2Type)b,(int)c);<br />
		   DB2Params[i].Direction = ParameterDirection.InputOutput;<br />
   		   DB2Params[i].Value = d;<br />
	   }<br />
   }<br />

I am interested in the third parameter where I pass the value as 20. I see the old value even after the call.
I did do a step by step debug within the DB2Helper class of the application block and I was able to see the returned value of the parameter in the filldataset method of that class, but once I return back to my class, I see the old value.

Any thoughts?

Thanks

Luis Alonso Ramos25-Aug-05 17:19

25-Aug-05 17:19

You don't get the value of the parameter in the DataSet, but you have to get it from the Parameter collection of the Command object:

DB2Command cmd = new DB2Command("proc name", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("InOutParam", inputValue).Direction = ParameterDireciton.InputOutput;
 
DB2DataAdapter da = new DB2DataAdapter(cmd);
da.Fill(ds);  // Execute the stored procedure and get the returned data sets
 
// Get value of output parameter, cast to whatever type
int outputValue = (int) cmd.Parameters[0].Value;

I hope this helps!

-- LuisR

tsramkumar26-Aug-05 1:35