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

Posted: December 14, 2009 | Author: | Filed under: Access 2007 | 1 Comment
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

 

 

Radiobuttonlist with a tooltip context from database

Posted: December 4, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
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

Posted: December 3, 2009 | Author: | Filed under: SQL Server 2005 | 1 Comment
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();

}

}