Hi
I'm trying to use a parameritized update query created in MS Access to update a table in MS Access from C# code.

The query seems to executy but no rows are affected. I'm supposed to be passing parameters from my form in Visual Studio but no records are affected. the query does work in MS Access... any reason why no records are being affected?

this is my code for the function that calls the store procedure (ms update query) in Access from the code in my form

private string UpdateDataPatient(string ID, string LN, string FN, DateTime birth,
            string address, string city, string prov, string post, string phone,
            int insCo, string insNum, string kin, string kinRel)
        {
            string sMsg = "";
            DateTime dob = birth;

            try
            {
                //create connection
                // OleDbConnection thisConn = new OleDbConnection();
                // thisConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\fcortes\Desktop\PP2E v1-0 Student Files 2005-0430\currntprojectNLH\NLHospital.mdb";
                //create sql command
                OleDbCommand thisCommand1 = new OleDbCommand("UpdatePatients", thisConn);
                thisCommand1.CommandType = CommandType.StoredProcedure;
                //thisCommand.Connection = thisConn;
                //Create DataAdapter

                OleDbParameter DateOfBirth = new OleDbParameter("@DateOfBirth", OleDbType.Date);
                DateOfBirth.Direction = ParameterDirection.Input;
                DateOfBirth.Value = this.txtDOB.Text;
                thisCommand1.Parameters.Add(DateOfBirth);

                //param health card
                OleDbParameter healthNumber = new OleDbParameter("@HealthNumber", OleDbType.VarChar);
                healthNumber.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(healthNumber);
                healthNumber.Value = this.txtHCard.Text;
                //@FirstName
                OleDbParameter firstName = new OleDbParameter("@FirstName", OleDbType.VarChar);
                firstName.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(firstName);
                firstName.Value = this.txtName.Text;
                //LastName
                OleDbParameter lastName = new OleDbParameter("@LastName", OleDbType.VarChar);
                lastName.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(lastName);
                lastName.Value = this.txtLast.Text;
                //Address
                OleDbParameter addressp = new OleDbParameter("@Address", OleDbType.VarChar);
                addressp.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(addressp);
                addressp.Value = this.txtAddress.Text;
                //City
                OleDbParameter cityp = new OleDbParameter("@City", OleDbType.VarChar);
                cityp.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(cityp);
                cityp.Value = this.txtCity.Text;
                //province
                OleDbParameter province = new OleDbParameter("@Province", OleDbType.VarChar);
                province.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(province);
                province.Value = this.txtProvSt.Text;
                //PostalCode
                OleDbParameter postalCode = new OleDbParameter("@PostalCode", OleDbType.VarChar);
                postalCode.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(postalCode);
                postalCode.Value = this.txtPostalCode.Text;
                //Phone
                OleDbParameter phonep = new OleDbParameter("@Phone", OleDbType.VarChar);
                phonep.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(phonep);
                phonep.Value = this.txtPhone.Text;
                //PolicyNumber
                OleDbParameter policyNumber = new OleDbParameter("@PolicyNumber", OleDbType.VarChar);
                policyNumber.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(policyNumber);
                policyNumber.Value = this.txtHCard.Text;
                //InsuranceCompanyID
                OleDbParameter insuranceCompanyID = new OleDbParameter("@InsuranceCompanyID", OleDbType.Integer);
                insuranceCompanyID.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(insuranceCompanyID);
                insuranceCompanyID.Value = Convert.ToString(this.cbInsuranceCompany.SelectedI ndex + 1);
                //NextOfKin
                OleDbParameter nextOfKin = new OleDbParameter("@NextOfKin", OleDbType.VarChar);
                nextOfKin.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(nextOfKin);
                nextOfKin.Value = this.txtNextOfKinName.Text;
                //NextOfKinRelationship
                OleDbParameter nextOfKinRelationship = new OleDbParameter("@NextOfKinRelationship", OleDbType.VarChar);
                nextOfKinRelationship.Direction = ParameterDirection.Input;
                thisCommand1.Parameters.Add(nextOfKinRelationship) ;
                nextOfKinRelationship.Value = this.txtNOKRelationship.Text;


                thisConn.Open();
                int rowsAffected = thisCommand1.ExecuteNonQuery();
                thisConn.Close();
                sMsg = rowsAffected + " records were affected";

            }
            catch (Exception e)
            {
                sMsg = "Record was not updated " + e.Message.ToString();
            }
            finally
            {
                thisConn.Close();
                thisConn = null;
            }
            return sMsg;
        }
    }

No exception is thrown when executed but no update takes place either. the store procedure is called "UpdatePatients" (in MS Access)

names match on C# and MS Access query
this is my Sql statemt for the "UpdatePatients" query

UPDATE Patients SET Patients.DateOfBirth = [@DateOfBirth], Patients.FirstName = [@FirstName], Patients.LastName = [@LastName], Patients.Address = [@Address], Patients.City = [@City], Patients.Province = [@Province], Patients.PostalCode = [@PostalCode], Patients.Phone = [@Phone], Patients.PolicyNumber = [@PolicyNumber], Patients.InsuranceCompanyID = [@InsuranceCompanyID], Patients.NextOfKin = [@NextOfKin], Patients.NextOfKinRelationship = [@NextOfKinRelationship]
WHERE (((Patients.HealthNumber)=[@HealthNumber]));


Thanks in advance for any help that can be provided!!

Francisco.

Try changing this:
to this:
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
Albert Einstein
US (German-born) physicist (1879 - 1955)

I don't think Access like @ parameters. You may need something like this:

UPDATE Patients SET Patients.DateOfBirth = ?, Patients.FirstName = ?, ....

Then make sure you pass the parameters to the command object in the right order.

Hope this helps,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

I have used the @ parameters quite a bit using access DB's.

You might want to look at organizing your code.

OleDBParameter [parameter1] = new OleDBParameter ("@paramter1", [value data]);
OleDBParameter [parameter2] = new OleDBParameter ("@paramter2", [value data]);
OleDBParameter [parameter3] = new OleDBParameter ("@paramter3", [value data]);

thisCommand1.Parameters.Add([parameter1]);
thisCommand1.Parameters.Add([parameter2]);
thisCommand1.Parameters.Add([parameter3]);

A lot of the information that you are giving the complier is not actually necessary, plus, this makes it easier to read. When I do this in the code, I get the update that I expect.

Also, are there any other commands that you are passing that do/do not work? If none of the commands that you are sending are working, a problem that I ran into once before is that the complier (VS2005) was set to copy my database to the output directory. This can be a bear to try and debug as the command is executed correctly, but the data is not saved to your DB, but rather a copy of the DB.

Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.
Albert Einstein
US (German-born) physicist (1879 - 1955)

 Really? With queries created in Access, or with "in-line queries" created in your code?

I had nothing but trouble getting queries created in Access to work without the ? characters. Could you post a quick example?

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Hi All,
I need to print the parameterised sql query with values..

Ex :
If the query with parameters is "Select * from table where tableid = @tableid"

I pass the value to it.

Now i need to print the sql query like
"Select * from table where tableid = 1"

How do i do it?????