Friday, June 20, 2008

String replacement with Tex or nText fields

Problem: How can I replace a string with another one in a field of Text or nText datatype?

Considerations: If the field was of VarChar or nVarChar type, there is no problem, 'cause in SqlServer exists a function (like in T-Sql) that do it in native mode: the "Replace" function. But this function doesn't work on Text and nText fields. So, we have to use a script builded with another function: UpdateText

declare @OldText varchar(1000)
set @OltText= 'Old'

declare curs cursor local fast_forward
    charindex(@OldText, Field)-1
    '%' + @OldText +'%'

declare @NewText varchar(1000)
set @NewText= 'New'

declare @txtlen int
set @txtlen = len(@OldText)

declare @ptr binary(16)
declare @pos int
declare @id int

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
    updatetext Table.Field @ptr @pos @txtlen @NewText

    fetch next from curs into @id, @ptr, @pos

close curs
deallocate curs

Table is the reference table
Field is the field where the text will be replaced
rowid is the table id (in this example it's of int type, so the @id variable are set like int, for other datatypes change the declaration of @id variable)

Tuesday, June 10, 2008

Database Connection String for Web.Config

Access Database
  <add name="AccessConnectionString"
   Data Source=|DataDirectory|dbname.mdb"
   providerName="System.Data.OleDb" />

ODBC Database
  <add name="ODBCConnectionString"
   providerName="System.Data.Odbc” />

SQLServer database
  <add name="OLEDBConnectionString"
   connectionString="Server=servername;Integrated Security=SSPI;Database=dbname;"
   providerName="System.Data.SqlClient" />

Excel Connection
  <add name="OLEDBConnectionString"
   Data Source= filename.xls; Extended Properties=Excel 8.0;"; />

.csv file Connection
  <add name="OLEDBConnectionString"
   connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filename.csv;

Moreover you can use native connection methods for Oracle.

Wednesday, June 4, 2008

Ajax - Response.Write with UpdatePanel

Usually, the Response.Write() method inside an UpdatePanel doesn't work.
To solve this problem, a workaround is to insert a LinkButton out of the UpdatePanel and to associate this control with the postback generated from the object inside the panel.

<asp:UpdatePanel ID="upd" runat="server">
    <asp:Button runat="server" ID="BUTTON" Text="Click" />
<asp:LinkButton ID="ALinkButton" runat="server"></asp:LinkButton>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    AddHandler Me.ALinkButton.Click, AddressOf FUNCTION
    Me.BUTTON.Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(Me.ALinkButton, ""))

    If Not Page.IsPostBack Then
    End If
End Sub

Protected Sub FUNCTION(ByVal sender As Object, ByVal e As System.EventArgs)
End Sub