Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 22nd, 2007, 04:08 PM
Registered User
 
Join Date: Jun 2007
Location: Hamilton, ON, Canada.
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.

Reply With Quote
  #2 (permalink)  
Old June 24th, 2007, 01:39 PM
Authorized User
 
Join Date: Aug 2006
Location: Charlotte, NC, USA.
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)
Reply With Quote
  #3 (permalink)  
Old June 25th, 2007, 11:05 AM
Registered User
 
Join Date: Jun 2007
Location: Hamilton, ON, Canada.
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?
Reply With Quote
  #4 (permalink)  
Old June 25th, 2007, 11:39 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #5 (permalink)  
Old June 25th, 2007, 09:48 PM
Authorized User
 
Join Date: Aug 2006
Location: Charlotte, NC, USA.
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)
Reply With Quote
  #6 (permalink)  
Old June 26th, 2007, 12:55 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #7 (permalink)  
Old July 11th, 2007, 08:47 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
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?????

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.