Programming

Make a Textbox that accepts only numbers

3
15 years
by in Visual Basic.Net 
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
Const pstr As String = "0123456789"
If pstr.IndexOf(e.KeyChar) = -1 Then
e.Handled = True
End If
End Sub

If you want to add “.” for exmaple to the combination you can change first line to 

Const pstr As String = "0123456789"

Prevent Adding Items to combobox

0
15 years
by in Visual Basic.Net 
Private Sub ComboBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
 e.KeyChar = ChrW(0)
End Sub

Clearing all textboxes on a Form

0
15 years
by in Visual Basic.Net

Do you have a form that has a lot of textboxes?

do you want to clear all those textboxes for example when there is a new?

this is the old scenario:

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""

etc…………
this is my idea:

Sub ClearForm(ByVal frm_name As Form)
Dim Contrl As Control
For Each Contrl In frm_name.Controls
If (TypeOf Contrl Is TextBox) Then Contrl.Text = ""
Next Contrl
End Sub

this is my friend samir ibrahim‘s idea, compatible with “.net framework 3.5 +”

For Each _Ctrl As Control In Me.Controls.OfType(Of TextBox)()
_Ctrl.Text = ""
Next

The second idea is more optimized with same result.

Of course my idea is cooler because i made it :mrgreen:

you just need to pass the form name to the function & it will clear the form for you, this will save your fingers extensive redundant typing :mrgreen::mrgreen::mrgreen::mrgreen::mrgreen:

Single Field to Multiple Rows

1
15 years
by in Visual Basic.Net

You want your field from all in one field

(No column name)
———————–
Mike,Joe,Jerry,Ben

to become separate rows like below?

NAME
——–
Mike
Joe
Jerry
Ben

then use script below..

declare @toRowXML xml
SET @toRowXML = '<b><x>'+replace('Mike,Joe,Jerry,Ben',',','</x><x>')+'</x></b>'
SELECT @toRowXML
SELECT rtrim(ltrim(toRow.value('.','nvarchar(100)'))) as NAME
 from @toRowXML.nodes('/b/x') as t(toRow)

Random your data using Order by NEWID()

0
15 years
by in MS Sql Server

This is not a new technology but I just would like to share or refresh your memory.

ORDER BY NEWID()

is used to randomly sorting query result.

it’s good if you would like to pick randomly few selected data for example : the lucky winner, the lucky customer, etc

if you want to random all of them then the query would look like this

SELECT Id FROM tableName
ORDER BY NEWID()

but imagine if you would only take one data from 500K rows you have. It would take quite some time. them don’t retrieve all of them

SELECT TOP N Id FROM tableName
ORDER BY NEWID()

How to Insert from other table that already exists

0
15 years
by in MS Sql Server

The basic Insert statement is.

CREATE TABLE Customer (
ID int identity(1,1),
CustomerName varchar(30),
Address varchar(100),
Phone varchar (100)
)
INSERT INTO Customer (CustomerName, Address, Phone)

How about you want to put or just copy some of the data from other table to this Customer Table?

INSERT INTO Customer (CustomerName, Address, Phone)
SELECT CustomerName, Address, Phone From OldCustomer

In above query you’ll insert your customer table with data from OldCustomer table with ID less than 50 (0-49).

How about if you just want to create a replication of a table with data type?
On

SELECT * INTO newTable FROM OldTable

You don’t need to create the table first. Cause on select into statement the create table is already done then the insertion.

SELECT * INTO Customer FROM OldCustomer

How about if you just want to create a replication of some column in a table and columns’ data type?

SELECT CustomerName, Phone INTO Customer FROM OldCustomer

And if there are needs to use join or where clause just use it as you need it. :)

example :

SELECT A.CustomerName, A.Phone, B.City INTO Customer
FROM OldCustomer A JOIN City B On A.CityID= B.CityID
WHERE City LIKE ':%'

INSERT INTO Customer (CustomerName, Address, Phone)
SELECT CustomerName, Address, Phone From OldCustomer A
JOIN City B On A.CityID= B.CityID
Where A.ID > 50
AND City LIKE 'L%'
Where ID > 50
values ('Jane', 'anywhere street', '9097655')

Coalesce another IsNull method

0
15 years
by in Visual Basic.Net 
COALESCE ( expression [ ,...n ] )

Coalesce is Equivalent with

CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 … ELSE expressionN END
END

My little experiment with coalesce

DECLARE @i VARCHAR(20)
DECLARE @exp2
SET @exp2 = NULL

SET @i = null
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [It’s Null]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [ more;]
SELECT COALESCE (@i , @exp2, ‘Stranger’) –result [Stranger]

SET @i = ‘Something’
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [Something more;]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [Something]
SELECT COALESCE (@i , @exp2 , ‘Stranger’) –result [Something]

As you see if I use case and combine it with null function it’ll need more than one line. Coalesce is very useful and helpful in above case.

More about insert statement

0
15 years
by in MS Sql Server

I’ve wandered in some forums I’ve found another way to insert multiple data.

before

INSERT INTO TableName (Col1, Col2)
VALUES (1, 'John');
INSERT INTO TableName (Col1, Col2)
VALUES (2, 'Mike');
INSERT INTO TableName (Col1, Col2)
VALUES (3, 'Jane');

