Database

Hum Dum wrote:

SELECT @address = CONVERT(VARCHAR(150),[TEXT]) FROM OPENXML(@idoc,'/Name_Address/address', 0) where [text] is not null

SELECT @address = COALESCE(@address+',' ,'') + [address]
FROM 
(
SELECT    * FROM       
OPENXML (@idoc, 'Name_Address/address',3)
WITH ([address]  varchar(1000) '.')
) P
SELECT @address

DisIsHoody wrote:

load the records into a dataset

DisIsHoody wrote:

database contains >10,000 records

DisIsHoody wrote:

Previously we were using Access as the front end

<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 &gt;">
        </NextPageButton>
        <PrevPageButton Text="&lt; 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>

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();
    }
}