Nishant kumar: 2015

Tuesday, 3 November 2015

How to find numeric value in mysql

SELECT * FROM myTable WHERE concat('',col1 * 1) = col1

Monday, 24 August 2015

how to check store procedure exist or not

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME="procedure name" AND ROUTINE_SCHEMA="dbname";

store processior in my sql

DELIMITER //
create procedure duplicat(IN stName longtext,IN stmobile longtext,IN stfather longtext,IN stemail longtext)
begin
Select Auto_No from tblname where Data_As_Duplicate is null and ucase(Student_Name_AlphabetShort)=stName and (studentMobileNumber=stmobile or lcase(studentEmailID)=stemail ) and ucase(Parent_Name_AlphabetShort)=stfather order by Auto_No ;
end//
DELIMITER ;

call duplicat('nishant', '7503485910','sdk','nishant70417@live.com')

Thursday, 13 August 2015

How to Set background color of Combobox item in vb.net

'Set the Combobox's DrawMode property to OwnerDrawVariable

Private Sub test_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ColorName As String
For Each ColorName In System.Enum.GetNames(GetType(System.Drawing.KnownColor))
ComboBox1.Items.Add(Color.FromName(ColorName))
Next
End Sub
'The above code will load the known color names, and then adds Color objects to the combobox's Items collection.
'Next, let's add our MeasureItem sub ( for the combobox ) :
Protected Sub Combobox1_MeasureItem(ByVal sender As Object, ByVal e As System.Windows.Forms.MeasureItemEventArgs) Handles ComboBox1.MeasureItem
Dim myRandom As New Random
e.ItemHeight = myRandom.Next(20, 20)
End Sub
'This, will call the overloaded Random.Next method to get the next random number between 20 and 40, and assign that to the ItemHeight property of the MeasureItemEventArgs parameter.
'Last step, add the DrawItem event :
Protected Sub Combobox1_DrawItem(ByVal sender As Object, ByVal e As System.Windows.Forms.DrawItemEventArgs) Handles ComboBox1.DrawItem
If e.Index < 0 Then
e.DrawBackground()
e.DrawFocusRectangle()
Exit Sub
End If
' Get the Color object from the Items list
Dim CurrentColor As Color = CType(ComboBox1.Items(e.Index), Color)

' get a square using the bounds height
Dim SizeRect As Rectangle = New Rectangle(2, e.Bounds.Top + 2, e.Bounds.Width, e.Bounds.Height - 2)

Dim ComboBrush As Brush

' call these methods first
e.DrawBackground()
e.DrawFocusRectangle()

' change brush color if item is selected
If e.State = Windows.Forms.DrawItemState.Selected Then
ComboBrush = Brushes.White
Else
ComboBrush = Brushes.Black
End If

' draw a rectangle and fill it
e.Graphics.DrawRectangle(New Pen(CurrentColor), SizeRect)
e.Graphics.FillRectangle(New SolidBrush(CurrentColor), SizeRect)

' draw a border
SizeRect.Inflate(1, 1)
e.Graphics.DrawRectangle(Pens.Black, SizeRect)

' draw the Color name
e.Graphics.DrawString(CurrentColor.Name, ComboBox1.Font, ComboBrush, e.Bounds.Height + 5, ((e.Bounds.Height - ComboBox1.Font.Height) \ 2) + e.Bounds.Top)
End Sub

Friday, 7 August 2015

How To Convert database to UTF8 in my sql

alter table TABLE_NAME CONVERT TO character SET utf8 collate UTF8_BIN

Wednesday, 5 August 2015

MySQL Stored Procedure

DELIMITER //
CREATE PROCEDURE aff2_2015
(IN con longtext, IN mob bigint)
BEGIN
SELECT * FROM affidavitdata_2015
WHERE studentFirstName = con and studentmobilenumber=mob;
END //
DELIMITER ;
CALL aff2_2015('nishant','7503485910');

How create setup file in vb.net

1) Click 'File' -> 'Add' -> 'New Project'.

2) Goto 'Other Project Types' and 'Setup and Development'.

3) Choose 'Setup Project'.

4) In the 'Solution Explorer' rightclick on your setup project.

5) Choose 'Properties'.

6) Click 'Prerequisites...' button.

