Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
|
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 22nd, 2007, 04:08 PM
Registered User
 
Join Date: Jun 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to fcortes
Default Calling Parameterized Update Query from C#

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.

 
Old June 24th, 2007, 01:39 PM
Authorized User
 
Join Date: Aug 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubisascends
Default

Try changing this:
Code:
 int rowsAffected = thisCommand1.ExecuteNonQuery();
to this:
Code:
thisCommand.ExecuteNonQuery();
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)
 
Old June 25th, 2007, 11:05 AM
Registered User
 
Join Date: Jun 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to fcortes
Default

Quote:
quote:Originally posted by anubisascends
 Try changing this:
Code:
 int rowsAffected = thisCommand1.ExecuteNonQuery();
to this:
Code:
thisCommand.ExecuteNonQuery();
I assume you meant "thisCommand1" since it's the one that's been defined.
I tried it just to see what happened, it does execute (I don't see why it wouldnt) but unfortunately I got the same result: nothing gets updated in the database, (the query seems executed but not with the expected result), plus I need to know now many rows were affected which is the reason why i stored that value in the int variable.

Thanks for replying though!

any other options?
 
Old June 25th, 2007, 11:39 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old June 25th, 2007, 09:48 PM
Authorized User
 
Join Date: Aug 2006
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubisascends
Default

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)
 
Old June 26th, 2007, 12:55 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
quote:Originally posted by anubisascends
 I have used the @ parameters quite a bit using access DB's.
 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.
 
Old July 11th, 2007, 08:47 AM
Registered User
 
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?????






Similar Threads
Thread Thread Starter Forum Replies Last Post
DateTime Typed Paramater on Parameterized Query Mutia VB.NET 2002/2003 Basics 1 July 25th, 2007 08:57 AM
Parameterized Query asters VB.NET 2002/2003 Basics 5 July 9th, 2007 12:08 PM
calling an UPDATE command from a textbox TomTab ADO.NET 3 January 19th, 2006 12:31 AM
Sql Parameterized query, @ or ? Pauline BOOK: Beginning ASP.NET 1.0 2 January 26th, 2005 11:33 AM
Problem In parameterized Sqladapter Update yoord BOOK: Beginning ASP.NET 1.0 11 September 1st, 2004 04:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.