mysql connector auto update command specifies incorrect database need another way to update

I use Typed Datasets for my DAL and class files for my BLL The Application uses multiple databases depending on what user logs in. The MSSQL autogenerated update method uses a sort of agnostic database variable [dbo]. so when the app passes in a different dabase name in its connection string it updates the correct database. However the MYSQL autogenerated code for the update command actually specifies the name of the dabase that the tableadapter is using when you create the typed dataset. so even if you pass in a different database in the connection still goes to the original database name. This is how I currently update rows in a database in my BLL using System; using System.Data; using System.Configuration; using System.Web; using VinWinBLL.DAL.CONTACTDAL.CompanyDALTableAdapters; using VinWinBLL.DAL.CONTACTDAL; namespace VinWinBLL.BLL.CONTACTBLL { /// /// Summary description for CompanyBLL /// public class CompanyBLL { private tblcompanyTableAdapter _tblcompanyAdapter = null;internal tblcompanyTableAdapter CompanyAdapter { get { if (_tblcompanyAdapter == null) _tblcompanyAdapter = new tblcompanyTableAdapter(); return _tblcompanyAdapter; } } public bool UpdateCompany(long companyid, string company, bool active) { CompanyDAL.tblcompanyDataTable companies = CompanyAdapter.GetCompanyDataByCompanyID(companyid.Value);if (companies.Count == 0) { return false; } CompanyDAL.tblcompanyRow comp = companies[0]; comp.Active = active; comp.Company = company; int rowsAffected = CompanyAdapter.Update(comp);return rowsAffected == 1; }
I apparently need another way to handle this since the autogenerated code using teh mysql connector has this bug. I am thinkingof adding a query in the tableadapter to "Update" the databale. I do this with the insert and it works properly making use of the connection string databse name. Mainly because the sql statement doesnt specify the database name like the autogenerated methods do. For example the autogenerated insert this._adapter.InsertCommand.CommandText = "INSERT INTO `vinwin_base_dbo`.`tblcompany` (`Company`, `Active`) VALUES (@Company" + ", @Active)"; My added insert methods sql statement this._commandCollection[5].CommandText = "INSERT INTO tblcompany\r\n (Company, Active)\r\nVALUES (@Com" + "pany, @Active);\r\nSelect last_insert_id();\r\n"; Notice how my sql statement doesnt specify the database name. To access the insert I use the following in my BLL class file private tblcompanyTableAdapter _tblcompanyAdapter = null;internal tblcompanyTableAdapter CompanyAdapter { get { if (_tblcompanyAdapter == null) _tblcompanyAdapter = new tblcompanyTableAdapter(); return _tblcompanyAdapter; } } public long AddCompany(string company, bool active) { byte b_active = Convert.ToByte(active); long companyid = Convert.ToInt64(CompanyAdapter.InsertQuery(company, b_active)); return companyid; }
So what I am wondering is if I create in teh table adapater an update method so that it looks like this this._commandCollection[6].CommandText = @"UPDATE tblcompany SET Company = @Company, Active = @Active WHERE (CompanyID = @Original_CompanyID) AND (@IsNull_Company = 1) AND (Company IS NULL) AND (Active = @Original_Active) OR (CompanyID = @Original_CompanyID) AND (Company = @Original_Company) AND (Active = @Original_Active)"; Problem is I am not sure how to handle this in the BLL. If I try to handle it in the same manner using the new "UpdateQuery" methodI get a build error No Overload for Method UpdateQuery takes 1 argument.

Posted On: Tuesday 20th of November 2012 09:15:00 PM Total Views:  267
View Complete with Replies

Related Messages:

Sending mail from mysql   (283 Views)
Is there any way to send e-mail from mysql database like mssql.
parameter must be defined error on mysql with c#   (771 Views)
the below code gives Parameter '@row' must be defined error on filling data adaptor what is wrong with my code ... best regards StringBuilder sql = new StringBuilder(); sql.Append("SET @row:=0;"); sql.Append("SELECT * From ("); sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2"); sql.Append("From _Tab1) As _T1"); sql.Append("WHERE Rec_No > 1 And Rec_No
loop in mysql   (221 Views)
i have one table banc_premium in which each app have more then 1 trounches few of them are paid for example 3 premiums are paid and three premium are due so have to print trounche number fouth,five and six it is working for single application but not working for multiple application kindly help me. The Procedure is CREATE PROCEDURE sp_repeat3(IN applicationnumber1 VARCHAR(100),OUT appnum VARCHAR(100)) BEGIN --Declare appnum int; select distinct applicationnumber into appnum from banc_premium where applicationnumber=applicationnumber1; select @rownum:=@rownum+1 Tronch,paymentdatetime,applicationnumber from banc_premium a, (SELECT @rownum:=(select count(*) from banc_premium where applicationnumber=appnum and paymentdatetime
transaction problem in mysql for update for each row of gridview   (270 Views)
i am having a transaction related problem regarding mysql.. need some advice how to proceed !!! here is a gridview for my application and i am inserting or updating for each row.... like foreach(GridViewRowrowinGridPaymentSchedule.Rows) { if(row.RowType==DataControlRowType.DataRow) { if(payment.Update(PayDetails,0)) { success=true; } } } if(success==true) { lblmsg.Text="PaymentScheduleDetailsUpdated"; ClearFields(); } well now what i want is if any of the row update or insert failes ... it should revert all the changes that was already made into the database ... even a single row update .. into that perticular table ... the code which i have called forpayment.Update(PayDetails, 0) is : public Boolean Update(ProjectPaymentForm Payment, int flag) { MySqlConnection con = new MySqlConnection(Connection.GetConnectionString()); if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); try { using (MySqlCommand cmd = new MySqlCommand("UPDATE payment_schedule_details SET" + " actual_date = ActualDate," + " actual_amount = ActualAmount," + " reason_for_delay = ReasonForDelay where payment_schedule_id=PayScheduleId" + " and milestone_desc=MileStone", con)) { //cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("PaymentScheduleID", Payment.ScheduleId); cmd.Parameters.AddWithValue("ActualDate", (Payment.ActualDate.ToString("yyyy-MM-dd") == null DBNull.Value.ToString() : Payment.ActualDate.ToString("yyyy-MM-dd"))); cmd.Parameters.AddWithValue("ActualAmount", (Payment.ActualAmount == null 0 : Payment.ActualAmount)); cmd.Parameters.AddWithValue("ReasonForDelay", Payment.ReasonForDelay); cmd.Parameters.AddWithValue("PayScheduleId", Payment.ScheduleId); cmd.Parameters.AddWithValue("MileStone", Payment.ProjectMileStone); cmd.Parameters.AddWithValue("flag", flag); if (cmd.ExecuteNonQuery() > 0) { con.Close(); return true; } } } catch (MySqlException ex) { throw ex; } return true; } can someone help me out how to proceed in this scenario how do i undo each of the updates made into the table payment schedule details ...
problem connecting to mysql database   (386 Views)
I'm new to and I'm trying to connect to a mysql database but I'm getting this error when I'm running my application. Access denied for user 'test_mysql'@'localhost' (using password: YES). I used thistutorial to get this far ( ) but I don't know how to fix this.
Custom paging with mysql   (275 Views)
I want to do custom paging in gridview with mysql database. can anyone have store procedure to do custom paging using object datasource.
Connection pooling issue with microsoft enterprise library in mysql   (372 Views)
I am using microsoft enterprise libarary to connect to mysql using mysql .net connector (latest version 6.2.3) But i am facing a problem of too many connections and connection pool reached maximum limit issue. As connection is disposed by enterprise library iteself so i dont close the connection anywhere. So after two or three hours i found i have 100 mysql connections in sleep mode which are doing nothing and are not being reused from the code . So i want to ask is there any issue with my code or is there any issue with mysql connector please suggest me a solution so that i can rectify this issue as it is causing a lot of trouble to me.
Need help with uploading a text file in to mysql database   (525 Views)
I want to upload a text file and stored to my database. the column on mydatabaseit is longblob type. I don't know much about this so if anyone can help I will
mysql data source provider - mysql connector/net   (282 Views)
, I having problem installing mysql connector/net 6.2.3 on my vsd 2010 because the installed went smoothly but when Im trying to use the mysql on data source provider, I cant see it on the list and when try to install/change the connector the visual studio integreation has an x mark meaning to say it was not installed on my vsd2010.
problem occured in mysql databse when i insert arabic word or devnagari word   (280 Views)
what i do then a column in mysql table accept hindi or urdu both word when i set chaset to utf8 then he work fine for Hindi but errror occured when i insert arablic [urdu] words. so i need a sollution that the column can be accept hindi or urdu both type word
How to do dropdownlist search in with C# with mysql ?   (295 Views)
I got table name ( customer ) and field Name as ( customerId , companyName , personName and poscode ) in Mysql Now i have created search function by using textbox (txtCompanyName.Text) like where i can query companyName by entering data to textbox to search . Coding :- qry = "select * from customer where companyName='" + txtCompanyName.Text + "'"; Bt i want to do search by using1 dropdownlist(companyName , personName, poscode).For example i selectdropdownlist as companyName andenter thename in textbox and search. After that i select the same dropdownlist with personName field and enter the name of the person and search . How to do that and what is the mysql query for such case Please help .
How to store and reterive the Image using mysql   (205 Views)
Hi I am developing the project in C# . i have store the image to the mysql database and reterive the image if any one know please help me...
mysql connector/Net   (202 Views)
I read throug about mysql connector. I get unclear point in this fact : Large-packet support for sending and receiving rows and BLOBs up to 2 gigabytes in size. It means that connector could handle up to 2 gigabytes size of a row with BLOB, is it
How to learn mysql.   (164 Views)
How can i learn mysql. i learn something something from ebooks but how i can run our mysqlcommand in mysql apps
mysql data source   (130 Views)
can anybody make heads or tales of the following nnection = New OdbcConnection("Driver={mySQL};Server=XXXXXXXX;Option=0000;Stmt=;Database=XXXXX; User=xxxxx;Password=xxxxx;") Line 25: Dim cmdstring As String = "SELECT clientnumber FROM clients WHERE clientnumber ='" & clientnumber & "'" Line 26: strconnection.Open() Line 27: Line 28: CMD = New OdbcCommand(cmdstring, strconnection) Exception Details: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ORIGINAL CODE BELOW strconnection = New OdbcConnection("Driver={mySQL};Server=XXXXXXXX;Option=3306;Stmt=;Database=XXXXX; User=xxxxx;Password=xxxxx;") Dim cmdstring As String = "SELECT clientnumber FROM clients WHERE clientnumber ='" & clientnumber & "'" strconnection.Open() any help is apreciated
Connecting to mysql localhost   (149 Views)
I just used part 1 and part 2 of this tutorial and I did all the installation/configuration, but when I try to run my page, I get the following error: I cannot find any resolution, anyone thanks. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Type of Property namespace DataAccess { public class DataReader { public static DataSet GetPropertyTypes() { string connectionString = "server=localhost; user id=Developer; password=mypassword; database=mydatabase; pooling=false;"; SqlConnection sqlConnection = new SqlConnection(connectionString); string queryString = "select * From propertytypes order by propertytypedescription"; SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, sqlConnection); DataSet dsPropertyTypes = new DataSet(); dataAdapter.Fill(dsPropertyTypes); return dsPropertyTypes; } } }
validating username and password in using mysql   (270 Views)
Hi , I am new to and MySQL . I am using MySQL 5.1 . I have a login form where username and password is entered into a textbox . The validation is done against the 'Username' and 'Password' fields in 'new' table which is in 'password' schema . serverhost :localhost uid:root password:pwd 1) vallidation should be done 2) if successful , it should redirect to 'Home.aspx' 3) else it should redirect to 'invalid.aspx' Can some one write the code for me
Too many connections error in mysql   (317 Views)
i am getting this error 'System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 3.51 Driver]Too many connections.' from the site. Database server is on another machine. i am closing all connections as soon as a database ttransaction gets over. I have given option=3;Pooling = true;Min Pool Size=5;Max Pool Size=60;Connect Timeout=0; in the connection string what may be the problem
Question about mysql migration   (272 Views)
I'm thinking about migrating my MS SQL driven web project to mysql. In my project I use formview with edit/update field functions. Will this work in mysql, Can I use the same code ( I know I will have to change the connection string)
Splitting varchar in stored procedure using mysql   (217 Views)
Hi. I would liketo know if there is amethod to split a varchar in stored procedure Or i can only split the varchar using algorithm