0

Dynamic MS SQL 2005 Stored Procedure Parameters

Posted by Zu on March 16, 2011 in .Net, SQL
I had a function in my .NET application, that needed to do a search with an unknown number of parameters.
for example:
 select * from tbl where x=1 or x=2 or x=3 or x=4
Solution:

Pass in a comma seperated list, use a table function to split that out into a table and then use an IN clause. This article goes over doing that.

 
0

.NET DateTime Picker

Posted by Zu on December 29, 2010 in .Net

im my endless search for an DateTime Picker i have finally found one that’s awesome:

http://www.basicdatepicker.com/download/downloadproduct.aspx

 
0

SQL JOINs

Posted by Zu on November 29, 2010 in SQL

Assuming you’re joining on columns with no duplicates, which is by far the most common case:

  • An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
  • An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.

Examples

Suppose you have two Tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

… continue reading.

 
0

Migrate user Transactions

Posted by Zu on November 10, 2010 in .Net, Ektron

make sure people don’t get charged by changing testmode=true in the web.config.

I am using authorize.net test CC information for this.

Dim catalogApi As New CatalogEntryApi()
        Dim evtAPI As New Ektron.Cms.Framework.Calendar.WebEvent()

        Dim bapi As New BasketApi
        Dim oAPI As New OrderApi
        Dim uAPI As New CustomerApi
        Dim usAPI As New UserAPI
        Dim userId As Integer

        Dim connStr As String = ConfigurationManager.ConnectionStrings("DbConnection").ConnectionString
        Dim myConnection As SqlConnection = New SqlConnection(connStr)
        Dim sql As String = "select something from something "
        Dim myCommand As SqlCommand = New SqlCommand(sql, myConnection)
        Dim dr As SqlDataReader

        Try
            ‘ Execute the command
            myConnection.Open()
            dr = myCommand.ExecuteReader()

            ‘ Make sure a record was returned
            While dr.Read()
                userId = usAPI.GetUserByUsername(dr("username")).Id
                Dim udata As CustomerData = uAPI.GetItem(userId)

                Dim oData As New OrderData
                Dim aApi As New AddressApi
                Dim cAPI As New CountryApi
                Dim rApi As New RegionApi

                ‘set address from DB
                Dim adata As New AddressData
                Dim cdata As New CountryData
                Dim rdata As New RegionData

                adata.AddressLine1 = dr("street")
                adata.City = dr("city").ToString
                ‘set country data
                cdata = cAPI.GetItem(840) ‘USA
                adata.Country = cdata
                adata.IsValidated = True
                adata.Name = dr("first_name").ToString & " " & dr("last_name").ToString
                adata.Phone = dr("daytime_phone").ToString
                If (dr("zip").ToString = "") Then
                    adata.PostalCode = "00000"
                Else
                    adata.PostalCode = dr("zip").ToString
                End If

                ‘set region data
                Dim rCriterial As New Ektron.Cms.Common.Criteria(Of RegionProperty)
                Dim rRegion As Generic.List(Of RegionData)
                rCriterial.AddFilter(RegionProperty.AlphaCode, CriteriaFilterOperator.EqualTo, dr("state"))
                rRegion = rApi.GetList(rCriterial)
                For Each r As RegionData In rRegion
                    rdata = rApi.GetItem(r.Id)
                Next
                ‘if state does not match, set it to maryland
                If rdata.Id = 0 Then
                    rdata = rApi.GetItem(21)
                End If
                adata.Region = rdata
                adata.Validate()
                ‘add address to customer
                Dim aID As Long = aApi.Add(adata)
                uAPI.ChangeBillingAddress(userId, aID)
                uAPI.ChangeShippingAddress(userId, aID)
     
                ‘Default payment CC for everyone
                Dim payment As New CreditCardPayment()
                Dim expDate As New CCExpirationDate
                expDate.Month = EkEnumeration.CCExpirationMonth.July
                expDate.Year = 2013
                payment.ExpirationDate = expDate
                payment.Number = "4007000000000"
                payment.CCID = "000"

                Dim basket As Ektron.Cms.Commerce.Basket
                Try
                    basket = bapi.GetDefaultBasket(udata.Id)

                    ‘find class ID based on SKU
                    Dim cCriteria As New Ektron.Cms.Common.Criteria(Of EntryProperty)
                    Dim cClass As Generic.List(Of EntryData)
                    Dim classID As Integer
                    cCriteria.AddFilter(EntryProperty.Sku, Ektron.Cms.Common.CriteriaFilterOperator.EqualTo, dr("event_id").ToString)
                    cClass = catalogApi.GetList(cCriteria)
                    For Each c As EntryData In cClass
                        basket.AddProduct(c.Id)
                        classID = c.Id
                    Next

                    basket.ShippingAddressId = udata.ShippingAddressId
 
                    ‘process order
                    oData = oAPI.PlaceOrder(basket.Id, userId, aID, aID, basket.ShippingMethodId, payment, "", "")

                    ‘update class inventory
                    Dim iAPI As New InventoryApi()
                    Dim iData As InventoryData
                    iData = iAPI.GetInventory(classID)
                    iData.UnitsInStock = iData.UnitsInStock – 1
                    iAPI.SaveInventory(iData)

                    ‘add seat names to class order
                    Dim contentApi As New Ektron.Cms.ContentAPI
                    contentApi.AddContentRating(classID, userId, 0, dr("seat_name") & " – " & dr("seat_email"), True)
                    lblMessage.Text += "<br> great success" & oData.Customer.UserName & " OrderID:" & oData.Id & " eventID:" & dr("event_id").ToString
                Catch ex As Exception
                    lblMessage.Text += "<br>" & oData.Customer.UserName & ex.Message
                End Try
            End While
        Catch ex As Exception
            lblMessage.Text += "<br>" & ex.Message

        End Try

 
