|
Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me.
But in my situation, I can not make a combination of columns unique. Things are like
1. Customer are renewed so the data can be very similar to each other.
2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end.
I am thinking I should change an existing field unique or add a new field which would be unique.
|
|
|
|
|
sharp_k wrote: But in my situation, I can not make a combination of columns unique.
The table represents a "customer". There are going to be columns in there, not all 80, which defines what a unique "customer" is.
You CANNOT procede until you determine which columns make it unique.
If there a few columns then you can add a uniqueness constraint.
But lets say you have a 'lot' of columns, like 50 columns, then you are probably out of luck for easy solutions because it is unlikely that you can add a uniqueness constraint for that many columns.
In that case you would need to wrap ALL access to table in a proc. The proc would verify, via a query, that no other record existed with those 50 columns before the insert. Views can often help with this.
You also need to consider exactly how those records get added. Because now the system is going to start producing errors where it didn't produce errors before.
Additionally if you have a 'lot' of columns which make it unique then for something called a "customer" I would think that there is a design problem.
|
|
|
|
|
Like others have said, you need to normalize your database.
When a customer is renewed you don't get a new customer and should therefore not get a new entry in the customer table, but rather a new entry in the subscription table or customer history table or whatever table makes most sense to your system.
As we don't know much about you database we can only give you generalized advice. Mine would be to read this[^] article.
It describes in an easy to understand way how to, and why, you normalize your database.
|
|
|
|
|
One way to stop duplicate records being inserted is to check and see if a record for a customer already exists before inserting the record.
For example, something along the lines of:
if (not exists(select top 1 1 from Customers where Field1 = @Param1 and Field2 = @Param2 ... and so on))
begin
insert into Customer (...)
values (...)
end
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
..should be wrapped in a transaction to prevent race-conditions, wouldn't it?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Indeed it could.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
sharp_k wrote: it happens duplicate records are inserted
How do you determine that?
To go along with what the others said, you can query the table for similar entries and present them to the user, e.g.:
You entered "John Smith at 123 Main St", is this the same customer as "John Smith at 123 North Main Street"?
|
|
|
|
|
Learn to normalize. You want one artificial key for quick access, that's the identity. Normalization gets you the combination of fields that make the record unique, which depends on the data being described.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I am getting the above error when I upload my website to host but it's working perfectly on my local PC although both are connected to the same database!
here is the error followed by the code:
Object reference not set to an instance of an object
[NullReferenceException: Object reference not set to an instance of an object.]
rmconline.salary_slip.btnGenerate_Click(Object sender, EventArgs e) in C:\Users\Jassim\documents\visual studio 2010\Projects\rmconline\rmconline\salary_slip.ascx.cs:70
DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +96
DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +540
DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
and here is my code:
protected void btnGenerate_Click(object sender, EventArgs e)
{
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_get_salary_slip", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
sql_command.Parameters.AddWithValue("param_is_exist", MySqlDbType.Bit).Direction = ParameterDirection.Output;
sql_reader = sql_command.ExecuteReader();
sql_reader.Read();
if (sql_reader.HasRows)
{
lblMonth.Text = String.Format("{0} {1:0000}", cboMonth.Text, txtYear.Value);
lblSalary.Text = "BD " + Convert.ToDouble(sql_reader["salary_slip_salary"]).ToString("#,###,##0.000");
panelNoSalarySlip.Visible = false;
panelSalarySlip.Visible = true;
sql_connection.Close();
populate_salary_slip_details();
}
else
{
panelSalarySlip.Visible = false;
panelNoSalarySlip.Visible = true;
}
}
private void populate_salary_slip_details()
{
data_table = new DataTable();
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_populate_salary_slip_details", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
sql_adapter = new MySqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataSalarySlip.DataSource = data_table;
dataSalarySlip.DataBind();
}
|
|
|
|
|
jrahma wrote: sql_adapter = new MySqlDataAdapter(sql_command);
the above you should change to include you connection object i.e.
sql_adapter = new MySqlDataAdapter(sql_command, sql_connection);
further reading MySQL DataAdapter[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
jrahma wrote: [NullReferenceException: Object reference not set to an instance of an object.]
rmconline.salary_slip.btnGenerate_Click(Object sender, EventArgs e) in C:\Users\Jassim\documents\visual studio 2010\Projects\rmconline\rmconline\salary_slip.ascx.cs:70
DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +96
DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +540
Running a debug build rather than a release build would yield actual file name, class name AND line numbers for where the exception exactly gets thrown; from there it is a matter of seconds to pinpoint the problem.
|
|
|
|
|
Hi,
I have two MySQL servers.. one is sourceMySQL and targetMySQL.
I want to update all the tables but WITHOUT the data on the targetMySQL using sourceMySQL without having to do it one by one so it will only update the table structure...
Is there anyway to say:
UPDATE all table structure IN targetMySQL FROM
|
|
|
|
|
Your best bet would be to get a copy of MySql workbench MySQL Workbench Downloads[^], then under Server Administration -> Data Dump -> Advanced Export Options, you can select the No Data option, and this will give you a script which will contain all the Create Table statements for the selected database, without data. You can also select to include all stored procedures in the same script.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Hi,
I have two MySQL servers.. one is sourceMySQL and targetMySQL.
I want to update all the procedures on the targetMySQL using sourceMySQL without having to do it one by one.
Is there anyway to say:
UPDATE all procedures IN targetMySQL FROM sourceMySQL?
|
|
|
|
|
Suppose in my SQL2005 database accounting is used in LAN, their copy two files: data1.mdf and data1_log.LDF another folder and renamed data2_log.LDF data2.mdf and, then, di transfer these 2 files into the same directory that contains the original accounting database when attach data2.mdf file into SQL2005 error: Can not attach a database with the same name as an existing database there. While I have renamed then why not attach files? want to attach the data file do?
|
|
|
|
|
let me guess, you rename it from windows explorer, am i right?
it's okay, before you're going to press ok (proceed attach), at top of the form, there's "Attach As" column, change your database name below, good luck! 
|
|
|
|
|
|
Seriously you are going about this completely the wrong way, you are treating SQL Server like a single user database IT IS NOT. To transfer databases you need to use the database tools NOT the windows explorer copy/paste.
Stop, close down all your DB stuff and do some learning about SQL Server first, not by trying to use what little you know but by getting a book and studying it or work through some examples, try SQLServerCentral.com they are specialists in this area.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
assuming you have 2 SQL2005 database: data1 and data2, I Copy stored procedures from data2 to data1 why not? I want to copy stored procedures through other databases you do?
|
|
|
|
|
I assume each database has the same table structure so the stored procedure will actually work!
You need to create a script of the stored proc and run the script in each database. Using SSMS right click on the procedure and script as create to a new query window. Then change databases and run the script.
Lean how to use the Management Studio!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I do not want to create new, I want to Copy stored procedures to another database and to quickly change their
|
|
|
|
|
Which just reinforces my comment on the above thread, you have no idea what you are doing, get a book and do some reading on how databases work.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I have 2 SQL servers (MS SQL and ORACLE).
I an running an update openquery (from MS SQL) in order to update a table from Oracle.
My select from openquery is not based on a simple select from one table; it is using few inner joins among tables from both servers.
When I am runnig the update I receive the following error:
<b>ROW-00009: Cannot update row in a read-only rowset ]</b>
As far as i read on the net, I saw that there is not possible because of too complex code.
Does anyone have any idea if there exists any workaround for this?
Thanks a lot.
-- modified 6-Jul-12 3:40am.
|
|
|
|
|
mikcutu wrote: My select from openquery is not based on a simple select from one table; it is using few inner joins among tables from both servers.
How about doing those inner joins, and put the result in a temporary table? That way you'd have a "simple" table to update.
That way you'd also see whether the error is in the query itself, or during the update.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hello Eddy,
Thanks for reply me. In the end, my update should be in a final table and this means to have also a join between the final table and the temporary one but this makes me thinking to try using a view for entire join selection and use it into the openquery.
I will try this and let you know.
Cheers,
Mikcutu.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