7) Choose your prerequisites (e.g. .NET Framework, Windows Installer).

8) Press 'OK', press 'Apply'.

9) Rightclick on 'Application Folder' in the left File System window.

10) Choose 'Add' -> 'Project Output'

11) When done, rightclick in the Right File System window.

12) Choose 'Add' and whatever you want to add to the application directory

during installation.

13) Rightclick on 'Project output from ....'

14) Select 'Create shorcut to Primary output from ...'

15) Rightclick on 'Shorcut to Primary output from...'

16) Choose 'Rename'

17) Name it after your application.

18) Select it and drag it to the left to 'User's Program Menu'.

19) (Build solution) Build your setup project.

20) In the setup project directory you'll find the .exe and .msi file to

install your application.

String Abribiation in vb.net

Function colldbabrivatename(ByVal sstring As String) As String
sstring = Replace(sstring, " ", "")
sstring = Trim(sstring)
Dim abribiatedata = Split(sstring, Space(1))
If UBound(abribiatedata) > 0 Then
Dim abribiateform As String = ""
For i As Integer = LBound(abribiatedata) To UBound(abribiatedata)
If abribiatedata(i).Substring(0, 1) = "[" Or abribiatedata(i).Substring(0, 1) = "(" Then Exit For
If Len(abribiatedata(i)) > 1 Then
If abribiateform = "" Then
abribiateform = abribiatedata(i).Substring(0, 1)
abribiateform1 = abribiatedata(i).Substring(0, 1)
Else
abribiateform = abribiateform & "" & abribiatedata(i).Substring(0, 1)
abribiateform1 = abribiateform1 & " " & abribiatedata(i).Substring(0, 1)
End If
Else
If abribiateform = "" Then
abribiateform = abribiatedata(i)
abribiateform1 = abribiatedata(i)
Else
abribiateform = abribiateform & "" & abribiatedata(i)
abribiateform1 = abribiateform1 & " " & abribiatedata(i)
End If
End If
Next
sstring = abribiateform
End If

colldbabrivatename = sstring
End Functio

sort string as alphabetical in vb.net

Function sortalphabet(ByVal wrd As String)
sortalphabet = ""
Dim myarr() As String
Dim Length As Integer = Len(wrd)
ReDim myarr(Length)
For i = 1 To Length
Dim charac As String = Mid(wrd, i, 1)
myarr(i - 1) = charac
Next
Dim Sorted As Boolean = False
Do While Not Sorted
Sorted = True
For x = 0 To UBound(myarr) - 1
If myarr(x) > myarr(x + 1) Then
Dim temp As String = myarr(x + 1)
myarr(x + 1) = myarr(x)
myarr(x) = temp
Sorted = False
End If
Next
Loop
sortalphabet = Join(myarr, Space(0))
End Function

Saturday, 1 August 2015

Email Send Class in Vb.net

Imports System.Net.Mail
Public Class Emailclass
Public Function SendEmail(ByVal Recipient As String, _
ByVal CopyRecipients As List(Of String), _
ByVal FromAddress As String, _
ByVal Subject As String, _
ByVal Body As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal ishtml As Boolean, _
ByVal ssl As Boolean, _
Optional ByVal Server As String = "smtp.gmail.com", _
Optional ByVal Port As Integer = 587, _
Optional ByVal Attachments As List(Of String) = Nothing, _
Optional ByVal ImgCnts As List(Of String) = Nothing) As Boolean

Dim Email As New MailMessage()

Try
Dim SMTPServer As New SmtpClient
For Each Attachment As String In Attachments
Email.Attachments.Add(New Attachment(Attachment))
Next

Email.From = New MailAddress(FromAddress)

Email.To.Add(Recipient)

For Each CopyRecipient As String In CopyRecipients
If CopyRecipient <> "" Then
Email.CC.Add(CopyRecipient)
End If
Next
Email.Body = Body
If ishtml = True Then
For Each ImgCnt As String In ImgCnts
If ImgCnt <> "" Then
Dim alternateView As AlternateView = alternateView.CreateAlternateViewFromString(Body, Nothing, "text/html")
Dim imgpath As String = Trim(Strings.Left(ImgCnt, InStr(ImgCnt, "|") - 1))
'Create the LinkedResource here
Dim logo As New LinkedResource(imgpath, "image/jpeg") 'Content Type is set as image/jpeg
logo.ContentId = Trim(Mid(ImgCnt, InStr(ImgCnt, "|") + 1))
logo.TransferEncoding = Net.Mime.TransferEncoding.Base64