0

Text was truncated or one or more characters had no match in the target code page.

Posted by Zu on October 1, 2010 in SQL

I’ve run into this problem many many times already.

here is a workaround?

1. save the excel as either tab delimited or pipe delimited.

(the problem here is that it ads quotes to the import, but thats easily fixable later….)

update table
set fieldName= replace(fieldName, ‘"’, ”)
where fieldName2= 188

2. select Flat file as the source in SSIS
3. change data types and sizes in Advanced
4. Import and done.

 
0

Insert into Smart Form

Posted by Zu on September 23, 2010 in Ektron
Dim capi2 AS New API.Content.Content
        Dim XMLID AS Integer = 278
        Dim folderID AS Integer = 71697
        capi2.AddContent(txtJobTitle.Text, "hello", "<root><JobTitle>" & txtJobTitle.Text & "</JobTitle></root>", "hello", "1033", "", folderID, "", "", "", XMLID, -1, False)

 
0

Display Library Assets (Files)

Posted by Zu on September 17, 2010 in Ektron
Dim libraryApi As New API.Library()
‘API.Library libraryApi = new API.Library();

Dim data() As LibraryData
Dim totalPages As Integer

Try

data = libraryApi.GetAllChildLibItems("quicklinks", [id], "", 0, 0, totalPages)
If ((data IsNot Nothing)) AndAlso (data.Length &gt; 0) Then
‘Dim item As LibraryData = Nothing
Dim str As String = ""
For Each item As LibraryData In data
‘if library item is in same language set on the website
If (item.LanguageId = libraryApi.RequestInformationRef.ContentLanguage) Then
Dim capi As New Ektron.Cms.API.Content.Content
Dim ven_lapi As Ektron.Cms.API.Library = New Ektron.Cms.API.Library()
Dim ldata As Ektron.Cms.LibraryData
‘Here Please give the library Id of that Asset–by ven
ldata = ven_lapi.GetLibraryItem(item.Id)

Dim cdata1 As Ektron.Cms.ContentData = capi.GetContent(ldata.ContentId)

str = item.FileName

lblMessage.Text += str
lblMessage.Text += cdata1.Teaser

End If
Next
Else
lblMessage.Text = "No active files found"
End If
Catch ex As Exception
lblMessage.Text = ex.Message
End Try

 
1

Upload library assets

Posted by Zu on September 17, 2010 in Ektron
‘ Dim myFile2 = FileName.Text.ToLower
Dim myFile As HttpPostedFile = DMSfile.PostedFile

‘add myDMSAssets as folder in site root directory
DMSfile.PostedFile.SaveAs(Server.MapPath("~/path/") &amp; myFile.FileName)

Dim fs As New System.IO.FileStream(Server.MapPath("~/path/" &amp; myFile.FileName), IO.FileMode.Open)

Dim UpdateFile As New Ektron.Cms.AssetUpdateData

UpdateFile.FileName = myFile.FileName
UpdateFile.LanguageId = 1033
‘add a folder in the WorkArea for DMS documents
UpdateFile.FolderId =
UpdateFile.Title = myFile.FileName &amp; " " &amp; Date.Now()
UpdateFile.EndDate = Date.Now.AddMonths(6)
UpdateFile.Teaser = "Name: " &amp; txtName.Text &amp; "
Dim dmsURL As String = ""
Dim contentAPI As New Ektron.Cms.API.Content.Content

Dim dmsID As Integer = contentAPI.AddAsset(fs, UpdateFile)

System.IO.File.Delete(Server.MapPath("~/path/" &amp; myFile.FileName))

Dim myContentBlock As New Ektron.Cms.Controls.ContentBlock
myContentBlock.DefaultContentID = dmsID
myContentBlock.Page = Page
myContentBlock.Fill()

Dim myAsset As New Ektron.Cms.API.Content.Asset
dmsURL = myAsset.GetViewUrl(myContentBlock.EkItem.AssetInfo.Id, 0)

… continue reading.

 
0

Insert Products

Posted by Zu on September 17, 2010 in Ektron
‘Add product Types (sub categories)
Dim catalogApi As New CatalogEntryApi()
Dim productTypeApi As New ProductTypeApi()
Dim inventoryAPI As New InventoryApi()

Dim dr As SqlDataReader
Dim currentUserId As Long = catalogApi.UserId
Dim Currency_USD As Integer = 840

‘ Create and Populate ApplicantDetails
If currentUserId &gt; 0 Then
Dim folderId As Long = 0
Long.TryParse([folder id], folderId)

Dim productTypeId As Long = 0
Long.TryParse([product id], productTypeId)

Dim price As Decimal = 0
Decimal.TryParse(txtprice.text.ToString().Trim(), price)

Dim entryData As EntryData = Nothing
Dim entryType As Ektron.Cms.Common.EkEnumeration.CatalogEntryType = productTypeApi.GetItem(productTypeId).EntryClass

Select Case entryType

Case Ektron.Cms.Common.EkEnumeration.CatalogEntryType.Kit
entryData = New KitData()
entryData.LanguageId = catalogApi.ContentLanguage
Exit Select

Case Ektron.Cms.Common.EkEnumeration.CatalogEntryType.Bundle
entryData = New BundleData()
entryData.LanguageId = catalogApi.ContentLanguage
Exit Select

Case Ektron.Cms.Common.EkEnumeration.CatalogEntryType.SubscriptionProduct
entryData = New SubscriptionProductData()
entryData.LanguageId = catalogApi.ContentLanguage
Exit Select
Case Else

entryData = New ProductData(catalogApi.ContentLanguage)
Exit Select

End Select

entryData.Title =
entryData.TaxClassId =
entryData.Sku =
entryData.GoLive =
entryData.EndDate =
entryData.CurrencyId = Currency_USD

entryData.ProductType = New ProductTypeData()
entryData.ProductType.Id = productTypeId
entryData.Pricing = New PricingData(Currency_USD, price, price)
entryData.FolderId = folderId

entryData.Html = "Other  " &amp; txtclassname.text &amp; _
" " &amp; txtDescription.text &amp; "  "

Try
catalogApi.Add(entryData)
lblMessage.Text = lblMessage.Text &amp; "
Entry Saved.  ID: " &amp; entryData.Id

Dim inventoryData As New InventoryData()
inventoryData.EntryId = entryData.Id
inventoryData.UnitsInStock =

Try
inventoryAPI.SaveInventory(inventoryData)

Dim tax As New Taxonomy()
Dim itemRequest As New TaxonomyContentRequest()
itemRequest.TaxonomyList =
itemRequest.ContentId = entryData.Id

‘Assign productID
Try
tax.AddTaxonomyItem(itemRequest)
Catch ex As Exception
lblMessage.Text = lblMessage.Text &amp; "

An error occured while saving the taxonomy.
" &amp; ex.Message
End Try

Catch ex As Exception
lblMessage.Text = lblMessage.Text &amp; "

An error occured while saving the inventory.
" &amp; ex.Message
End Try

Catch ex As Exception
lblMessage.Text = lblMessage.Text &amp; "

An error occured while saving the entry.
" &amp; ex.Message
End Try
Else
lblMessage.Text = "Not logged in."
End If

 
0

Insert into HTML Form

Posted by Zu on September 17, 2010 in Ektron
Dim cApi As New Ektron.Cms.ContentAPI()
Dim fSubData As New FormSubmittedData()
Dim userapi As Ektron.Cms.UserAPI = New Ektron.Cms.UserAPI()
Dim fDataItem As FormFieldDataItem() = New FormFieldDataItem(1) {}

fDataItem(0) = New FormFieldDataItem()
fDataItem(0).FieldName = "EmployerID"
fDataItem(0).DataValue = txtid.text
fDataItem(1) = New FormFieldDataItem()
fDataItem(1).FieldName = "JobTitle"
fDataItem(1).DataValue = txtJobTitle.Text
fSubData.DataItems = fDataItem
fSubData.DateSubmitted = Now.Date()

Try
cApi.SubmitFormFieldData([form id], fSubData, True)
lblMessage.Text = "Great Success!"
Catch ex As Exception
lblMessage.Text = ex.Message.ToString
End Try

Copyright © 2007-2012 Cheat Sheet All rights reserved.
Desk Mess Mirrored v1.8.1 theme from BuyNowShop.com.