Add an AutoNumber column as primary key field in Access (2003/7) through DDL

Here is the syntax:

Alter TABLE XYZ
Add column myID  COUNTER CONSTRAINT PrimaryKey PRIMARY KEY 

If we want to set the seed for the AutoNumber field, we can run this query:
alter table XYZ
alter column myID Counter(1000,1);

 

There is a documnet from Microsoft to demostrate:

How to Create and Drop Tables and Relationships Using SQL DDL

refer to this link: http://support.microsoft.com/?id=116145

You can also find more information on this topic from this document:

(How to use common Data Definition Language (DDL) SQL statements for the Jet database engine)

http://support.microsoft.com/default.aspx?scid=kb;en-us;180841

 

 

Advertisements

Radiobuttonlist with a tooltip context from database

I came across this request and solved with a solution by adding listitems from code and add Titile attribute to the listitems for the tooltip content.
(http://forums.asp.net/t/1487353.aspx)
The forum user came back one month later with extra but I cannot make it work with the internal property tweak. With the clue from the user research, I continued with the search and found another solution with CSS style. I dropped the title property solution with a hyperlink which includes listitems and contents for tooltips.
The style I grabbed from this link: http://jlhaslip.trap17.com/samples/tooltips/index.html
Finally, I came up with the solution for the new requirement from this thread: http://forums.asp.net/p/1500095/3546469.aspx#3546469
 
I post the answer here for future reference:

Here is a link of the style you will use for your tooltips:

http://jlhaslip.trap17.com/samples/tooltips/index.html

Here is the CSS style from the link:

text/css">
 /*
        =================================
        start of Tooltip css code here
        ================================= */
 
        a.info{
        position:relative;           /*this is the key*/
        z-index:24;
        background-color:#e0e0e0;    /* background colour of display text */
        color:#000000;               /* colour of display text */
        border:1px dotted #999;    /* border colour */
        text-decoration:none;
        font-style:italic;
        }
 
        a.info:hover {
        z-index:25;
        background-color:#ffff66;
 
        }
 
        a.info span{
        display: none;  /* hide the span text using this css */
        }
 
        a.info:hover span{ /*the span will display just on :hover state*/
        display:block;
        position:absolute;
        top: 1.5em;
        left: 3em;
        width:15em;
        border:1px solid #ff0000; /* border colour */
        background-color:#ffff99; /* background colour here */
        color:#000000;         /* text colour */
        text-align: center;
        font-size: .8em;
        font-style:italic;
        z-index:30;
        }
 

You need to add a hyperlink and define a <span> tag for the tooltip text of your RadiobuttonList in your code:

string myLink = "<a href=\"#\" class=\"info\">" + rblReader_ChangeTypes["Name"].ToString() + "<span>"+rblReader_ChangeTypes["ToolTip"].ToString()+"</span></a>"

;

 The code block:

string connectionString = ConfigurationManager.ConnectionStrings["forumConnectionString"].ConnectionString;

SqlConnection sqlConn1 = new SqlConnection(connectionString);

SqlCommand sqlCmd_GetChangeTypes = new SqlCommand("Select ID, Name, tooltip FROM [aTable]", sqlConn1);

sqlConn1.Open();

SqlDataReader rblReader_ChangeTypes = sqlCmd_GetChangeTypes.ExecuteReader();

if (rblReader_ChangeTypes.HasRows)

{

while (rblReader_ChangeTypes.Read())

{

string myLink = "<a href=\"#\" class=\"info\">" + rblReader_ChangeTypes["Name"].ToString() + "<span>"+rblReader_ChangeTypes["ToolTip"].ToString()+"</span></a>";

ListItem li = new ListItem(myLink, rblReader_ChangeTypes["ID"].ToString());

//li.Attributes.Add("title", rblReader_ChangeTypes["ToolTip"].ToString());

//li.Attributes.Add("class", "info");

li.Attributes.Add(

"ID", rblReader_ChangeTypes["ID"].ToString());

rblChangeType.Items.Add(li);

//Source for Tooltip

// http://jlhaslip.trap17.com/samples/tooltips/index.html

}

rblChangeType.SelectedIndex = 0;

}

else

{

// logic for no rows had been returned

}

rblReader_ChangeTypes.Close();

sqlConn1.Close();


Bulk Insert to load text data file to SQl Server with Format File

You can use BULK INSERT command to load data into Sql Server like BCP command.
The simple form T-SQL sytax is:
 
BULK INSERT dbo.yourtable
FROM ‘C:\myData.txt’
 
You can find detailed BULK INSERT syntax from this link:
 
You can use a Format File to control your table columns to match up with your data file columns. You can use this bcp command to generate a format file for your table:
Format file in XML format:

bcp mydb..Book1SkipOneColumn format nul f Default.xml c x T

Format file in non-XML format:

bcp mydb..Book1 format nul f Default2.fmt c T

You can find more information about format file here: http://technet.microsoft.com/en-us/library/ms190393.aspx

By using a format file, you can skip a table column or skip a data column or mapping columns between data file and the table. You can find some sample from this link: http://technet.microsoft.com/en-us/library/ms190396.aspx

It seems the syntax in the sample will not work with file extension .csv file but I need to get a confirmation on this later.

I have included a sample ASP.NET page through a button click to upload a text data file to SQL Server 2005:

protected void Button2_Click(object sender, EventArgs e)

{

string ConnectionString = ConfigurationManager.ConnectionStrings["forumConnectionString"].ToString();

SqlConnection con = new SqlConnection(ConnectionString);

using (SqlCommand cmdInsert = new SqlCommand(@"BULK INSERT myTestSkipCol FROM ‘C:\datatest\Book1Noheader2.txt’ WITH (FORMATFILE = ‘C:\datatest\Default2.fmt’)", con))

{

con.Open();

cmdInsert.ExecuteNonQuery();

con.Close();

}

}

Another one without format file:

protected void Button1_Click(object sender, EventArgs e)

{

string ConnectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ToString();

SqlConnection con = new SqlConnection(ConnectionString);

using (SqlCommand cmdInsert = new SqlCommand(@"BULK INSERT Book1 FROM ‘C:\datatest\Book1.csv’ WITH (FIRSTROW = 2, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’)", con))

{

con.Open();

cmdInsert.ExecuteNonQuery();

con.Close();

}

}