another way I found

INSERT INTO TableName (Col1, Col2)
SELECT 1 , 'John'
UNION ALL
SELECT 2 , 'Mike'
UNION ALL
SELECT 3 , 'Jane'

--only in SQL Server 2008
INSERT INTO TableName (Col1, Col2)
VALUES (1, 'John'),
(2, 'Mike'),
(3, 'Jane')

Find Character occurrence in String

0
15 years
by in MS Sql Server

I made this query to answer a challenge given from a programming forum. After the query is done I realized that I’ve made a mistake. And it was quite a mistake. The challenge ask you to list all the character that occur in sequence. What I made is a query to list the most sequential occurrence character..

I know this query is far from perfect. But just let me share it with you.

this is the data source

Data
------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1
4A73E7EB-7777-4A04-9258-F1E75097977C
5AAF477C-274D-400D-9067-035968F33B19
725DA718-30D0-44A9-B36A-89F27CDFEEDE
8083ED5A-D3B9-4694-BB04-F0B09C588888
22244444-43B9-4694-BB04-F0B098888888

expected result :

Data				        pattern	len	pattern2	len2
----------------------------------------------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	999	3	NULL		NULL
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1	NULL	NULL	NULL		NULL
4A73E7EB-7777-4A04-9258-F1E75097977C	7777	4	NULL		NULL
5AAF477C-274D-400D-9067-035968F33B19	AA	2	NULL		NULL
725DA718-30D0-44A9-B36A-89F27CDFEEDE	44	2	NULL		NULL
8083ED5A-D3B9-4694-BB04-F0B09C588888	88888	5	NULL		NULL
22244444-43B9-4694-BB04-F0B098888888	8888888	7	NULL		NULL

DATA					Char	Pos	Len
------------------------------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	9	1	3
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1	NULL	NULL	NULL
4A73E7EB-7777-4A04-9258-F1E75097977C	7	10	4
5AAF477C-274D-400D-9067-035968F33B19	A	2	2
725DA718-30D0-44A9-B36A-89F27CDFEEDE	4	15	2
8083ED5A-D3B9-4694-BB04-F0B09C588888	8	32	5
22244444-43B9-4694-BB04-F0B098888888	8	30	7

My query

Create table #t (Data VARCHAR(40), pattern varchar(50), [len] int,
pattern2 varchar(50), len2 int )

INSERT #t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT #t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT #t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT #t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT #t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT #t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
INSERT #t (Data) SELECT '22244444-43B9-4694-BB04-F0B098888888'

select * from #t

declare @pattern varchar(20)
set @pattern = null
declare @maxlen int
declare @e int
declare @i varchar(2)
declare @j varchar(2)

set @maxlen = (select MAX(len(data)) from #t)
print @maxlen

declare @q nvarchar(4000)

set @i = 1
set @j = 2

while (@i < 36)
 begin
 print @i

 set @q = '
 update  #t
 set pattern = right(isnull(pattern,''''),isnull(len(pattern),1)-1) +
               SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
 where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
 and RIGHT(ISNULL(pattern, SUBSTRING(Data,'+@i+',1)),1) = SUBSTRING(Data,'+@i+',1)
 and isnull(substring(data, '+@i+'-[len]+1, 1),SUBSTRING(data, '+@i+', 1))
     =  SUBSTRING(data, '+@i+', 1)

 update  #t
 set pattern2 = right(isnull(pattern2,''''),isnull(len(pattern2),1)-1) +
                SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
 where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
 and RIGHT(ISNULL(pattern2, SUBSTRING(Data,'+@i+',1)),1)
                              = SUBSTRING(Data,'+@i+',1)
 and isnull(substring(data, '+@j+'-[len2]+1, 1),
      SUBSTRING(data, '+@i+', 1)) =  SUBSTRING(data, '+@i+', 1)
 and [len] != 0

 update #t
 set [len] = len(pattern),
 len2 = len(pattern2)

 update #t
 set [pattern] = [pattern2],
 [len]= len2,
 pattern2 = NULL
 where len2 > [len]
 and SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)

 update #t
 set [pattern2] = NULL,
 [len2]= NULL
 where SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)
 '

 print @q
 exec SP_executesql @q
 set @i +=1
 set @j +=1

 end

select * from #t
select DATA, LEFT(pattern,1) AS 'Char',  PATINDEX('%'+pattern+'%', DATA) AS Pos, [Len]
 from #t

Basically what I am trying to do is compare the char with the following char. And Keep the pattern and if I found another sequential occurrence the most frequent will be recorded.

Create temporary table in dynamic query

0
15 years
by in MS Sql Server

there are cases when you are forced to create temporary table in dynamic query.

below is sample of creating temporary then select it in dynamic query.

declare @tableName varchar(30)
set @tableName = 'TableB'
declare @query nvarchar(4000)
set @query = 'CREATE TABLE #'+@tableName+'
                    ( id int,
                      data varchar(30)
                    )
              insert into #'+@tableName+' values(1, ''Me'')
              SELECT * from #'+@tableName
 exec sp_executesql @query
« Previous
Next »
Go to Top