|
DisIsHoody wrote: database contains >10,000 records
DisIsHoody wrote: Previously we were using Access as the front end
I suspect your database design is a complete disaster, probably evolved over a number of years by a power user (possibly yourself) and now you want to use it seriously. I make the STRONG suggestion to have a professional look at it.
Your database probably needs a complete redesign, and your existing data migrated to the new structure. PLEASE do not do this yourself, get a professional otherwise you will be back here in a year or two needing to do it all over again.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know the pain of migrating an application from MS-Access to SQL server ... I'm in the middle of it now.
You might want to take a strategy like this ...
You have a grid full of records that a user may update.
Each record should have two additional fields: modified_datetime,modified_by
Create a button (or something) which puts the record into "edit" mode; at this time, re-fetch the record so that you are sure you have the most current version.
During the "save" of the record, you will need to re-fetch the record and check that the modified_datetime has not changed, if no change then update the record with your new values and updated modified_datetime,modified_by values.
If someone has modified the record, you can inform your user that someone has already modified the record and he/she has to "re-edit" the record.
Something to think about.
I also agree with the other members that you might want to take this time to re-design your database design and not do a straight Ms-Access to MS-SQL import.
|
|
|
|
|
Not a very good Problem you are experiencing. I once came across the Problem , but mine was different , i was having Concurrency issues with 50 000 users. Luckily the System was written in a manner that another use must not See other user changes when he is busy until he is done and Committed the Data.
The Problem here is that a Dataset is ADO.NET Feature and it means you connect to the database and take Data and disconnect , and when you want to do some updates, you reconnect and do the Update and disconnect again.
So it will be a bit Difficult to Update the Data on other machines unless they query again. This is more difficult in Windows Applications. This is what i like about CodeProject, People Come with Real Problems , i think this will lead me to write an Article about this, Hope to write it soon. i will see what i can do tonight.
Good Luck
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Believe me...I know the database needs a whole re-write. It was originally designed by the president of the company when the formed the business. We have been trying to get him to hire a professional to completely re-write it, however from his perspective it works right now so why should we change it. So unfortunately I'm stuck working with the database in it's current format. As for the idea of having a column to monitor the data/time the record was last updated...I think this will work pretty good for the time being. Thanks for the idea. I appreciate all the responses.
Dominick
|
|
|
|
|
Sounds to me like the program should be re-written, not the database. Of course this may be a simplistic accessment because I am not familiar with your problem domain, but, why would you have the program always load the entire database rather than just the record currently being worked on? Most progams that work with a database have some sort of search function to allow a user to find the record they need (returning only minimal identifying data), then a detail form/page that is used to work on that record. This way you are never loading much more data than you need and since the record is getting loaded just before working on it and saved immediately after, it should always be relatively current. The only concurrancy problem you have then is if two users are working on the same record at once.
Hope this helps.
Kevin Rucker, Application Programmer
QSS Group, Inc.
United States Coast Guard OSC
Kevin.D.Rucker@uscg.mil
"Programming is an art form that fights back." -- Chad Hower
|
|
|
|
|
is there any way to determine what's the table physical size (in bytes)?
|
|
|
|
|
Yes[^]
Would you want to try to Google the answers to the other questions yourself, or would you like me to post links to various sections of the manual?
I are Troll
|
|
|
|
|
That depends on the density of the disk.
|
|
|
|
|
Hi,
How can I read the database physical file size?
|
|
|
|
|
You need to query the information_schema database, for this and your other posts. Can I suggest you install a graphical front end for MySQL (I'd recommend HeidiSQL[^]) - apart form showing you all you need to know about this, you can view the SQL it uses to query the database in it's output window.
|
|
|
|
|
|
Hi,
How can I get the server properties in mysql, for example, physical size?
Regards,
|
|
|
|
|
Hi,
I have a website with a textbox.. user will write the sql query in the textbox.. how can I execute the code on mysql?
Thanks,
|
|
|
|
|
With crossed fingers.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I suggest you read up on SQL Injection
|
|
|
|
|
That's not a good practice But If you want to go ahead with this only then I can provide you little suggestion.
Just Put you query inside Execute() and give SqlCommand To Execute .
|
|
|
|
|
Very simple, you need to embedded texts of textbox in the paramater of SQLCommand function and then pass connection object and then Execute it.
|
|
|
|
|
Hi,
I tried it and it's working BUT it's just executing the first query so f i type any other quesry it will just show the first query result with no error unless i signout my application or restart it.. why is this?
here is the code:
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Panel runat="server" ID="panelError" BorderWidth="1" BorderColor="Red" style="padding:10px" Visible="false" BorderStyle="Solid" HorizontalAlign="Left">
<dxe:ASPxLabel ID="lblErrorTitle" runat="server" Text="Error" Font-Bold="true" ForeColor="Red" /><br /><br />
<dxe:ASPxLabel ID="lblErrorDetails" runat="server" Text="" />
</asp:Panel>
<dxtc:ASPxPageControl ID="ASPxPageControl1" runat="server" ActiveTabIndex="0" Height="250px" Width="100%">
<TabPages>
<dxtc:TabPage Text="Query Editor" TabStyle-HorizontalAlign="Center" TabStyle-VerticalAlign="Middle">
<Controls>
<dxe:ASPxMemo ID="txtQuery" runat="server" Height="200px" Width="100%" /><br /><br />
<asp:Button runat="server" ID="btnExecute" Width="100px" Text="Execute" OnClick="btnExecute_Click" />
</Controls>
</dxtc:TabPage>
</TabPages>
</dxtc:ASPxPageControl>
<br /><br />
<dxwgv:ASPxGridView ID="gridQuery" runat="server" AutoGenerateColumns="True"
CssFilePath="~/App_Themes/Plastic Blue/{0}/styles.css" CssPostfix="PlasticBlue"
Width="100%">
<Columns />
<Images ImageFolder="~/App_Themes/Plastic Blue/{0}/">
<CollapsedButton Height="9px"
Url="~/App_Themes/Plastic Blue/GridView/gvCollapsedButton.png" Width="9px" />
<ExpandedButton Height="9px"
Url="~/App_Themes/Plastic Blue/GridView/gvExpandedButton.png" Width="9px" />
<HeaderSortDown Height="11px"
Url="~/App_Themes/Plastic Blue/GridView/gvHeaderSortDown.png" Width="11px" />
<HeaderSortUp Height="11px"
Url="~/App_Themes/Plastic Blue/GridView/gvHeaderSortUp.png" Width="11px" />
</Images>
<SettingsPager ShowDefaultImages="False" PageSize="30">
<AllButton Text="All">
</AllButton>
<NextPageButton Text="Next >">
</NextPageButton>
<PrevPageButton Text="< Prev">
</PrevPageButton>
</SettingsPager>
<Styles CssFilePath="~/App_Themes/Plastic Blue/{0}/styles.css"
CssPostfix="PlasticBlue">
<Header ImageSpacing="10px" SortingImageSpacing="10px">
</Header>
</Styles>
<SettingsBehavior AllowDragDrop="False" AllowGroup="False" />
</dxwgv:ASPxGridView>
</ContentTemplate>
</asp:UpdatePanel>
and here is the background code:
try
{
data_table = new DataTable();
string sql_statement = "SQL" + DateTime.Now.ToString("ddMMMyyyyHHmmss");
sql_connection = new MySqlConnection((string)Session["connection_string"]);
sql_connection.Open();
sql_command = new MySqlCommand("PREPARE " + sql_statement + " FROM '" + txtQuery.Text + "'; EXECUTE " + sql_statement + "; DROP PREPARE " + sql_statement + ";", sql_connection);
sql_command.CommandType = CommandType.Text;
sql_adapter = new MySqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
gridQuery.DataSource = data_table;
gridQuery.DataBind();
}
catch (MySqlException exp)
{
lblErrorDetails.Text = exp.Message;
panelError.Visible = true;
return;
}
finally
{
if (sql_reader != null) sql_reader.Close();
if (sql_connection != null)
{
if (sql_connection.State == ConnectionState.Open)
sql_connection.Close();
}
}
|
|
|
|
|
Hi,
How can I determine what's the current server name in mysql?
|
|
|
|
|
Does MySQL have a server?
|
|
|
|
|
Hi,
How can I determine the default character set and collation for the mysql server?
|
|
|
|
|
Use ServerProperty() Function.
See MSDN[^] For more details.
|
|
|
|
|
Hello
I'm trying to run some big delete statements and keep running into the following exception:
Error - The transaction log for database 'SomeDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases, Line: 16
I've already created a 20GB log file for it, still failing
dev
|
|
|
|
|
I know this is a long shot, however I would look for an explanation in the log_reuse_wait_desc column in sys.databases, more particularly at line 16.
And if that does not help, at least tell us what you see there.
|
|
|
|
|
Are you using transactions ?
If so, maybe you need to change your algorithm so that you are not deleting so many rows at one time.
Something like:
select key from mytable into #Temp1
Loop
begin transactioin
delete some rows where select top 1000 from #Temp1
delete same rows from #Temp1
Commit
Until No more rows in #Temp1
Otherwise, if this is part of a one-time data cleanup, then you might want to
A) Get your users off the database, so that you can do system maintenace.
1) Take a full backup of the database
2) switch the recover mode of the database to "simple"
3) Perform your delete
4) Take another full backup
5) Put the database back into the recovery mode you had before
6) Allow your users back into the database
Just some ideas.
Good luck.
|
|
|
|