alternateView.LinkedResources.Add(logo)
Email.AlternateViews.Add(alternateView)
End If
Next
End If
Email.Subject = Subject
Email.IsBodyHtml = ishtml
SMTPServer.Host = Server
SMTPServer.Port = Port

SMTPServer.Credentials = New System.Net.NetworkCredential(UserName, Password)

SMTPServer.EnableSsl = ssl
SMTPServer.Send(Email)
MessageBox.Show("email sent successfully")
Email.Dispose()

Return True

Catch ex As SmtpException

Email.Dispose()
Return False

Catch ex As ArgumentOutOfRangeException

Email.Dispose()
Return False

Catch Ex As InvalidOperationException

Email.Dispose()
Return False
End Try
End Function

End Class

Validate Email in Vb.net

Public Function ValidateEmail(ByVal strEmail As String) As Boolean
Dim strtmp As String, n As Long, sExt As String
ValidateEmail = True

If strEmail = "" Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("error") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("eror") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("websit") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("webside") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("webste") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("webite") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("wqebsite") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("ebsite") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("wesid") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("wewbsite") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("websitw") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("websive") Then
ValidateEmail = False
Exit Function

ElseIf strEmail.ToLower.Contains("websitw") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("webit") Then
ValidateEmail = False
Exit Function
ElseIf strEmail.ToLower.Contains("websi") Then
ValidateEmail = False
Exit Function

ElseIf InStr(1, strEmail, "@") = 0 Then
ValidateEmail = False
Exit Function
ElseIf InStr(1, strEmail, "@") = 1 Then
ValidateEmail = False
Exit Function
ElseIf InStr(1, strEmail, "@") = Len(strEmail) Then
ValidateEmail = False
Exit Function
ElseIf InStr(InStr(1, strEmail, "@") + 1, strEmail, "@") > 0 Then
ValidateEmail = False
Exit Function
ElseIf InStr(1, strEmail, "@.") > 0 Then
ValidateEmail = False
Exit Function
ElseIf Len(strEmail) < 6 Then
ValidateEmail = False
Exit Function
End If
sExt = ""
If InStr(InStr(1, strEmail, "@"), strEmail, ".") <> 0 Then
sExt = Right(strEmail, Len(strEmail) - InStr(InStr(1, strEmail, "@"), strEmail, "."))
End If
If sExt = "" Then
ValidateEmail = False
Exit Function
End If

strtmp = strEmail
While InStr(1, strtmp, "@") <> 0
n = 1
strtmp = Right(strtmp, Len(strtmp) - InStr(1, strtmp, "@"))
End While
If n > 1 Then
ValidateEmail = False
End If
End Function

Email Validation check from regex code in vb.net

Imports System.Text.RegularExpressions ' add name space
Function EmailAddressCheck(ByVal emailAddress As String) As Boolean
Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]" & _
"*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
Dim emailAddressMatch As Match = Regex.Match(emailAddress, pattern)
If emailAddressMatch.Success Then
EmailAddressCheck = True

Else
EmailAddressCheck = False

End If
End Function

Monday, 27 July 2015

How to insert Only Numeric Value in Datagridview in vb.net

Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
AddHandler CType(e.Control, TextBox).KeyPress, AddressOf TextBox_keyPress
End Sub
Private Sub TextBox_keyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs)
If Char.IsDigit(CChar(CStr(e.KeyChar))) = False Then e.Handled = True
If Not (Char.IsDigit(CChar(CStr(e.KeyChar))) Or e.KeyChar = ".") Then e.Handled = True
If e.KeyChar = Convert.ToChar(Keys.Back) Then e.Handled = False ' Check back space key
End Sub

how to find number from string in vb.net

'add name space in vb.net code
Imports System.Text.RegularExpressions

' Code for find number from string
Dim x As String = "123a123&*^*&^*&^*&^ a sdsdfsdf"
MsgBox(Integer.Parse(Regex.Replace(x, "[^\d]", "")))

