saqdotnet: 2014

Monday, December 8, 2014

How to send images in Mail vb.net?

Create Mail message object
Build HTML string for images
Add HTML string in AlternateView
Add images as LinkedResources in AlternateView
Add AlternateView to Mail message.

Below code will demonstrate the steps:

 
 Dim fileNames As String() = System.IO.Directory.GetFiles("C:\imageFolder\", "*.png")  
 Dim mailMsg As MailMessage = New MailMessage()  
 Dim imageHTML as String = String.Empty  
 Dim htmlView As AlternateView
   
 For i As Int16 = 0 To fileNames.Count - 1  
      imageHTML = imageHTML & "<table style=""padding:0px;margin:0px;border:2 solid black;""><tr><td valign=""top"" align=""left""><img width=720 height=930 src=cid:" & System.IO.Path.GetFileName(fileNames(i)) & "></td></tr></table>"  
 Next  

 htmlView = AlternateView.CreateAlternateViewFromString(imageHTML, Nothing, "text/html")
  
 For i As Int16 = 0 To fileNames.Count - 1  
      ' Linked source take physical image path to embed image.  
      Dim imagelink As LinkedResource = New LinkedResource(fileNames(i))  
      imagelink.ContentId = System.IO.Path.GetFileName(fileNames(i)) 'imgFile.Name  
      imagelink.TransferEncoding = System.Net.Mime.TransferEncoding.Base64  
      htmlView.LinkedResources.Add(imagelink)  
 Next  

 mailMsg.AlternateViews.Add(htmlView)

Happy Coding!

Wednesday, October 22, 2014

Data at the root level is invalid. Line 1, position 1

If you getting error mention in subject, use the following way to load file:

   
   Dim iXmlDocument As XmlDocument  
   iXmlDocument = Getxmldocument(sFilePath)  
   LoadNodes(iXmlDocument)  
        
   Function Getxmldocument(ByVal sFilePAth As String) As XmlDocument  
   
     Dim xmlDOC As New XmlDocument  
   
     Using myXmlTextReader As New XmlTextReader(sFilePAth)  
       myXmlTextReader.XmlResolver = Nothing  
       Dim settings As XmlReaderSettings = New XmlReaderSettings()  
       settings.ValidationFlags = Schema.XmlSchemaValidationFlags.None  
       settings.ProhibitDtd = False  
       settings.ValidationType = ValidationType.None  
   
       Using reader As XmlReader = XmlReader.Create(myXmlTextReader, settings)  
         result.Load(reader)  
         reader.Close()  
       End Using  
   
       myXmlTextReader.Close()  
       settings = Nothing  
     End Using  
   
     Return xmlDOC  
   End Function  
      
   ' load nodes...  
   Public Function LoadNodes(ByRef xmlFileReader As XmlDocument) As Boolean  
   
     Dim xNodeList As XmlNodeList  
     Dim xNode As XmlNode  
     xNodeList = xmlFileReader.SelectNodes("/Tag1/innerTag1")  
     ......  
     .......  
   End Function

Friday, September 26, 2014

Access RadGrid on Client using Javascript/Jquery

To access RadGrid on client, use following code.
I will also show the code that doesn't work:

// didnt work. .Net crash saying The Controls collection cannot be modified because the control contains code blocks (i.e. <%--<% %>--%>)
<%--var grid1 = $find("<%= RadGrid1.ClientID%>");--%>

//didnt work. This just gives Null.   
<%--var grid1 = $find("<%# RadGrid1.ClientID%>"); --%> 

//didnt work. This just gives Null.  
<%--var grid1 = document.getElementById('<%# RadGrid1.ClientID%>') --%> 

//didnt work. It give me ID of RadGrid; not a reference to object.  
var grid1 = document.getElementById('RadGrid1').id;  

//This gives reference as htmldiv control which still cant be used to access functions of radgrid.  
var grid1 = document.getElementById('RadGrid1')

  
// THIS WORKS **********************************************
  
var grid = $find(document.getElementById("RadGrid1").id);

var masterTable = grid.get_masterTableView();
  
//********************************************

Thursday, September 25, 2014

Add js reference in aspx page

Hello All,

While working on some js code, I couldn't figure out that why my function declared in js external file not being fired from LinkButton.
I had reference inside head tags which was WRONG.
The reference need to be inside <body> under <form> tag.

<body>
<form id="form1" runat="server">
<script type="text/javascript" src="scripts/grid.js" ></script>
.
.
.
.

Tuesday, September 23, 2014

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

Monday, September 22, 2014

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

Wednesday, September 10, 2014

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

Monday, July 28, 2014

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

Thursday, June 26, 2014

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

Monday, June 9, 2014

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.

Wednesday, June 4, 2014

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

Monday, June 2, 2014

Replace Single Quotes in SQL

You need to use double quotes to represent single quote for SQL to understand:

For instance, to replace single quote with empty space:
(I have colored red for input quotes and non red for replacements for easy reading)

Replace(@string, '''','')

To replace single quote with two single quotes, it would be

Replace(@string,'''','''''')

Just remember, 2 quotes for each single quote, plus opening and closing quotes.

HTH

Thursday, March 6, 2014

How to write SQL CLR in VB 2013

Hi,

Its not very obvious to open new project in VS 2013 in VB to find option for SQL CLR.
What you need to do is following:

Open VS 2013

Go to New Project

Dialog box will Appear

Expand Other languages and click on SQL Server

Click on SQL Server Database Project and click ok

When the project load, right click on project and click Add new item

Now to change the project in VB, right click on project and go to properties.

Select SQL CLR tab.

There you will see Language option.

Change it to Visual Basic.

Now right click on project and click Add new item

Now you this time you will see SQL CLR VB in left menu.

Select it and then add desired file according to need to build Store procedure or function.

Happy coding!

Tuesday, January 14, 2014

Alter Database failed because a lock could not be placed on a database

I was trying to take offline my database in SQL Server but getting above error. Usually there is an open connection to a database which need to be killed.

To view connections:
EXEC sp_who2

Check for any connections open to the database and get the SPID.
Now execute:
KILL <SPID>

SPID is sql server process ID which is assigned by SQL Server when connected to database.

HTH

Monday, January 13, 2014

Change Server Name in Crystal Reports

Changing Server Name is not very obvious but very easy.

1. In field explorer, Right Click on Database Fields.

2. Click on Set DataSource Location. It will open following window

3. Expand the Properties of the existing connection and right Click on Data Source as shown below

4. Click Edit, and type in the NEW Server Name and hit enter. It will change server name for all sub reports too.

5. Test the report before moving to production.

HTH.