How to add Tootip for each item of Radiobutton from database.

Posted: October 30, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
You cannot assign a tooltip for each if you use a datasource for your radiobutton with both .DataTextField  and .DataValueField assigned to two columns from the datasource.
To solve this problem, you can use a listitem to add both text and value as a listitem and add an attribute to this listitem for the "title" which will behavior as tooltip.
Here is the code snippet:
 

string connectionString = ConfigurationManager.ConnectionStrings["test_for_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())

{

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

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

rblChangeType.Items.Add(li);

}

rblChangeType.SelectedIndex = 0;

}

else

{

// logic for no rows had been returned

}

rblReader_ChangeTypes.Close();

sqlConn1.Close();

*****Edit*****************

***A follow up question to control the Duration of the tooltip**************

            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");
            
                  rblChangeType.Items.Add(li);

                //Source for Tooltip
                // http://jlhaslip.trap17.com/samples/tooltips/index.html
              
            }

 

Style:

 <style type="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;
        }
 

Here is a link of the style for your tooltips:

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

 

Due dates calculation with SQL or in C#

Posted: October 30, 2009 | Author: | Filed under: SQL Server | Leave a comment
Based on invoice date to decide when the due date is. If we decide a business rule for this is: take 25 as a cutoff date.
Here is a solution in SQL:

SELECT Vendorname, Invoicedate,

CASE  WHEN Day(Invoicedate) <= 25 THEN Dateadd(DAY, 9, Dateadd(Mm, Datediff(M, 0, Invoicedate) + 1, 0))

ELSE Dateadd(DAY, 9, Dateadd(Mm, Datediff(M, 0, Invoicedate) + 2, 0))

END AS Paydate FROM @t

 
 

We can work with datetime in C# to get another solution:

DateTime dt = new DateTime(2009, 12, 21);

//DateTime dt = new DateTime(2009, 10, 21);

if (dt.Day>25)

{

if (dt.Month>10)

{

DateTime Paydate = new DateTime(dt.Year+1, (dt.Month+2)%12, 10);

Response.Write(Paydate.ToString());

}

else

{

DateTime Paydate = new DateTime(dt.Year, dt.Month + 2, 10);

Response.Write(Paydate.ToString());

}

}

else

{

if (dt.Month > 11)

{

DateTime Paydate = new DateTime(dt.Year + 1, (dt.Month + 1) % 12, 10);

Response.Write(Paydate.ToString());

}

else

{

DateTime Paydate = new DateTime(dt.Year, dt.Month + 1, 10);

Response.Write(Paydate.ToString());

}

}

Setup Linked Server for Access 2007 in SQL Server 2008

Posted: October 29, 2009 | Author: | Filed under: SQL Server 2008 | Leave a comment

EXEC sp_addlinkedserver

@server = N’my2007Database’,

@provider

= N’Microsoft.ACE.OLEDB.12.0′,

@srvproduct

= N’OLE DB Provider for ACE 2007′,

@datasrc

= N’C:\datatest\Database1.accdb’

GO

— Set up login

EXEC

sp_addlinkedsrvlogin

@rmtsrvname = N’my2007Database’,

@useself

= N’TRUE’,

@locallogin

= NULL,

@rmtuser

= N’Admin’,

@rmtpassword

= NULL

GO

–****Resolve permission issue

USE

[master]

GO

EXEC

master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

GO

EXEC

master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

GO

–http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb

–******

–List the tables on the linked server

EXEC

sp_tables_ex N’my2007Database’

GO

–Test run for a table

SELECT

* FROM my2007Database…myTableInAccess

Uniqueidentifier data type in a parameter

Posted: October 28, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
When you create a parameter in VS for a SqlDataSource, the IDE add Type="Object" to the parameter. The query will not work without some modification. Here is a quick fix for this problem: Simply remove the type="Object" from the parameter and keep everything else as is.
Another way to solve this problem is to CAST the parameter to uniqueidenifier explicitly in the WHERE clause.
The following solution is what I used to answer a question for a querystringparameter:
 
1. Try this simple fix: remove Type="Object" from your querystringparameter.

<asp:QueryStringParameter Name="UserId" QueryStringField="UserID" />

Or

2. Add a CAST to your parameter in the WHERE clause if you do keepthe Type="Object" in the queryparameter:

…WHERE ([Userid] = CAST(@UseIid as uniqueidentifier))

 

How to get DataSet out of SqlDataSource

Posted: October 16, 2009 | Author: | Filed under: ASP.NET | Leave a comment
Here is a quick way to get a dataset from a SqlDataSource:
DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
DataSet ds = dv.Table.DataSet.Copy();

Set a datatime column to NULL instead of default database value in ASP.NET

Posted: October 15, 2009 | Author: | Filed under: ASP.NET | Leave a comment
When you set a parameter value to empty string, the datetime column in the table may use 1/1/1900 as a value. It works in many cases but sometime you just want a NULL value in that place. Here is a code snippet in VB.NET to show how to update the column to NULL:

Dim ESOConnection As SqlConnection

Dim MyUpdate As SqlCommand

ESOConnection =

New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ToString)

Dim strSQL As String = "UPDATE ARREST_INDEX SET LNAME = @LNAME,DOB = NULLIF(@DOB,”), " & _

"OCA = @OCA WHERE ARRESTNO = @ARRESTNO"

MyUpdate =

New SqlCommand(strSQL, ESOConnection)

MyUpdate.CommandType = CommandType.Text

MyUpdate.Parameters.AddWithValue(

"@LNAME", tx_lname.Text)

MyUpdate.Parameters.AddWithValue(

"@DOB", tx_dob.Text)

MyUpdate.Parameters.AddWithValue(

"@OCA", tx_oca.Text)

MyUpdate.Parameters.AddWithValue(

"@ARRESTNO", 1) ‘assign some value here

ESOConnection.Open()

MyUpdate.ExecuteNonQuery()

ESOConnection.Close()

Find SQL Server Trigger(s) associated with a table and how to enable/disable them

Posted: October 11, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
If you want to know whether there is any trigger associated with a specific table in your database, you can run the following query.

SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID(N‘yourTableName’)

Sometime you may need to turn the trigger on and off test your queries. You can use query snipt below to do that:

–Turn off your trigger

ALTER

table yourTableName

DISABLE

trigger myTrigger

–SELECT

* FROM sys.triggers WHERE parent_id =  OBJECT_ID(N‘yourTableName’) 

–turn on your trigger

ALTER

tableyourTableName

ENABLE

trigger myTrigger

–SELECT

* FROM sys.triggers WHERE parent_id =  OBJECT_ID(N‘yourTableName’)

There is a column named as is_disabled and you can find the value change in that column when you run the first query at the beginning.