How to find text change in data grid view and calculate in data grid view in vb.net

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim table As New DataTable

With table.Columns
.Add("Column1", GetType(Integer))
.Add("Column2", GetType(Integer))
.Add("Sum", GetType(Integer))
End With
Me.DataGridView1.DataSource = table
End Sub
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
AddHandler e.Control.TextChanged, AddressOf CellTextChanged
End Sub

Private Sub CellTextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim txt As DataGridViewTextBoxEditingControl
Dim col1 As Integer
Dim col2 As Integer

Try
If DataGridView1.CurrentCell.ColumnIndex = 0 Then
txt = DirectCast(sender, DataGridViewTextBoxEditingControl)
If txt.Text.Length > 0 Then
col1 = Integer.Parse(txt.Text)
End If
If DataGridView1.CurrentRow.Cells("Column2").Value & "" <> "" Then
col2 = Integer.Parse(DataGridView1.CurrentRow.Cells("Column2").Value)
End If
ElseIf DataGridView1.CurrentCell.ColumnIndex = 1 Then
txt = DirectCast(sender, DataGridViewTextBoxEditingControl)
If DataGridView1.CurrentRow.Cells("Column1").Value & "" <> "" Then
col1 = Integer.Parse(DataGridView1.CurrentRow.Cells("Column1").Value)
End If
If txt.Text.Length > 0 Then
col2 = Integer.Parse(txt.Text)
End If
End If
DataGridView1.CurrentRow.Cells("sum").Value = col1 + col2
Catch ex As Exception
MessageBox.Show("Input string was not in a correct format.")
End Try
End Sub

Saturday, 25 July 2015

Count Sunday between two dates in sql and Count days in a month in sql

declare @d1 datetime, @d2 datetime select @d1 = '9/1/2014',@d2= '9/30/2014';with dates (date )as(select @d1 union all select dateadd(d,1,date) from dates where date < @d2 )select date from dates where datename(dw,date) = 'Sunday'

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2/4/2015'),0)))

Wednesday, 6 May 2015

Database Class in vb.net (db class) sql connecton class in vb.net with Mysql and sql database

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports MySql.Data.MySqlClient
Public Class db

Public EmailExtraction As String = ConfigurationSettings.AppSettings("Eamilconnection")
Public localconnection As String = ConfigurationSettings.AppSettings("connection")
Public localconnection1 As String = ConfigurationSettings.AppSettings("connection1")
Public amanconnection As String = ConfigurationSettings.AppSettings("amanconnection")
Public anticonnection As String = ConfigurationSettings.AppSettings("anticonnection")
Public Function execute(ByVal myqry As String, ByVal executeconnection As String) As Boolean 'insert into database
On Error GoTo err
If executeconnection.Contains("database = ugc") = True Then
Dim appcon As SqlConnection = New SqlConnection(executeconnection)
Dim qrycmd As SqlCommand = New SqlCommand
appcon.Open()
qrycmd.Connection = appcon
qrycmd.CommandText = myqry
qrycmd.CommandTimeout = 0
qrycmd.ExecuteNonQuery()
appcon.Close()
Return True
Else
Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
Dim MySqlCommand As MySqlCommand = New MySqlCommand
MySQLConnection.Open()
MySqlCommand.Connection = MySQLConnection
MySqlCommand.CommandText = myqry
MySqlCommand.CommandTimeout = 0
MySqlCommand.ExecuteNonQuery()
MySQLConnection.Close()
Return True
End If
err:
Resume
End Function
Public Function getdata(ByVal myqry As String, ByVal executeconnection As String) As DataTable

On Error GoTo err
If executeconnection.Contains("database = ugc") = True Then

Dim dt As DataTable = New DataTable
Dim dataset As DataSet = New DataSet
Dim appcon As SqlConnection = New SqlConnection(executeconnection)
appcon.Open()
Dim myadapter As SqlDataAdapter = New SqlDataAdapter
myadapter.SelectCommand = New SqlCommand(myqry, appcon)
myadapter.SelectCommand.CommandTimeout = 0
myadapter.Fill(dataset)
dt = dataset.Tables(0)
appcon.Close()
Return dt
Exit Function

Else

