Code sample: Use parameter with SqlDataAdapter

 
 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim myPara As String = Request.QueryString("ThreadID")

If (String.IsNullOrEmpty(Request.QueryString("ThreadID"))) Then

myPara = 123

‘"defaultValue"

End If

Dim strConn As String = System.Configuration.ConfigurationManager.ConnectionStrings("myTestConnectionString08").ToString()

Dim myConnection As SqlConnection = New SqlConnection(strConn)

 

Dim myAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Posts WHERE ThreadID=@ThreadID", myConnection)

‘Use parameter with SqlDataAdapter

myAdapter.SelectCommand.Parameters.AddWithValue(

"@ThreadID", myPara)

Dim ds As DataSet = New DataSet()

myAdapter.Fill(ds)

DataList1.DataSource = ds

DataList1.DataBind()

End Sub


DNN Dynamic Form Submit Button Center Align Issue

Here is the discussion about this issue (THREE YEARS ago!) and options to fix it.
http://www.datasprings.com/Products/ProductForums/tabid/727/forumid/5/tpage/1/view/topic/postid/1459/language/en-US/Default.aspx#1459
 
Edit: The above link stops working now. (5/31/2011)
Here is the the quick fix to modify align=”<%=ButtonAlign()%>”  at  line 503 in  DynamicForms.ascx  file.
The  DynamicForms.ascx  file is located at  /DesktopModules/Dynamic Forms/DynamicForms.ascx
The original:
<DIVid=’SaveForm_<% =ModuleID.TosTring %>class=”DynamicForms_SaveFormDiv”>
<Span align=”<%=ButtonAlign()%>“>
Modified:
<DIVid=’SaveForm_<% =ModuleID.TosTring %>class=”DynamicForms_SaveFormDiv” align=”<%=ButtonAlign()%>“> 
 <Span>
You can remove the empty <span></span> tags if you want.
 
After the modfication of this code, you can use the Dynamic Forms’ setting to change the alignment to center. left or right.
DynamicForm Instance>> Module Configuration>> General Settings>>Align Link Buttons:  Choose From Center, Right or Left.
Don’t forget to hit the link at the bottom to save the setting change.: Update Settings.
 

Split one column into multiple columns in SQL Server 2005

 
You can use a Split function and a CROSS APPLY to split a column into multiple columns.
(The Split function I used here is from:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Here is the script:
 

;with mycte as (select * from t1 t

CROSS

APPLY dbo.Split( ‘,’, t.AutoCombo))

SELECT

ID,AutoCombo,col1,[1] as A1,[2] as A2,[3] as A3,[4] as A4 FROM(

select

ID,AutoCombo,col1,pn, s FROM mycte ) src

PIVOT

( MAX(s) FOR pn in ([1],[2],[3],[4])) pvt

 

 

 
Sample table:

CREATE

TABLE [dbo].[t1](

[id] [int]

IDENTITY(1,1) NOT NULL,

[autoCombo] [varchar]

(250) NULL,

[col1] [varchar]

(50) NULL

)

ON [PRIMARY]

GO

SET

ANSI_PADDING OFF

GO

SET

IDENTITY_INSERT [dbo].[t1] ON

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (1, CONVERT(TEXT, N’1002,1003,9856′), CONVERT(TEXT, N’somevaleue’))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (2, CONVERT(TEXT, N’1001′), CONVERT(TEXT, N’somevaleue1001′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (3, CONVERT(TEXT, N’1005′), CONVERT(TEXT, N’somevaleue1005′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (4, CONVERT(TEXT, N’1006′), CONVERT(TEXT, N’somevaleue1006′))

INSERT

[dbo].[t1] ([id], [autoCombo], [col1]) VALUES (5, CONVERT(TEXT, N’1007,1009′), CONVERT(TEXT, N’somevaleue1007′))

SET

IDENTITY_INSERT [dbo].[t1] OFF


Database from Single_User Mode to Multiple_user mode (issues and solution)

I don’t know why once in a while a user database can be switched to single_user mode. Sometimes it works fine with a Alter Database command: ALTER DATABASE [myDNName] SET MULTI_USER WITH NO_WAIT.
But you may have run into errors when you try to execute this command. You may need to wait for a while or run a KILL command to kill the currenct connection to the database we want to modify the setting.
Here is a link for how to proceed with the KILL:
http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx
 
 

SEO basic link

Here is a link to SEO basics: SEO: A Comprehensive Guide for Beginners
http://blog.kissmetrics.com/seo-guide/
 
You may get a refresh for SEO from this 5 minutes reading.

Keep Tracking Lastupdated Datetime In A Table

First, you need to add a Lastupdated datetime column to your table and set the data type as datetime. After you created the column, you can assign a default value to the column with a GETDATE() function, which will insert a dattime when a new record is created. The last piece is to create a AFTER UPDATE trigger for the table.
Each time, when a record is being updated, the lastupdateddatetime will be updated accordingly.
 
Here is the sample code for the update trigger I created for a sample table:
 

CREATE TRIGGER updatedatetimeChanged

ON newTable

AFTER UPDATE

AS

BEGIN

–newTable is the table name with a primary key field id

SET NOCOUNT ON;

update newTable

set lastupdateddatetime=getdate()

FROM newTable n INNER JOIN deleted d ON n.id=d.id

END

GO