is it wrong query in access 2010 and C#.net

Question

1.00/5 (1 vote)

See more:

Hi all

Is it wrong Query in C#.net and Access database

C#

string _command_up_stat_comm = "
UPDATE Ghesting SET status_pay = ? WHERE (fk_code_m_buyer = ?) AND (date_ghest = ?)";
  OleDbParameter[] pa_sta_gh = new OleDbParameter[3];
  pa_sta_gh[0] = new OleDbParameter("status_pay", OleDbType.Char);
  pa_sta_gh[0].Value = Convert.ToString(U_status_ghest_comm);
  pa_sta_gh[1] = new OleDbParameter("fk_code_m_buyer", OleDbType.Char);
  pa_sta_gh[1].Value = Convert.ToString(code_m_pay_comm);
  pa_sta_gh[2] = new OleDbParameter("date_ghest", OleDbType.Char);
  pa_sta_gh[2].Value = Convert.ToString(date_pay_comm);
  a.Query_Update_command(_command_up_stat_comm, pa_sta_gh);

this code and query run but don't correctly

Posted 28-Feb-15 2:25am

bernova

Updated 28-Feb-15 2:27am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2015-02-28T03:01:00

Solution 1

The query looks good. Copy it and run it at the backend to confirm it works.

Posted 28-Feb-15 3:01am

Abhinav S

Comments

bernova 28-Feb-15 10:05am

tanks a lot
but the code is run but don't correctly

Jörgen Andersson 28-Feb-15 15:45pm

What's the problem or error message?

Maciej Los · Answer 2 · 2015-02-28T06:49:00

Solution 2

If date_ghest field is date data type, why do you set it as a char?

C#

pa_sta_gh[2] = new OleDbParameter("date_ghest", OleDbType.Char);

Use proper date format!

I'd suggest to use named parameters:

SQL

PARAMETERS [sta_pay] CHAR, [code_buyer] CHAR, [date_ghest] DATETIME;
UPDATE Ghesting SET status_pay = [sta_pay]
WHERE ((fk_code_m_buyer = [code_buyer]) AND (date_ghest = [date_ghest]));

For further information please see:
PARAMETERS Declaration (Microsoft Access SQL)[^]

Posted 28-Feb-15 6:49am

Maciej Los

Comments

Jörgen Andersson 28-Feb-15 15:41pm

OleDb does not support named parameters. https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.110%29.aspx

Maciej Los 28-Feb-15 19:27pm

Did you follow the link? MS Access database engine supports named parameters, Jorgen ;) Believe me i used it and use it till now ;)

Jörgen Andersson 1-Mar-15 1:36am

Access yes, OleDb no.

Maciej Los 1-Mar-15 3:23am

Try to find out ;)
Cheers, Maciej

bernova 1-Mar-15 6:13am

string date=dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["date_pay_ghest_grid"].Value.ToString();
string mabl = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["mablagh_ghest_grid"].Value.ToString();
string co = dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells["code_m_buyer_Grid"].Value.ToString();
using (OleDbConnection con = new OleDbConnection(tt.Access_Con))
{
con.Open();
using (OleDbCommand cv = new OleDbCommand())
{
cv.CommandType = CommandType.Text;
cv.CommandText = "UPDATE Ghesting SET date_ghest = [dat_gh] , mablagh_ghest = [mabl] WHERE (( fk_code_m_buyer = [fk_co]) AND (date_ghest = [dat_ghes]))";
cv.Connection = con;
cv.Parameters.AddWithValue("dat_gh", OleDbType.Char).Value = date;
cv.Parameters.AddWithValue("mabl", OleDbType.Char).Value = co;
cv.Parameters.AddWithValue("fk_co", OleDbType.Char).Value = mabl;
cv.Parameters.AddWithValue("dat_ghes", OleDbType.Char).Value = fg.Date_m_ghest;
//cv.Parameters.AddWithValue("phon_num", OleDbType.Char).Value = phone_number_textbox.Text.ToString();
//cv.Parameters.AddWithValue("date_ghest", OleDbType.Char).Value = fg.Date_m_ghest;

cv.ExecuteNonQuery();
//fr = bb.select_ghesting(code_m_buyer.Text);
//dataGridView1.DataSource = fr;
}
}

Maciej Los 1-Mar-15 6:38am

As i mentioned in a comment to your answer: why do you convert date to string (see first line of your code)?

bernova 1-Mar-15 7:47am

because i convert date field to Helical date

Maciej Los 1-Mar-15 8:31am

Date must be a date, not string! This is probably the reason you get incorrect output.