Dim dtdatabase As DataTable = New DataTable()
Dim dataset As DataSet = New DataSet()
Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
MySQLConnection.Open()
Dim MyAdp As MySqlDataAdapter = New MySqlDataAdapter()
MyAdp.SelectCommand = New MySqlCommand(myqry, MySQLConnection)
MyAdp.SelectCommand.CommandTimeout = 0
MyAdp.Fill(dataset)
dtdatabase = dataset.Tables(0)
MySQLConnection.Close()
Return dtdatabase
End If

err:
Resume
End Function
Public Function executeodbc(ByVal myqry As String, ByVal executeconnection As String) As Boolean 'insert into database
Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
Dim qrycmd As OdbcCommand = New OdbcCommand
appcon.Open()
qrycmd.Connection = appcon
qrycmd.CommandText = myqry
qrycmd.CommandTimeout = 0
qrycmd.ExecuteNonQuery()
appcon.Close()
Return True
End Function
Public Function getdataodbc(ByVal myqry As String, ByVal executeconnection As String) As DataTable
Dim dt As DataTable = New DataTable
Dim dataset As DataSet = New DataSet
Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
appcon.Open()
Dim myadapter As OdbcDataAdapter = New OdbcDataAdapter
myadapter.SelectCommand = New OdbcCommand(myqry, appcon)
myadapter.SelectCommand.CommandTimeout = 0
myadapter.Fill(dataset)
dt = dataset.Tables(0)
appcon.Close()
Return dt
End Function
End Class

Tuesday, 21 April 2015

To Remove Duplicate from mysql Table Query

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

Friday, 17 April 2015

Missing build configuration dropdown in Visual Studio 2008

Build configuration dropdown

My project is using Microsoft Visual Studio 2008 for development. For some strange reason the build configuration dropdown does not show up by default in the IDE.

I found the following procedure online and very helpful. It describes how to get this useful dropdown visible:

In Visual Studio 2008, click “Tools” -> “Options”
Under “Projects and Solutions” -> “General”, check “Show advanced build options”, and click “OK”
Right click anywhere in the blank space of the toolbar and click “Customize…”
In “Commands” tab, select “Build” in “Categories”.
Scroll the right listbox to the bottom and drag “Solution Configurations” to the toolbar

Opening A URL in vb.net

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

System.Diagnostics.Process.Start("http://nishant2u.blogspot.in/")
End Sub

Email Validation function in Vb.net

String Sound Matching in Vb.net

Public Class SoundexComparison

Public Shared Function GetSoundexCode(ByVal word As String) As String
word = word.ToUpper()

' Keep the first character of the word.
Dim SoundexCode As String = word.Substring(0, 1)

Dim i As Integer
For i = 1 To word.Length - 1

' Transform a single character.
Dim Character As String = Transform(word.Substring(i, 1))

' Decide whether to append this character code,
' depending on the previous sound.
Select Case word.Substring(i - 1, 1)
Case "H", "W"
' Ignore
Case "A", "E", "I", "O", "U"
' Characters separated by a vowel represent distinct
' sounds, and should be encoded.
SoundexCode &= Character
Case Else
If SoundexCode.Length = 1 Then
' We only have the first character, which is never
' encoded. However, we need to check whether it is
' the same phonetically as the next character.
If Transform(word.Substring(0, 1)) <> Character Then
SoundexCode &= Character
End If
Else
' Only add if it does not represent a duplicated
' sound.
If Transform(word.Substring(i - 1, 1)) <> _
Character Then
SoundexCode &= Character
End If
End If
End Select

Next

' A SoundEx code must be exactly 4 characters long.
' Pad it with zeroes in case the code is too short.
SoundexCode = SoundexCode.PadRight(4, "0"c)

' Truncate the code if it is too long.
Return SoundexCode.Substring(0, 4)
End Function

Public Shared Function Transform(ByVal character As String) As String
' Map the character to a SoundEx code.
Select Case character
Case "B", "F", "P", "V"
Return "1"
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Return "2"
Case "D", "T"
Return "3"
Case "L"
Return "4"
Case "M", "N"
Return "5"
Case "R"
Return "6"
Case Else
' All other characters are ignored.
Return String.Empty
End Select
End Function

End Class

About Me