How to handle single quote in SQL Query or DataSet.Select ?

Problem : When we try to select/filter records based on a column value which contains a single quote then the SQL server will give syntax error because there is a single quote in the start and at the end of SQL query normaly.

Solution 1:  Parameterized Query

We can use parameterized query to avoid the single quote issue like this:

string VendorName = “John O’Corner”;

command.CommandText = “SELECT * FROM Products WHERE VendorName= @VendorName”;

command.Parameters.Add(
       new SqlParameter(“@VendorName”, SqlDbType.VarChar, 50)).Value = VendorName;

Solution 2:  Replace Single Quote

Another option is that you can replace single quote with two single quotes. This will give an effect of a string end then string start in SQL Query. This method is especially suitable while we are selecting records in a dataset.

SystemDataSet.ProductRow[] productRows =  dataSet.Products.Select( ” VendorName = ‘” + VendorName.Replace( “‘”, “”” ) + “‘” );

How to access deleted rows in a typed dataset?

 

Sometimes after doing some processing on a dataset and before finally committing the changes in the database we need to know some information about added, modified, deleted or dispatched rows in the dataset. One of the well known approaches  is to loop the rows of a data set and check the ‘RowState’ value of each row which could be added, modified, deleted or dispatched. But when you are using a transaction in your code then you will notice that after updating the changes in database and before committing the transaction you will lose the deleted rows although you will still be able to get the rowstate of added and modified rows. This was irritating for me as i wanted to get the count of deleted rows in my code. The fortunately i came accross another nicer method of accessing added, modified, deleted and dispatched rows which is Table.GetChanges(‘RowsState’). In this way you can separately make tables for each kind of rows and then use them for your purpose. Here is an example of ‘Customer’ table.

 DataTable tableAddedRows, tableModifiedRows, tableDeletedRows, tableDispatchedRows;

 tableAddedRows = myDataSet.tables["Customer"].Rows()[0].Table.GetChanges( DataRowState.Added );
 tableModifiedRows = myDataSet.tables["Customer"].Rows()[0].Table.GetChanges( DataRowState.Modified); 
 tableDeletedRows = myDataSet.tables["Customer"].Rows()[0].Table.GetChanges( DataRowState.Deleted );
 tableDispatchedRows = myDataSet.tables["Customer"].Rows()[0].Table.GetChanges( DataRowState.Dispatched);

Now you can use these tables to do whatever you want. Please note that if there is no row of any one of these rowstates then its corresponding table will be null.

I hope this helps!

The SMTP server requires a secure connection or the client was not authenticated

Problem: The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first. c25sm1228430ika.11

 

 

Solution:

 

 

Public Shared Sub SendEmail()

 

 

    Dim Message As New Net.Mail.MailMessage()

 

    Dim FromEmail As New Net.Mail.MailAddress(“From Email Address”)

    Message.From = FromEmail

    Message.To.Add(“To Email Address”)

 

 

    Message.Subject = “Subject of the Email”

    Message.Body = “Body of the Email”

    ‘Message.SubjectEncoding = System.Text.Encoding.UTF8

    ‘Message.BodyEncoding = System.Text.Encoding.UTF8

    ‘Message.IsBodyHtml = False

    ‘Message.Priority = Net.Mail.MailPriority.High

 

    Dim SmtpClient As New Net.Mail.SmtpClient(“smtp.YourEmailServer.com”, PortNo eg: 587 for gmail )

    SmtpClient.EnableSsl = True

    ‘smtp.DeliveryMethod = Net.Mail.SmtpDeliveryMethod.Network

    ‘smtp.UseDefaultCredentials = False

 

SmtpClient.Credentials = New Net.NetworkCredential(“YourEmailAddress”, “YourEmailPassword”)

 

    SmtpClient.Send(Message)

 

 

End Sub

 

Note: An important point to note is that you have to set the EnableSSl to True before you set the NetWork Credentials of your SMTP client.

View zeeshan malik’s profile

How to call client-side Javascript function after an UpdatePanel asychronous (Ajax) request is over

If you are using AJAX then the only way i have found yet to give an alert to a user on return to the Asynchronous post back is to add an “end request” handler to the PageRequestManager.

In this way you can tell the request manager to run a javascript function on returning from a Asynchronous post back event of AJAX.

Code for doing this is :

function load()

{
   Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
}

where “EndRequestHandler” will be the name of your javascript function you want to call.
Call the above function in Onload event of <body> tag:

<body onload=”load()”>

function EndRequestHandler()

{

          alert(“You record has been saved successfully”);

}

Now If you want to give a different message based on your logic in server side code (code behind) then you can use a server side Hidden Field:

<input id=”hdnValue” type=”hidden” runat=”server”  value=”" />

Set its value in server side code on Asychronous Post Back: 

Protected Sub btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreateSample.Click

    If condition Then

hdnValue.value = “do this”

    Else     

hdnValue.value = “do that”

    End If 

End Sub

Now you can check the value of this Hidden Field in your Client Side EndRequestHandler function and give a different alert to user based on its value:

function EndRequestHandler()
{
     if (document.getElementById(‘<%= hdnValue.ClientID %>’).value == “do this”)

     { 
          alert(“You record has been saved successfully”);
     } 
     else
     {
          alert(“There is an error”);
     }
 }

View zeeshan malik’s profile