Insert Variable along with data in SQL

Create a table with some data in it:

       

create table #data (name varchar(500))
insert into #data (name)
select 'Texas'
union 
select 'LA'
union 
select 'Kansas'
union 
select 'New York'

-- Create Dest Table 
create table #dest (id int, name varchar(500))

-- Select the code below to insert into #dest table 
declare @vid int
set @vid = 1 <-- Any value 

insert into #dest (id, name)
select @vid, s.name from 
(select name from #data) s 
------------------------------------------------------

insert into #dest (id, name)
select @vid , (select top 10 name from #data) <-- This will give subquery return more than 1 row error message 

 

Javascript breakpoint VS 2013

In VS 2013, there is a bug that wont let debugger to stop at the breakpoint.

The workaround is to add "debugger" keyword in js code before the line of breakpoint and then debugger will stop / hit the breakpoint.

Here is the MS connect solution

HTH

How to read data from RadGrid?

Hello,

While working on RadGrid, I was in need of real data bind to Grid. I was able to access text of GridViewItem using (gridItem("col1").Text) but if text is formatted, then only you can see is the formatted data.

To see the exact data bind to Grid, use the DataItem property as shown below:

Private Sub RadGrid1_ItemDataBound(sender As Object, e As GridItemEventArgs) Handles RadGrid1.ItemDataBound

    Dim gridItem As GridDataItem = CType(e.Item, GridDataItem)
    Dim data as String = DirectCast(gridItem.DataItem, System.Data.DataRowView).Row.ItemArray(0)

End Sub


HTH

SQL Date Comparison

select case when '2014-07-23 19:00:00' = 'Jul 23 2014 7:00PM' then 1 else 0 end
-- This is string to string comparison

Result will be 0



select case when CAST('2014-07-23 19:00:00' AS DATETIME ) = 'Jul 23 2014 7:00PM' then 1 else 0 end
-- This is datetime to implicit datetime comparison

Result will be 1


select case when '2014-07-23 19:00:00' = CAST('Jul 23 2014 7:00PM' AS DATETIME) then 1 else 0 end
-- This is implicit datetime to datetime comparison

Result will be 1

Hide / Show Custom Errors in Asp.net

If you are seeing "Runtime Error" in your asp.net page and would like to see detail and actual line where the error is happening, this is what we need to do:

In Web.config file, look for tag "customErrors" tag and set the value as:


<customErrors mode="On" />        THIS WILL HIDE ERROR AND SHOW RUNTIME MESSAGE
<customErrors mode="Off" />        THIS WILL SHOW REAL MESSAGE


HTH

Install Assembly in GAC

How to install assembly in GAC?

The very simple way would be to use command prompt. Sounds difficult but it's not.

To have Gacutil installed, either need some version of Windows SDK or
if you cannot install SDK, then copy following 3 files from the machine where Visual Studio or SDK is already installed. I copied them from  C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools

The files are:

• gacutil.exe
• gacutil.exe.config
• 1033\gacutlrc.dll     (can be placed in the same dir as gacutil.exe on the server)

Then go to cmd and get your directory set where gacutil.exe is placed.
Run following:

 gacutil.exe -I <dllfilename>     to install the DLLs in the GAC.


Assembly Locations:
For FW 2.0, its C:\Windows\assembly

For FW 4.0, its C:\Windows\Microsoft.NET\assembly\

In folder above, you can look in 32 or 64 or MSIL folder.
 

How to pass NULL to SelectParameters in SQLDatasource?

Here is the problem
 
How to pass NULL to SelectParameters in SQLDatasource from .aspx or codebehind file?
 
.aspx Method

Two things:
1. First set the SqlDataSource Property CancelSelectOnNullParameter = false
2. Second set the parameter property ConvertEmptyStringToNull="true"
 
Example:

<asp:SqlDataSource ID="ds1" runat="server" ConnectionString='<%$ ConnectionStrings:dbConnString %> 'SelectCommand="someSelect @param1=@param1" CancelSelectOnNullParameter="false">

<SelectParameters>

<asp:Parameter Name="param1" Type="String"  ConvertEmptyStringToNull="true" />

</SelectParameters>

</asp:SqlDataSource>

-------------------------------------------------------------------------------------------------------------------

Codebehind Method

To pass from codebehind, the parameter need to be set in the proper event of Datasource.
Also no other properties of parameter need to set in aspx. Just declare a parameter.
In example below, I am passing null during "Select" call, so the code will look as:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
        If e.Command.Parameters("@param1").Value = "" Then
            e.Command.Parameters("@param1").Value = System.DBNull.Value
        End If    
End Sub


HTH