SQL 分頁

Restore Indentity/auto number to 1

in the database, we would like to restore the identity back to 1, we can use the following methods:

1) drop table and recreate again

2) restore the identity to false and get back to true

3) type this sql:

DBCC CHECKIDENT(‘TableNAME’, RESEED, 1)

the last one is taught by Ricky

SQL IN Statement with variable/parameter

DECLARE @OrderList varchar(500)

SET @OrderList = ”’admin”, ”Brand Guest”’     //呢度係 ‘  ‘   ‘   , 3 個’ 組成~~

DECLARE @SQL varchar(600)

SET @SQL = ‘SELECT * FROM [WebDB].[dbo].[A_LoginTrack]WHERE LT_LOGNAME IN (‘ + @OrderList + ‘)’

PRINT @SQL   // print sql statement

EXEC(@SQL)

Datalist ([Eval("XXX") ])

<asp:DataList ID=”DataList3″ runat=”server” DataSourceID=”SqlDataSource3″ RepeatColumns=”4″ CellPadding=”20″ CellSpacing=”20″
                ShowFooter=”False” ShowHeader=”False”>
                <HeaderTemplate>
               
                </HeaderTemplate>
                <ItemTemplate>
                    <a href=’<%# Eval(“AP_LINK”, “?getgroup=”)%><%# Eval(“AP_PAGEGROUP”) %>’ rel=’lightbox[<%# Eval("AP_THUMB") %>]‘ id=’<%# Eval(“AP_UID”) %>’
                        title=’<%# Eval(“AP_ALT”) %>’>
                        <img src=’<%# Eval(“AP_THUMB”, “images/press/{0}”)%>’ alt=’<%# Eval(“AP_ALT”) %>’ title=’<%# Eval(“AP_ALT”) %>’ border=”0″ /></a>
                </ItemTemplate>
                <HeaderStyle Font-Bold=”False” Font-Italic=”False” Font-Overline=”False” Font-Strikeout=”False” Font-Underline=”False” Wrap=”False” />
            </asp:DataList>
            <asp:SqlDataSource ID=”SqlDataSource3″ runat=”server” ConnectionString=”<%$ ConnectionStrings:WebDBConnectionString %>” SelectCommand=”SELECT * FROM [A_Press] WHERE (([IS_ACTIVE] = @IS_ACTIVE) AND ([AP_PAGE] = @AP_PAGE) AND (YEAR([AP_POSTDATE]) = @AP_POSTDATE))”>
                <SelectParameters>
                    <asp:Parameter DefaultValue=”1″ Name=”IS_ACTIVE” Type=”Int16″ />
                    <asp:Parameter DefaultValue=”0″ Name=”AP_PAGE” Type=”Int16″ />
                    <asp:QueryStringParameter DefaultValue=”2009″ Name=”AP_POSTDATE” QueryStringField=”year” />
                </SelectParameters>
            </asp:SqlDataSource>

break sql statement

係一句sql statement , 係前面加 @ , 咁就可以直接click enter 分行~~

OleDbCommand cmd1 = new OleDbCommand(@”INSERT INTO [orders] (customer_name, customer_address, customer_suburb,
customer_postcode, customer_state, card_type, card_number, card_expire, order_total, order_date) VALUES (‘@name’, ‘@address’, ‘@suburb’, @code, ‘@state’,
‘@type’, @number, ‘@expire’, @total, ‘@date’)”, conn1);