Sending String from Database from one Form to another

Question

I'm trying to send a string and a bool from one form to another. The string I'm trying to send is the PayrollNo where the name is present also in the ODeDb Database.

Here's the code I'm using but its not working,

private void BtnContinue_Click(object sender, EventArgs e)
{
    string ConnString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";
    string Query = "SELECT PayrollNo, FirstName, LastName FROM [Employee] WHERE (FirstName + ' ' + LastName) =" +DropBoxEmp.Text;
    EmployeeDetails form = new EmployeeDetails();
    using (OleDbConnection Conn = new OleDbConnection(ConnString))
    {
        Conn.Open();
        OleDbCommand GetPayRoll = new OleDbCommand(Query, Conn);
        string NewPayroll = (GetPayRoll.ExecuteNonQuery()).ToString();
        Conn.Close();
        form.PassValuePayrollNo = NewPayroll;
        form.PassEditing = true;
        form.Tag = this;
        form.Show(this);
        Hide();
    }
}

I get the error :

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

Additional information: Syntax error (missing operator) in query expression '(FirstName + ' ' + LastName) =Gary Lindsay'.

DropBoxEMP is a comboBox populated with the first and last names appended from [Employee]. Gary Lindsay is the appended firstName and lastName from the Table [Employee] How should I change my code to send the string correctly? Any help would be greatly appreciated

When you use a parameter to supply the value you 1) will fix the problem; 2) prevent sql injection attacks — Hans Keﬆing
– Hans Keﬆing, Commented Mar 2, 2016 at 14:39
This question has an example of using parameterized queries: stackoverflow.com/questions/12048152/oledb-parameterized-query — ChrisLively
– ChrisLively, Commented Mar 2, 2016 at 14:40

Soner Gönül · Accepted Answer · 2016-03-02 14:47:39Z

I strongly suspect this happens because you forget to use single quotes for your

(FirstName + ' ' + LastName) =" +DropBoxEmp.Text

should be

(FirstName + ' ' + LastName) = '" + DropBoxEmp.Text + "'"

But do not use this way.

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. Also use using statement to dispose your command as you did for your connection. This statement provides an auto-dispose method so you don't need to close your connection manually.

Also using ExecuteNonQuery for a SELECT statement does not make sense. This method just executes your query. It does not return any data or something.

If you wanna return some data from your query, you can use ExecuteReader or ExecuteScalar if your query returns only one row which I think it is in your case.

string NewPayroll = (string)GetPayRoll.ExecuteScalar();

Sonar, you never fail to help me out, thanks a bunch!! I'll mark this as an answer as soon as I can!

Hans Keﬆing · Accepted Answer · 2016-03-02 14:51:06Z

When you use a parameter to supply the value you 1) will fix the (first) problem; 2) prevent sql injection attacks (work out what your concatenated query would do when you add the missing quotes but want to get the details about "Paddy O'Malley").

I don't use OleDb much, so this may contain errors, but will provide the direction:

first: change the query to use a parameter:

string Query = "SELECT PayrollNo, FirstName, LastName FROM [Employee]"+
 " WHERE (FirstName + ' ' + LastName) = ?";

Then add that parameter plus value to the command object:

GetPayRoll.Parameters.Add("?", OleDbType.VarChar, 50).Value = DropBoxEmp.Text;

and then execute the query, after noticing the comments about that from Soner Gönül.

Yes that's exactly what I've done almost, Thanks for the help!

Collectives™ on Stack Overflow

Sending String from Database from one Form to another

2 Answers 2

But do not use this way.

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

But do not use this way.

2 Comments

1 Comment

Linked

Related