Imports System.Data
Imports System.Data.SqlClient
Partial Class Login
Inherits System.Web.UI.Page
Pagr Load Event
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
TxtUserCode.Focus()
Session("UserID") = TxtUserCode.Text
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetExpires(DateTime.Now)
End Sub
Check User In DataBase That User Is Valid Or Not
Private Function validateUser() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Result As Integer
Dim Lsql As New StringBuilder
Try
Lsql.Append("SELECT count(*) FROM UserMast WHERE UserID=@UserID AND Password=@Password")
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtPassword.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Result = Sqlcom.ExecuteScalar
If Result > 0 Then
validateUser = True
Else
validateUser = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
validateUser = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Login Button
Protected Sub BtnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnLogin.Click
If validateUser() Then
' Response.Redirect("FrmProjectMast.aspx")
' Response.Redirect("Default.aspx")
Response.Redirect("IssueMast.aspx")
'Response.Redirect("UserProject.aspx")
'Response.Redirect("UserMaster.aspx")
Else
LblStatus.Text = "Login failed, Try again."
TxtUserCode.Focus()
End If
End Sub
Cancel Button
Protected Sub BtnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
LblStatus.Text = ""
TxtPassword.Text = ""
TxtUserCode.Text = ""
TxtUserCode.Focus()
End Sub
End Class
How to insert& update& delete Record And check duplicate record
Imports System.Data
Imports System.Data.SqlClient
Partial Class UserMaster
Inherits System.Web.UI.Page
Private Sub Load_GridView()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("SELECT * FROM UserMast")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
GridCust.DataSource = UserTable
GridCust.DataBind()
Else
UserTable.Rows.Clear()
GridCust.DataSource = UserTable
GridCust.DataBind()
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack = False Then
If Session.Item("UserID") Is Nothing Then
Response.Redirect("Login.aspx")
End If
TxtUserID.Text = Session.Item("UserID")
Load_GridView()
Load_Project()
TxtUserID.Text = "New"
End If
End Sub
Private Function Read_ItemIDs() As String
Dim Res As String = ""
Dim MyItem As ListItem
For Each MyItem In CBLProject.Items
If MyItem.Selected = True Then
If Res.Trim <> "" Then
Res = Res & "," & Convert.ToInt32(MyItem.Value)
Else
Res = Convert.ToInt32(MyItem.Value)
End If
End If
Next
Return Res
End Function
Private Function Can_Save() As Boolean
Try
Can_Save = True
If TxtUserName.Text.ToUpper() = "" Then
LblStatus.Text = "Please enter the current user name"
Show_Message("Please enter the current user name")
Can_Save = False
TxtUserName.Focus()
Exit Function
End If
If TxtUserCode.Text.ToUpper = "" Then
Show_Message("Please enter the user code")
LblStatus.Text = "Please enter the user code"
Can_Save = False
TxtUserCode.Focus()
Exit Function
End If
If TxtLoginPass.Text.ToUpper = "" Then
Show_Message("Please enter the password")
Can_Save = False
TxtLoginPass.Focus()
Exit Function
End If
If DDLType.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select user type" Show_Message("Please select user type") End If If CBLProject.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select the project" Show_Message("Please select the project") End If Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString) Dim Sqlcom As New SqlCommand Dim Lsql As New StringBuilder Dim res As Integer Dim UserID As Decimal Try If TxtUserID.Text.Trim = "New" Then UserID = 0 Else UserID = Decimal.Parse(TxtUserID.Text) End If Lsql.Append("SELECT count(*) FROM UserMast WHERE UPPER(UserName)=UPPER(@UserName) AND (UserID<>@UserID)")
Sqlcom.Parameters.AddWithValue("@UserID", UserID)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
res = Sqlcom.ExecuteScalar()
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
If res > 0 Then
Can_Save = False
LblStatus.Text = "User name already exist."
Can_Save = False
TxtUserName.Focus()
Exit Function
Else
Can_Save = True
End If
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString
Can_Save = False
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString()
End Try
End Function
Private Sub Reload_Id()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("select * from UserMast WHERE UserName LIKE '" & TxtUserName.Text & "'")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
TxtUserID.Text = UserTable.Rows(0).Item("UserID")
TxtUserName.Text = UserTable.Rows(0).Item("UserName")
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Private Function Save_Projec() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As SqlCommand
Dim Res As Integer
Dim MyChoice As String = ""
Dim Lsql As New StringBuilder
Dim PArray As String()
Dim Num As Integer = 0
Try
MyChoice = Read_ItemIDs()
PArray = MyChoice.Split(",")
For Num = 0 To PArray.Length - 1
Lsql.Length = 0
Sqlcom = New SqlCommand
If TxtUserID.Text <> 0 Then
Lsql.Append("INSERT INTO UserProject(UserID,ProjectID)VALUES(@UserID,@ProjectID)")
End If
Save_Projec = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@ProjectID", PArray(Num))
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Projec = True
Else
Save_Projec = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Next
Catch ex As Exception
Save_Projec = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcon.Dispose()
End Try
End Function
Protected Function Save_Record() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Res As Integer
Dim Lsql As New StringBuilder
Dim Choice As Nullable(Of Decimal)
Try
If DDLType.SelectedIndex = 0 Then
Choice = Nothing
Else
If DDLType.SelectedIndex = 1 Then
Choice = 1
Else
Choice = 2
End If
End If
If TxtUserID.Text.Trim = "New" Then
Lsql.Append("INSERT INTO UserMast(UserName,UserCode,Password,UserType)VALUES(@UserName,@UserCode,@Password,@UserType)")
Else
Lsql.Append("UPDATE UserMast SET UserName=@UserName,UserCode=@UserCode,Password=@Password,UserType=@UserType WHERE UserID=@UserID")
End If
Save_Record = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.Parameters.AddWithValue("@UserCode", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtLoginPass.Text)
Sqlcom.Parameters.AddWithValue("@UserType", Choice)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Record = True
Else
Save_Record = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
Save_Record = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Protected Sub BtnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
If Can_Save() Then
If Save_Record() Then
Reload_Id()
If Save_Projec() Then
LblStatus.Text = "Record saved"
Show_Message("Record saved")
Clear_Me()
Load_GridView()
End If
End If
Else
LblStatus.Text = "Unable to save record"
Show_Message("Unable to save record")
Load_GridView()
Clear_Me()
End If
End Sub
Private Sub Clear_Me()
TxtLoginPass.Text = ""
TxtUserCode.Text = ""
TxtUserName.Text = ""
TxtUserID.Text = "New"
DDLType.SelectedIndex = 0
TxtUserName.Focus()
End Sub
Private Sub Show_Message(ByVal Msg As String)
Page.ClientScript.RegisterStartupScript(Me.GetType(), "YExport", "alert(""" & Msg & """);", True)
End Sub
Protected Sub Load_SelectedRecord()
Dim IntRow As Integer
Try
IntRow = GridCust.SelectedIndex
TxtUserID.Text = GridCust.Rows(IntRow).Cells(1).Text.ToString()
TxtUserName.Text = GridCust.Rows(IntRow).Cells(2).Text.ToString()
TxtUserCode.Text = GridCust.Rows(IntRow).Cells(3).Text.ToString()
TxtLoginPass.Text = GridCust.Rows(IntRow).Cells(4).Text.ToString()
LblStatus.Text = ""
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub GridCust_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridCust.SelectedIndexChanged
TxtUserID.Text = GridCust.SelectedRow.Cells(1).Text
Load_SelectedRecord()
End Sub
Private Function Can_Delete(ByVal UserID As Decimal) As Boolean
Can_Delete = True
Dim Num As Integer = 0
Try
If UserID < 0 Then Show_Message("Sorry! You have not right to delete record") LblStatus.Text = "Sorry! You have not right to delete record" Can_Delete = False TxtUserName.Focus() End If Catch ex As Exception Show_Message(ex.Message.ToString) LblStatus.Text = ex.Message.ToString Can_Delete = False TxtUserName.Focus() Exit Function End Try End Function Protected Sub GridCust_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridCust.RowCommand Dim LIndex As Integer Dim UserID As Decimal Dim UserTable As New DataTable Try Select Case e.CommandName.ToUpper() Case "REMOVE" LIndex = Decimal.Parse(e.CommandArgument.ToString()) UserID = GridCust.Rows(LIndex).Cells(1).Text If Can_Delete(UserID) Then If Delete_Record(UserID) Then Clear_Me() Show_Message("Record deleted.") LblStatus.Text = "Record deleted." GridCust.DataSource = UserTable GridCust.DataBind() Load_GridView() Else Show_Message("Unable to delete record.") LblStatus.Text = "Unable to delete record." End If End If End Select Catch ex As Exception LblStatus.Text = ex.Message.ToString() End Try End Sub Private Function Delete_Record(ByVal UserID As Decimal) As Boolean Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString) Dim Sqlcom As New SqlCommand Dim Res As Integer Dim Lsql As New StringBuilder Try Lsql.Append("DELETE FROM UserMast where UserID=@UserID") Delete_Record = True Sqlcom.Parameters.AddWithValue("@UserID", UserID) Sqlcom.CommandType = CommandType.Text Sqlcom.CommandText = Lsql.ToString Sqlcom.Connection = Sqlcon Sqlcon.Open() Res = Sqlcom.ExecuteNonQuery If Res > 0 Then
Delete_Record = True
Else
Delete_Record = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
Delete_Record = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Protected Sub GridCust_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridCust.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
For Each ctl As Control In e.Row.Cells(5).Controls
Dim DeleteLink As LinkButton = CType(ctl, LinkButton)
DeleteLink.Attributes.Add("Onclick", "return confirm('Do you really want to delete this record?')")
DeleteLink.CommandArgument = e.Row.RowIndex
Next
End If
End Sub
Protected Sub GridCust_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridCust.PageIndexChanging
LblStatus.Text = ""
GridCust.PageIndex = e.NewPageIndex
Load_GridView()
End Sub
Private Sub Load_Project()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim ProjTable As New DataTable
Try
Lsql.Append("SELECT ProjID,ProjName FROM PojectMaster ORDER BY ProjCode")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(ProjTable)
If ProjTable.Rows.Count > 0 Then
CBLProject.DataSource = ProjTable
CBLProject.DataTextField = ProjTable.Columns("ProjName").ColumnName
CBLProject.DataValueField = ProjTable.Columns("ProjID").ColumnName
CBLProject.DataBind()
Else
ProjTable.Rows.Clear()
CBLProject.DataSource = ProjTable
CBLProject.DataBind()
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Partial Class Login
Inherits System.Web.UI.Page
Pagr Load Event
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
TxtUserCode.Focus()
Session("UserID") = TxtUserCode.Text
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetExpires(DateTime.Now)
End Sub
Check User In DataBase That User Is Valid Or Not
Private Function validateUser() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Result As Integer
Dim Lsql As New StringBuilder
Try
Lsql.Append("SELECT count(*) FROM UserMast WHERE UserID=@UserID AND Password=@Password")
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtPassword.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Result = Sqlcom.ExecuteScalar
If Result > 0 Then
validateUser = True
Else
validateUser = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
validateUser = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Login Button
Protected Sub BtnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnLogin.Click
If validateUser() Then
' Response.Redirect("FrmProjectMast.aspx")
' Response.Redirect("Default.aspx")
Response.Redirect("IssueMast.aspx")
'Response.Redirect("UserProject.aspx")
'Response.Redirect("UserMaster.aspx")
Else
LblStatus.Text = "Login failed, Try again."
TxtUserCode.Focus()
End If
End Sub
Cancel Button
Protected Sub BtnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
LblStatus.Text = ""
TxtPassword.Text = ""
TxtUserCode.Text = ""
TxtUserCode.Focus()
End Sub
End Class
How to insert& update& delete Record And check duplicate record
Imports System.Data
Imports System.Data.SqlClient
Partial Class UserMaster
Inherits System.Web.UI.Page
Private Sub Load_GridView()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("SELECT * FROM UserMast")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
GridCust.DataSource = UserTable
GridCust.DataBind()
Else
UserTable.Rows.Clear()
GridCust.DataSource = UserTable
GridCust.DataBind()
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack = False Then
If Session.Item("UserID") Is Nothing Then
Response.Redirect("Login.aspx")
End If
TxtUserID.Text = Session.Item("UserID")
Load_GridView()
Load_Project()
TxtUserID.Text = "New"
End If
End Sub
Private Function Read_ItemIDs() As String
Dim Res As String = ""
Dim MyItem As ListItem
For Each MyItem In CBLProject.Items
If MyItem.Selected = True Then
If Res.Trim <> "" Then
Res = Res & "," & Convert.ToInt32(MyItem.Value)
Else
Res = Convert.ToInt32(MyItem.Value)
End If
End If
Next
Return Res
End Function
Private Function Can_Save() As Boolean
Try
Can_Save = True
If TxtUserName.Text.ToUpper() = "" Then
LblStatus.Text = "Please enter the current user name"
Show_Message("Please enter the current user name")
Can_Save = False
TxtUserName.Focus()
Exit Function
End If
If TxtUserCode.Text.ToUpper = "" Then
Show_Message("Please enter the user code")
LblStatus.Text = "Please enter the user code"
Can_Save = False
TxtUserCode.Focus()
Exit Function
End If
If TxtLoginPass.Text.ToUpper = "" Then
Show_Message("Please enter the password")
Can_Save = False
TxtLoginPass.Focus()
Exit Function
End If
If DDLType.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select user type" Show_Message("Please select user type") End If If CBLProject.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select the project" Show_Message("Please select the project") End If Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString) Dim Sqlcom As New SqlCommand Dim Lsql As New StringBuilder Dim res As Integer Dim UserID As Decimal Try If TxtUserID.Text.Trim = "New" Then UserID = 0 Else UserID = Decimal.Parse(TxtUserID.Text) End If Lsql.Append("SELECT count(*) FROM UserMast WHERE UPPER(UserName)=UPPER(@UserName) AND (UserID<>@UserID)")
Sqlcom.Parameters.AddWithValue("@UserID", UserID)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
res = Sqlcom.ExecuteScalar()
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
If res > 0 Then
Can_Save = False
LblStatus.Text = "User name already exist."
Can_Save = False
TxtUserName.Focus()
Exit Function
Else
Can_Save = True
End If
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString
Can_Save = False
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString()
End Try
End Function
Private Sub Reload_Id()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("select * from UserMast WHERE UserName LIKE '" & TxtUserName.Text & "'")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
TxtUserID.Text = UserTable.Rows(0).Item("UserID")
TxtUserName.Text = UserTable.Rows(0).Item("UserName")
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Private Function Save_Projec() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As SqlCommand
Dim Res As Integer
Dim MyChoice As String = ""
Dim Lsql As New StringBuilder
Dim PArray As String()
Dim Num As Integer = 0
Try
MyChoice = Read_ItemIDs()
PArray = MyChoice.Split(",")
For Num = 0 To PArray.Length - 1
Lsql.Length = 0
Sqlcom = New SqlCommand
If TxtUserID.Text <> 0 Then
Lsql.Append("INSERT INTO UserProject(UserID,ProjectID)VALUES(@UserID,@ProjectID)")
End If
Save_Projec = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@ProjectID", PArray(Num))
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Projec = True
Else
Save_Projec = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Next
Catch ex As Exception
Save_Projec = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcon.Dispose()
End Try
End Function
Protected Function Save_Record() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Res As Integer
Dim Lsql As New StringBuilder
Dim Choice As Nullable(Of Decimal)
Try
If DDLType.SelectedIndex = 0 Then
Choice = Nothing
Else
If DDLType.SelectedIndex = 1 Then
Choice = 1
Else
Choice = 2
End If
End If
If TxtUserID.Text.Trim = "New" Then
Lsql.Append("INSERT INTO UserMast(UserName,UserCode,Password,UserType)VALUES(@UserName,@UserCode,@Password,@UserType)")
Else
Lsql.Append("UPDATE UserMast SET UserName=@UserName,UserCode=@UserCode,Password=@Password,UserType=@UserType WHERE UserID=@UserID")
End If
Save_Record = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.Parameters.AddWithValue("@UserCode", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtLoginPass.Text)
Sqlcom.Parameters.AddWithValue("@UserType", Choice)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Record = True
Else
Save_Record = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
Save_Record = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Protected Sub BtnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
If Can_Save() Then
If Save_Record() Then
Reload_Id()
If Save_Projec() Then
LblStatus.Text = "Record saved"
Show_Message("Record saved")
Clear_Me()
Load_GridView()
End If
End If
Else
LblStatus.Text = "Unable to save record"
Show_Message("Unable to save record")
Load_GridView()
Clear_Me()
End If
End Sub
Private Sub Clear_Me()
TxtLoginPass.Text = ""
TxtUserCode.Text = ""
TxtUserName.Text = ""
TxtUserID.Text = "New"
DDLType.SelectedIndex = 0
TxtUserName.Focus()
End Sub
Private Sub Show_Message(ByVal Msg As String)
Page.ClientScript.RegisterStartupScript(Me.GetType(), "YExport", "alert(""" & Msg & """);", True)
End Sub
Protected Sub Load_SelectedRecord()
Dim IntRow As Integer
Try
IntRow = GridCust.SelectedIndex
TxtUserID.Text = GridCust.Rows(IntRow).Cells(1).Text.ToString()
TxtUserName.Text = GridCust.Rows(IntRow).Cells(2).Text.ToString()
TxtUserCode.Text = GridCust.Rows(IntRow).Cells(3).Text.ToString()
TxtLoginPass.Text = GridCust.Rows(IntRow).Cells(4).Text.ToString()
LblStatus.Text = ""
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub GridCust_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridCust.SelectedIndexChanged
TxtUserID.Text = GridCust.SelectedRow.Cells(1).Text
Load_SelectedRecord()
End Sub
Private Function Can_Delete(ByVal UserID As Decimal) As Boolean
Can_Delete = True
Dim Num As Integer = 0
Try
If UserID < 0 Then Show_Message("Sorry! You have not right to delete record") LblStatus.Text = "Sorry! You have not right to delete record" Can_Delete = False TxtUserName.Focus() End If Catch ex As Exception Show_Message(ex.Message.ToString) LblStatus.Text = ex.Message.ToString Can_Delete = False TxtUserName.Focus() Exit Function End Try End Function Protected Sub GridCust_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridCust.RowCommand Dim LIndex As Integer Dim UserID As Decimal Dim UserTable As New DataTable Try Select Case e.CommandName.ToUpper() Case "REMOVE" LIndex = Decimal.Parse(e.CommandArgument.ToString()) UserID = GridCust.Rows(LIndex).Cells(1).Text If Can_Delete(UserID) Then If Delete_Record(UserID) Then Clear_Me() Show_Message("Record deleted.") LblStatus.Text = "Record deleted." GridCust.DataSource = UserTable GridCust.DataBind() Load_GridView() Else Show_Message("Unable to delete record.") LblStatus.Text = "Unable to delete record." End If End If End Select Catch ex As Exception LblStatus.Text = ex.Message.ToString() End Try End Sub Private Function Delete_Record(ByVal UserID As Decimal) As Boolean Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString) Dim Sqlcom As New SqlCommand Dim Res As Integer Dim Lsql As New StringBuilder Try Lsql.Append("DELETE FROM UserMast where UserID=@UserID") Delete_Record = True Sqlcom.Parameters.AddWithValue("@UserID", UserID) Sqlcom.CommandType = CommandType.Text Sqlcom.CommandText = Lsql.ToString Sqlcom.Connection = Sqlcon Sqlcon.Open() Res = Sqlcom.ExecuteNonQuery If Res > 0 Then
Delete_Record = True
Else
Delete_Record = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
Delete_Record = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Protected Sub GridCust_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridCust.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
For Each ctl As Control In e.Row.Cells(5).Controls
Dim DeleteLink As LinkButton = CType(ctl, LinkButton)
DeleteLink.Attributes.Add("Onclick", "return confirm('Do you really want to delete this record?')")
DeleteLink.CommandArgument = e.Row.RowIndex
Next
End If
End Sub
Protected Sub GridCust_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridCust.PageIndexChanging
LblStatus.Text = ""
GridCust.PageIndex = e.NewPageIndex
Load_GridView()
End Sub
Private Sub Load_Project()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim ProjTable As New DataTable
Try
Lsql.Append("SELECT ProjID,ProjName FROM PojectMaster ORDER BY ProjCode")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(ProjTable)
If ProjTable.Rows.Count > 0 Then
CBLProject.DataSource = ProjTable
CBLProject.DataTextField = ProjTable.Columns("ProjName").ColumnName
CBLProject.DataValueField = ProjTable.Columns("ProjID").ColumnName
CBLProject.DataBind()
Else
ProjTable.Rows.Clear()
CBLProject.DataSource = ProjTable
CBLProject.DataBind()
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
End Class
How to insert checkbox in gridview and select grid row
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Inherits System.Web.UI.Page
Private Sub Load_Grid()
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim IssueTable As New DataTable
Try
Lsql.Append("SELECT IssueAttech.* FROM IssueAttech")
Lsql.Append(" WHERE IssueID = " & TxtDocID.Text)
Dim TA As New SqlDataAdapter(Lsql.ToString(), Sqlcon)
TA.Fill(IssueTable)
GridViewIssue.DataSource = IssueTable
GridViewIssue.DataBind()
Catch ex As Exception
GridViewIssue.DataSource = Nothing
GridViewIssue.DataBind()
End Try
End Sub
Protected Sub CmdDownload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdDownload.Click
Create_ExcelFileWithData()
End Sub
Private Function Can_Download() As Boolean
Dim Res As Integer = 0
Dim Chk As CheckBox
Try
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
Res = Res + 1
End If
Next
If Res > 0 Then
Can_Download = True
Else
Can_Download = False
LblStatus.Text = "Please select the item to download."
End If
Catch ex As Exception
Can_Download = False
LblStatus.Text = ex.Message.ToString
End Try
End Function
Protected Sub GridCust_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridViewIssue.PageIndexChanging
LblStatus.Text = ""
GridViewIssue.PageIndex = e.NewPageIndex
Load_Grid()
End Sub
Protected Sub ChkAll_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Ctl As CheckBox
Ctl = GridViewIssue.HeaderRow.FindControl("ChkAll")
Dim chkFlag As Boolean = False
If Ctl.Checked Then
chkFlag = True
End If
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
Chk.Checked = chkFlag
Next
End Sub
Private Function Read_DocIDs() As String
Dim DocIDs As String = ""
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
If DocIDs.Trim <> "" Then
DocIDs = DocIDs & "," & GridViewIssue.DataKeys(dr.RowIndex).Value
Else
DocIDs = GridViewIssue.DataKeys(dr.RowIndex).Value
End If
End If
Next
Return DocIDs
End Function
Private Sub Create_ExcelFileWithData()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim TA As SqlDataAdapter
Dim FPath As String = ""
Dim PathWithFileName As String = ""
Dim ExConnectionString As String = ""
Dim ExcelConnection As OleDbConnection = Nothing
Dim LSQL As New StringBuilder
Dim MyQuery As New StringBuilder
Dim PassTable As New DataTable
Dim DocIDs As String = TxtDocID.Text
Try
DocIDs = Read_DocIDs()
MyQuery.Append("Select IssueAttech.* FROM IssueAttech")
MyQuery.Append(" WHERE IssueAttech.DocID IN (" & DocIDs & ")")
TA = New SqlDataAdapter(MyQuery.ToString, SqlCon)
TA.Fill(PassTable)
'------------------------ Code For Creating New File with a worksheet and columns ....................
'Create Excel File
PathWithFileName = System.IO.Path.GetTempFileName()
FPath = System.IO.Path.ChangeExtension(PathWithFileName, "xls")
'Open Connection of the created file
ExConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FPath & ";Extended Properties=Excel 8.0"
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelConnection.Open()
Dim MSQL As String = ""
'Create Table as WorkSheet in the excel file
If PassTable.Columns.Count > 0 Then
LSQL.Append("CREATE TABLE IssueAtteched(")
For Each PCol As DataColumn In PassTable.Columns
LSQL.Append(PCol.ColumnName & " nvarchar,")
Next
MSQL = LSQL.ToString
MSQL = MSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
LSQL.Append(")")
End If
Dim ExcelCommand As New OleDbCommand(LSQL.ToString, ExcelConnection)
ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
'------------ Write data in new created file from passTable
Dim OleInComm As OleDbCommand
Dim XPram As OleDbParameter
Dim OleAdpt As OleDbDataAdapter
Dim PSQL As New StringBuilder
Dim WDS As New DataSet
Dim Dr As DataRow
Dim Res As Integer = 0
Dim GImage As Byte()
LSQL.Length = 0
MSQL = ""
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelCommand = New OleDbCommand
ExcelCommand.CommandText = "Select * from IssueAtteched"
ExcelCommand.Connection = ExcelConnection
OleInComm = New OleDbCommand
For Each DCol As DataColumn In PassTable.Columns
LSQL.Append(DCol.ColumnName & ",")
PSQL.Append("@" & DCol.ColumnName & ",")
Next
MSQL = LSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
MSQL = PSQL.ToString.Trim(",")
PSQL.Length = 0
PSQL.Append(MSQL)
OleInComm.CommandText = "INSERT INTO IssueAtteched(" & LSQL.ToString & ") VALUES (" & PSQL.ToString & ")"
OleInComm.Connection = ExcelConnection
For Each DCol As DataColumn In PassTable.Columns
XPram = OleInComm.Parameters.Add(DCol.ColumnName, OleDbType.VarChar)
XPram.SourceColumn = DCol.ColumnName
Next
OleAdpt = New OleDbDataAdapter(ExcelCommand)
ExcelConnection.Open()
OleAdpt.Fill(WDS, "IssueAtteched")
If IsNothing(WDS) = False Then
For Each IRow As DataRow In PassTable.Rows
Dr = WDS.Tables(0).NewRow
For Each X As DataColumn In PassTable.Columns
If IRow(X.ColumnName) Is DBNull.Value = False Then
Dr(X.ColumnName) = IRow(X.ColumnName).ToString
Else
Dr(X.ColumnName) = ""
End If
Next
WDS.Tables(0).Rows.Add(Dr)
Next
OleAdpt = New OleDbDataAdapter
OleAdpt.InsertCommand = OleInComm
Res = OleAdpt.Update(WDS, "IssueAtteched")
End If
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
OleInComm.Dispose()
GImage = System.IO.File.ReadAllBytes(FPath)
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment; filename=IssueAtteched.xls")
Response.AddHeader("Content-Length", GImage.Length.ToString)
Response.BinaryWrite(GImage)
Catch ex As Exception
ExcelConnection.Close()
ExcelConnection.Dispose()
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Session.Item("UserID") = Nothing Then
Response.Redirect("TssLogin.aspx")
Else
If IsPostBack = False Then
TxtDocID.Text = Request.QueryString("IssueID")
Load_Grid()
End If
End If
End Sub
End Class
How to Put Commas in Amount
Public Function CommaSeparatedAmt(ByVal Amount As String) As String
Dim strAmt As String = ""
Dim s As String = ""
Dim s1 As String = ""
Dim s2 As String = ""
Dim AmtString As String
Dim DecAmt As String
If Amount.Contains(".") Then
Dim strArr As String() = Split(Amount, ".")
AmtString = strArr(0)
DecAmt = strArr(1)
Else
AmtString = Amount
DecAmt = "00"
End If
Select Case AmtString.Length
Case Is < 4 strAmt = AmtString Case 4 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) strAmt = s + "," + s1 Case 5 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) strAmt = s + "," + s1 Case 6 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 2) s2 = AmtString.ToString.Substring(3, 3) strAmt = s + "," + s1 + "," + s2 Case 7 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) s2 = AmtString.ToString.Substring(4, 3) strAmt = s + "," + s1 + "," + s2 Case 8 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) s2 = AmtString.ToString.Substring(5, 3) strAmt = s + "," + s1 + "," + s2 Case 9 s = AmtString.ToString.Substring(0, 3) s1 = AmtString.ToString.Substring(3, 3) s2 = AmtString.ToString.Substring(6, 3) strAmt = s + "," + s1 + "," + s2 Case Else strAmt = AmtString End Select Return strAmt & "." & DecAmt End Function Convert amount in words Public Class ClsRupees Public Function Return_Rupee_String(ByVal Number As Double) As String Dim IntNumber As Long Dim FloatNumber As Decimal If Int(Number) <> Number Then
IntNumber = Int(Number)
FloatNumber = Format((Number - IntNumber), "0.00") * 100
Return_Rupee_String = Convert_To_String(IntNumber)
Return_Rupee_String = UCase("Rs. " + " " + Return_Rupee_String + " AND " & " Paise " & " " + Convert_To_String(FloatNumber) + " Only/-")
Else
Return_Rupee_String = UCase("Rs. " + " " + Convert_To_String(Number) + " Only / -")
End If
End Function
Public Function Convert_To_String(ByVal Number As Double) As String
Dim Cr, La, Th, Hu, Te As Integer
Dim RString As String
Dim sFraction As Long
Dim Fracpos As Long
RString = ""
Number = Math.Abs(Number)
Fracpos = InStr(1, Trim(Number), ".")
If Fracpos > 0 Then
sFraction = Mid(Trim(Number), Fracpos + 1, Len(Trim(Number) - Fracpos))
End If
If Number >= 10000000 Then
Cr = Number \ 10000000
Number = Number Mod 10000000
RString = RString + Return_String(Int(Cr)) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number \ 100000
Number = Number Mod 100000
RString = RString + Return_String(Int(La)) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number \ 1000
Number = Number Mod 1000
RString = RString + Return_String(Int(Th)) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number \ 100
Number = Number Mod 100
RString = RString + Return_String(Int(Hu)) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Return_String(Int(Te)) End If If Val(sFraction) <> 0 Then
If Val(Left(sFraction, 1)) <> 0 Then
If Val(sFraction) < 10 Then RString = RString + " AND " + Trim(Get_Paise(Val(sFraction * 10))) Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If End If Convert_To_String = RString End Function Public Function Return_String(ByVal PassNum As Decimal) As String Dim TaRupee As New DSRupeesTableAdapters.RupeesTableAdapter Dim RupeeTable As New DSRupees.RupeesDataTable RupeeTable = TaRupee.GetDataByAmount(PassNum) If RupeeTable.Rows.Count > 0 Then
Return_String = RupeeTable(0).AmountString.ToString()
Else
Return_String = Under_Hundred_String(PassNum)
End If
RupeeTable.Dispose()
TaRupee.Dispose()
End Function
Private Function Under_Hundred_String(ByVal num As Integer) As String
Dim FirstString As String = ""
Dim SecondString As String = ""
Dim FirstPart As Integer
Dim SecondPart As Integer
Dim RsTable As New DSRupees.RupeesDataTable
Dim TARupees As New DSRupeesTableAdapters.RupeesTableAdapter
FirstPart = (Int(num / 10) * 10)
SecondPart = (num - FirstPart)
'-----------------
RsTable = TARupees.GetDataByAmount(Decimal.Parse(FirstPart))
If RsTable.Rows.Count > 0 Then
FirstString = RsTable(0).AmountString.ToString()
End If
'-----
RsTable = TARupees.GetDataByAmount(Decimal.Parse(SecondPart))
If RsTable.Rows.Count > 0 Then
SecondString = RsTable(0).AmountString.ToString()
End If
Under_Hundred_String = FirstString + " " + SecondString
End Function
Private Function Get_Paise(ByVal Number As Double) As String
Dim Cr As Double
Dim La As Double
Dim Th As Double
Dim Hu As Double
Dim Te As Double
Dim RString As String = ""
If Number >= 10000000 Then
Cr = Number / 10000000
Number = Number Mod 10000000
RString = RString + Trim(Return_String(Int(Cr))) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number / 100000
Number = Number Mod 100000
RString = RString + Trim(Return_String(Int(La))) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number / 1000
Number = Number Mod 1000
RString = RString + Trim(Return_String(Int(Th))) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number / 100
Number = Number Mod 100
RString = RString + Trim(Return_String(Int(Hu))) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Trim(Return_String(Int(Te))) End If Get_Paise = RString End Function End Class Directly Print Report Public CrReport As Object CrystalReportViewer1.ReportSource = CrReport Dim Myform As New FrmSuppLedgerPreview Dim DSPrint As New DSCLedgerPrint DSPrint.Tables.Clear() DSPrint.Tables.Add(Print_Detail()) Dim XReport As New CrSLDetail XReport.SetDataSource(DSPrint) Myform.CrReport = XReport Myform.Show(Me) How to apply transaction in typed dataset Imports Microsoft.VisualBasic Imports System.Data.SqlClient Namespace DSBatchTableAdapters Partial Public Class BatchMasterTableAdapter Dim _Transaction As SqlTransaction Public Property Transaction() As SqlTransaction Get Return _Transaction End Get Set(ByVal value As SqlTransaction) _Transaction = value _connection = _Transaction.Connection If _adapter Is Nothing Then Me.InitAdapter() End If For Each command As SqlCommand In Me.CommandCollection command.Transaction = _Transaction Next Me.Adapter.InsertCommand.Transaction = _Transaction Me.Adapter.UpdateCommand.Transaction = _Transaction Me.Adapter.DeleteCommand.Transaction = _Transaction End Set End Property Public Sub SetCommandTimeOut(ByVal PassTime As Integer) For Each Command As SqlCommand In Me.CommandCollection Command.CommandTimeout = PassTime Next End Sub End Class Partial Public Class BatchChildTableAdapter Dim _Transaction As SqlTransaction Public Property Transaction() As SqlTransaction Get Return _Transaction End Get Set(ByVal value As SqlTransaction) _Transaction = value _connection = _Transaction.Connection If _adapter Is Nothing Then Me.InitAdapter() End If For Each command As SqlCommand In Me.CommandCollection command.Transaction = _Transaction Next Me.Adapter.InsertCommand.Transaction = _Transaction Me.Adapter.UpdateCommand.Transaction = _Transaction Me.Adapter.DeleteCommand.Transaction = _Transaction End Set End Property Public Sub SetCommandTimeOut(ByVal PassTime As Integer) For Each Command As SqlCommand In Me.CommandCollection Command.CommandTimeout = PassTime Next End Sub End Class End Namespace How to dictinct record from the data table by data view Dim CustTable As New DataTable Dim DTable As New DataTable CustTable = Return_CustomerTable() Dim DView As New DataView(CustTable) DView.Sort = "Name" DTable = DTV.ToTable() How to track the checkbox status of the check list box Dim Count As Decimal = 0 If ChkCC.CheckedItems.Count > 0 Then
For Count = 0 To ChkCC.Items.Count - 1
If ChkCC.GetItemChecked(Count) = True Then
Count = Count + 1
End If
Next
End If
MsgBox(Count & " items checked.")
How to dictinct record from the data table by data view
Dim Col(0) As String
Col(0) = "CustName"
Dim CustTable As New DataTable
Dim DTable As New DataTable
CustTable = Return_CustomerTable()
Dim DTV As New DataView(CustTable)
DTable = DTV.ToTable(True, Col)
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Inherits System.Web.UI.Page
Private Sub Load_Grid()
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim IssueTable As New DataTable
Try
Lsql.Append("SELECT IssueAttech.* FROM IssueAttech")
Lsql.Append(" WHERE IssueID = " & TxtDocID.Text)
Dim TA As New SqlDataAdapter(Lsql.ToString(), Sqlcon)
TA.Fill(IssueTable)
GridViewIssue.DataSource = IssueTable
GridViewIssue.DataBind()
Catch ex As Exception
GridViewIssue.DataSource = Nothing
GridViewIssue.DataBind()
End Try
End Sub
Protected Sub CmdDownload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdDownload.Click
Create_ExcelFileWithData()
End Sub
Private Function Can_Download() As Boolean
Dim Res As Integer = 0
Dim Chk As CheckBox
Try
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
Res = Res + 1
End If
Next
If Res > 0 Then
Can_Download = True
Else
Can_Download = False
LblStatus.Text = "Please select the item to download."
End If
Catch ex As Exception
Can_Download = False
LblStatus.Text = ex.Message.ToString
End Try
End Function
Protected Sub GridCust_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridViewIssue.PageIndexChanging
LblStatus.Text = ""
GridViewIssue.PageIndex = e.NewPageIndex
Load_Grid()
End Sub
Protected Sub ChkAll_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Ctl As CheckBox
Ctl = GridViewIssue.HeaderRow.FindControl("ChkAll")
Dim chkFlag As Boolean = False
If Ctl.Checked Then
chkFlag = True
End If
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
Chk.Checked = chkFlag
Next
End Sub
Private Function Read_DocIDs() As String
Dim DocIDs As String = ""
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
If DocIDs.Trim <> "" Then
DocIDs = DocIDs & "," & GridViewIssue.DataKeys(dr.RowIndex).Value
Else
DocIDs = GridViewIssue.DataKeys(dr.RowIndex).Value
End If
End If
Next
Return DocIDs
End Function
Private Sub Create_ExcelFileWithData()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim TA As SqlDataAdapter
Dim FPath As String = ""
Dim PathWithFileName As String = ""
Dim ExConnectionString As String = ""
Dim ExcelConnection As OleDbConnection = Nothing
Dim LSQL As New StringBuilder
Dim MyQuery As New StringBuilder
Dim PassTable As New DataTable
Dim DocIDs As String = TxtDocID.Text
Try
DocIDs = Read_DocIDs()
MyQuery.Append("Select IssueAttech.* FROM IssueAttech")
MyQuery.Append(" WHERE IssueAttech.DocID IN (" & DocIDs & ")")
TA = New SqlDataAdapter(MyQuery.ToString, SqlCon)
TA.Fill(PassTable)
'------------------------ Code For Creating New File with a worksheet and columns ....................
'Create Excel File
PathWithFileName = System.IO.Path.GetTempFileName()
FPath = System.IO.Path.ChangeExtension(PathWithFileName, "xls")
'Open Connection of the created file
ExConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FPath & ";Extended Properties=Excel 8.0"
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelConnection.Open()
Dim MSQL As String = ""
'Create Table as WorkSheet in the excel file
If PassTable.Columns.Count > 0 Then
LSQL.Append("CREATE TABLE IssueAtteched(")
For Each PCol As DataColumn In PassTable.Columns
LSQL.Append(PCol.ColumnName & " nvarchar,")
Next
MSQL = LSQL.ToString
MSQL = MSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
LSQL.Append(")")
End If
Dim ExcelCommand As New OleDbCommand(LSQL.ToString, ExcelConnection)
ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
'------------ Write data in new created file from passTable
Dim OleInComm As OleDbCommand
Dim XPram As OleDbParameter
Dim OleAdpt As OleDbDataAdapter
Dim PSQL As New StringBuilder
Dim WDS As New DataSet
Dim Dr As DataRow
Dim Res As Integer = 0
Dim GImage As Byte()
LSQL.Length = 0
MSQL = ""
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelCommand = New OleDbCommand
ExcelCommand.CommandText = "Select * from IssueAtteched"
ExcelCommand.Connection = ExcelConnection
OleInComm = New OleDbCommand
For Each DCol As DataColumn In PassTable.Columns
LSQL.Append(DCol.ColumnName & ",")
PSQL.Append("@" & DCol.ColumnName & ",")
Next
MSQL = LSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
MSQL = PSQL.ToString.Trim(",")
PSQL.Length = 0
PSQL.Append(MSQL)
OleInComm.CommandText = "INSERT INTO IssueAtteched(" & LSQL.ToString & ") VALUES (" & PSQL.ToString & ")"
OleInComm.Connection = ExcelConnection
For Each DCol As DataColumn In PassTable.Columns
XPram = OleInComm.Parameters.Add(DCol.ColumnName, OleDbType.VarChar)
XPram.SourceColumn = DCol.ColumnName
Next
OleAdpt = New OleDbDataAdapter(ExcelCommand)
ExcelConnection.Open()
OleAdpt.Fill(WDS, "IssueAtteched")
If IsNothing(WDS) = False Then
For Each IRow As DataRow In PassTable.Rows
Dr = WDS.Tables(0).NewRow
For Each X As DataColumn In PassTable.Columns
If IRow(X.ColumnName) Is DBNull.Value = False Then
Dr(X.ColumnName) = IRow(X.ColumnName).ToString
Else
Dr(X.ColumnName) = ""
End If
Next
WDS.Tables(0).Rows.Add(Dr)
Next
OleAdpt = New OleDbDataAdapter
OleAdpt.InsertCommand = OleInComm
Res = OleAdpt.Update(WDS, "IssueAtteched")
End If
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
OleInComm.Dispose()
GImage = System.IO.File.ReadAllBytes(FPath)
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment; filename=IssueAtteched.xls")
Response.AddHeader("Content-Length", GImage.Length.ToString)
Response.BinaryWrite(GImage)
Catch ex As Exception
ExcelConnection.Close()
ExcelConnection.Dispose()
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Session.Item("UserID") = Nothing Then
Response.Redirect("TssLogin.aspx")
Else
If IsPostBack = False Then
TxtDocID.Text = Request.QueryString("IssueID")
Load_Grid()
End If
End If
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Partial Class Login
Inherits System.Web.UI.Page
Pagr Load Event
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
TxtUserCode.Focus()
Session("UserID") = TxtUserCode.Text
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetExpires(DateTime.Now)
End Sub
Check User In DataBase That User Is Valid Or Not
Private Function validateUser() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Result As Integer
Dim Lsql As New StringBuilder
Try
Lsql.Append("SELECT count(*) FROM UserMast WHERE UserID=@UserID AND Password=@Password")
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtPassword.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Result = Sqlcom.ExecuteScalar
If Result > 0 Then
validateUser = True
Else
validateUser = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
validateUser = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Login Button
Protected Sub BtnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnLogin.Click
If validateUser() Then
' Response.Redirect("FrmProjectMast.aspx")
' Response.Redirect("Default.aspx")
Response.Redirect("IssueMast.aspx")
'Response.Redirect("UserProject.aspx")
'Response.Redirect("UserMaster.aspx")
Else
LblStatus.Text = "Login failed, Try again."
TxtUserCode.Focus()
End If
End Sub
Cancel Button
Protected Sub BtnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
LblStatus.Text = ""
TxtPassword.Text = ""
TxtUserCode.Text = ""
TxtUserCode.Focus()
End Sub
End Class
How to insert& update& delete Record And check duplicate record
Imports System.Data
Imports System.Data.SqlClient
Partial Class UserMaster
Inherits System.Web.UI.Page
Private Sub Load_GridView()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("SELECT * FROM UserMast")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
GridCust.DataSource = UserTable
GridCust.DataBind()
Else
UserTable.Rows.Clear()
GridCust.DataSource = UserTable
GridCust.DataBind()
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack = False Then
If Session.Item("UserID") Is Nothing Then
Response.Redirect("Login.aspx")
End If
TxtUserID.Text = Session.Item("UserID")
Load_GridView()
Load_Project()
TxtUserID.Text = "New"
End If
End Sub
Private Function Read_ItemIDs() As String
Dim Res As String = ""
Dim MyItem As ListItem
For Each MyItem In CBLProject.Items
If MyItem.Selected = True Then
If Res.Trim <> "" Then
Res = Res & "," & Convert.ToInt32(MyItem.Value)
Else
Res = Convert.ToInt32(MyItem.Value)
End If
End If
Next
Return Res
End Function
Private Function Can_Save() As Boolean
Try
Can_Save = True
If TxtUserName.Text.ToUpper() = "" Then
LblStatus.Text = "Please enter the current user name"
Show_Message("Please enter the current user name")
Can_Save = False
TxtUserName.Focus()
Exit Function
End If
If TxtUserCode.Text.ToUpper = "" Then
Show_Message("Please enter the user code")
LblStatus.Text = "Please enter the user code"
Can_Save = False
TxtUserCode.Focus()
Exit Function
End If
If TxtLoginPass.Text.ToUpper = "" Then
Show_Message("Please enter the password")
Can_Save = False
TxtLoginPass.Focus()
Exit Function
End If
If DDLType.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select user type" Show_Message("Please select user type") End If If CBLProject.SelectedIndex < 0 Then Can_Save = False LblStatus.Text = "Please select the project" Show_Message("Please select the project") End If Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString) Dim Sqlcom As New SqlCommand Dim Lsql As New StringBuilder Dim res As Integer Dim UserID As Decimal Try If TxtUserID.Text.Trim = "New" Then UserID = 0 Else UserID = Decimal.Parse(TxtUserID.Text) End If Lsql.Append("SELECT count(*) FROM UserMast WHERE UPPER(UserName)=UPPER(@UserName) AND (UserID<>@UserID)")
Sqlcom.Parameters.AddWithValue("@UserID", UserID)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
res = Sqlcom.ExecuteScalar()
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
If res > 0 Then
Can_Save = False
LblStatus.Text = "User name already exist."
Can_Save = False
TxtUserName.Focus()
Exit Function
Else
Can_Save = True
End If
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString
Can_Save = False
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
Catch ex As Exception
Can_Save = False
LblStatus.Text = ex.Message.ToString()
End Try
End Function
Private Sub Reload_Id()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim UserTable As New DataTable
Try
Lsql.Append("select * from UserMast WHERE UserName LIKE '" & TxtUserName.Text & "'")
Dim TA As New SqlDataAdapter(Lsql.ToString(), SqlCon)
TA.Fill(UserTable)
If UserTable.Rows.Count > 0 Then
TxtUserID.Text = UserTable.Rows(0).Item("UserID")
TxtUserName.Text = UserTable.Rows(0).Item("UserName")
End If
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Private Function Save_Projec() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As SqlCommand
Dim Res As Integer
Dim MyChoice As String = ""
Dim Lsql As New StringBuilder
Dim PArray As String()
Dim Num As Integer = 0
Try
MyChoice = Read_ItemIDs()
PArray = MyChoice.Split(",")
For Num = 0 To PArray.Length - 1
Lsql.Length = 0
Sqlcom = New SqlCommand
If TxtUserID.Text <> 0 Then
Lsql.Append("INSERT INTO UserProject(UserID,ProjectID)VALUES(@UserID,@ProjectID)")
End If
Save_Projec = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@ProjectID", PArray(Num))
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Projec = True
Else
Save_Projec = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Next
Catch ex As Exception
Save_Projec = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcon.Dispose()
End Try
End Function
Protected Function Save_Record() As Boolean
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Sqlcom As New SqlCommand
Dim Res As Integer
Dim Lsql As New StringBuilder
Dim Choice As Nullable(Of Decimal)
Try
If DDLType.SelectedIndex = 0 Then
Choice = Nothing
Else
If DDLType.SelectedIndex = 1 Then
Choice = 1
Else
Choice = 2
End If
End If
If TxtUserID.Text.Trim = "New" Then
Lsql.Append("INSERT INTO UserMast(UserName,UserCode,Password,UserType)VALUES(@UserName,@UserCode,@Password,@UserType)")
Else
Lsql.Append("UPDATE UserMast SET UserName=@UserName,UserCode=@UserCode,Password=@Password,UserType=@UserType WHERE UserID=@UserID")
End If
Save_Record = True
Sqlcom.Parameters.AddWithValue("@UserID", TxtUserID.Text)
Sqlcom.Parameters.AddWithValue("@UserName", TxtUserName.Text)
Sqlcom.Parameters.AddWithValue("@UserCode", TxtUserCode.Text)
Sqlcom.Parameters.AddWithValue("@Password", TxtLoginPass.Text)
Sqlcom.Parameters.AddWithValue("@UserType", Choice)
Sqlcom.CommandType = CommandType.Text
Sqlcom.CommandText = Lsql.ToString
Sqlcom.Connection = Sqlcon
Sqlcon.Open()
Res = Sqlcom.ExecuteNonQuery
If Res > 0 Then
Save_Record = True
Else
Save_Record = False
End If
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
Catch ex As Exception
Save_Record = False
LblStatus.Text = ex.Message.ToString
Sqlcon.Close()
Sqlcom.Dispose()
Sqlcon.Dispose()
End Try
End Function
Protected Sub BtnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
If Can_Save() Then
If Save_Record() Then
Reload_Id()
If Save_Projec() Then
LblStatus.Text = "Record saved"
Show_Message("Record saved")
Clear_Me()
Load_GridView()
End If
End If
Else
LblStatus.Text = "Unable to save record"
Show_Message("Unable to save record")
Load_GridView()
Clear_Me()
End If
End Sub
Private Sub Clear_Me()
TxtLoginPass.Text = ""
TxtUserCode.Text = ""
TxtUserName.Text = ""
TxtUserID.Text = "New"
DDLType.SelectedIndex = 0
TxtUserName.Focus()
End Sub
Private Sub Show_Message(ByVal Msg As String)
Page.ClientScript.RegisterStartupScript(Me.GetType(), "YExport", "alert(""" & Msg & """);", True)
End Sub
Protected Sub Load_SelectedRecord()
Dim IntRow As Integer
Try
IntRow = GridCust.SelectedIndex
TxtUserID.Text = GridCust.Rows(IntRow).Cells(1).Text.ToString()
TxtUserName.Text = GridCust.Rows(IntRow).Cells(2).Text.ToString()
TxtUserCode.Text = GridCust.Rows(IntRow).Cells(3).Text.ToString()
TxtLoginPass.Text = GridCust.Rows(IntRow).Cells(4).Text.ToString()
LblStatus.Text = ""
Catch ex As Exception
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub GridCust_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridCust.SelectedIndexChanged
TxtUserID.Text = GridCust.SelectedRow.Cells(1).Text
Load_SelectedRecord()
End Sub
Private Function Can_Delete(ByVal UserID As Decimal) As Boolean
Can_Delete = True
Dim Num As Integer = 0
Try
If UserID < 0 Then Show_Message("Sorry! You have not right to delete record") LblStatus.Text = "Sorry! You have not right to delete record" Can_Delete = False TxtUserName.Focus() End If Catch ex As Exception Show_Message(ex.Message.ToString) LblStatus.Text = ex.Message.ToString Can_Delete = False TxtUserName.Focus() Exit Function End Try End Function How to insert checkbox in gridview and select grid row
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Inherits System.Web.UI.Page
Private Sub Load_Grid()
Dim Sqlcon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim Lsql As New StringBuilder
Dim IssueTable As New DataTable
Try
Lsql.Append("SELECT IssueAttech.* FROM IssueAttech")
Lsql.Append(" WHERE IssueID = " & TxtDocID.Text)
Dim TA As New SqlDataAdapter(Lsql.ToString(), Sqlcon)
TA.Fill(IssueTable)
GridViewIssue.DataSource = IssueTable
GridViewIssue.DataBind()
Catch ex As Exception
GridViewIssue.DataSource = Nothing
GridViewIssue.DataBind()
End Try
End Sub
Protected Sub CmdDownload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdDownload.Click
Create_ExcelFileWithData()
End Sub
Private Function Can_Download() As Boolean
Dim Res As Integer = 0
Dim Chk As CheckBox
Try
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
Res = Res + 1
End If
Next
If Res > 0 Then
Can_Download = True
Else
Can_Download = False
LblStatus.Text = "Please select the item to download."
End If
Catch ex As Exception
Can_Download = False
LblStatus.Text = ex.Message.ToString
End Try
End Function
Protected Sub GridCust_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridViewIssue.PageIndexChanging
LblStatus.Text = ""
GridViewIssue.PageIndex = e.NewPageIndex
Load_Grid()
End Sub
Protected Sub ChkAll_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Ctl As CheckBox
Ctl = GridViewIssue.HeaderRow.FindControl("ChkAll")
Dim chkFlag As Boolean = False
If Ctl.Checked Then
chkFlag = True
End If
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
Chk.Checked = chkFlag
Next
End Sub
Private Function Read_DocIDs() As String
Dim DocIDs As String = ""
Dim Chk As CheckBox
For Each dr As GridViewRow In GridViewIssue.Rows
Chk = dr.Cells(0).FindControl("ChkSelect")
If Chk.Checked Then
If DocIDs.Trim <> "" Then
DocIDs = DocIDs & "," & GridViewIssue.DataKeys(dr.RowIndex).Value
Else
DocIDs = GridViewIssue.DataKeys(dr.RowIndex).Value
End If
End If
Next
Return DocIDs
End Function
Private Sub Create_ExcelFileWithData()
Dim SqlCon As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirConnectionString").ToString)
Dim TA As SqlDataAdapter
Dim FPath As String = ""
Dim PathWithFileName As String = ""
Dim ExConnectionString As String = ""
Dim ExcelConnection As OleDbConnection = Nothing
Dim LSQL As New StringBuilder
Dim MyQuery As New StringBuilder
Dim PassTable As New DataTable
Dim DocIDs As String = TxtDocID.Text
Try
DocIDs = Read_DocIDs()
MyQuery.Append("Select IssueAttech.* FROM IssueAttech")
MyQuery.Append(" WHERE IssueAttech.DocID IN (" & DocIDs & ")")
TA = New SqlDataAdapter(MyQuery.ToString, SqlCon)
TA.Fill(PassTable)
'------------------------ Code For Creating New File with a worksheet and columns ....................
'Create Excel File
PathWithFileName = System.IO.Path.GetTempFileName()
FPath = System.IO.Path.ChangeExtension(PathWithFileName, "xls")
'Open Connection of the created file
ExConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FPath & ";Extended Properties=Excel 8.0"
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelConnection.Open()
Dim MSQL As String = ""
'Create Table as WorkSheet in the excel file
If PassTable.Columns.Count > 0 Then
LSQL.Append("CREATE TABLE IssueAtteched(")
For Each PCol As DataColumn In PassTable.Columns
LSQL.Append(PCol.ColumnName & " nvarchar,")
Next
MSQL = LSQL.ToString
MSQL = MSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
LSQL.Append(")")
End If
Dim ExcelCommand As New OleDbCommand(LSQL.ToString, ExcelConnection)
ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
'------------ Write data in new created file from passTable
Dim OleInComm As OleDbCommand
Dim XPram As OleDbParameter
Dim OleAdpt As OleDbDataAdapter
Dim PSQL As New StringBuilder
Dim WDS As New DataSet
Dim Dr As DataRow
Dim Res As Integer = 0
Dim GImage As Byte()
LSQL.Length = 0
MSQL = ""
ExcelConnection = New OleDbConnection(ExConnectionString)
ExcelCommand = New OleDbCommand
ExcelCommand.CommandText = "Select * from IssueAtteched"
ExcelCommand.Connection = ExcelConnection
OleInComm = New OleDbCommand
For Each DCol As DataColumn In PassTable.Columns
LSQL.Append(DCol.ColumnName & ",")
PSQL.Append("@" & DCol.ColumnName & ",")
Next
MSQL = LSQL.ToString.Trim(",")
LSQL.Length = 0
LSQL.Append(MSQL)
MSQL = PSQL.ToString.Trim(",")
PSQL.Length = 0
PSQL.Append(MSQL)
OleInComm.CommandText = "INSERT INTO IssueAtteched(" & LSQL.ToString & ") VALUES (" & PSQL.ToString & ")"
OleInComm.Connection = ExcelConnection
For Each DCol As DataColumn In PassTable.Columns
XPram = OleInComm.Parameters.Add(DCol.ColumnName, OleDbType.VarChar)
XPram.SourceColumn = DCol.ColumnName
Next
OleAdpt = New OleDbDataAdapter(ExcelCommand)
ExcelConnection.Open()
OleAdpt.Fill(WDS, "IssueAtteched")
If IsNothing(WDS) = False Then
For Each IRow As DataRow In PassTable.Rows
Dr = WDS.Tables(0).NewRow
For Each X As DataColumn In PassTable.Columns
If IRow(X.ColumnName) Is DBNull.Value = False Then
Dr(X.ColumnName) = IRow(X.ColumnName).ToString
Else
Dr(X.ColumnName) = ""
End If
Next
WDS.Tables(0).Rows.Add(Dr)
Next
OleAdpt = New OleDbDataAdapter
OleAdpt.InsertCommand = OleInComm
Res = OleAdpt.Update(WDS, "IssueAtteched")
End If
ExcelConnection.Close()
ExcelConnection.Dispose()
ExcelCommand.Dispose()
OleInComm.Dispose()
GImage = System.IO.File.ReadAllBytes(FPath)
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment; filename=IssueAtteched.xls")
Response.AddHeader("Content-Length", GImage.Length.ToString)
Response.BinaryWrite(GImage)
Catch ex As Exception
ExcelConnection.Close()
ExcelConnection.Dispose()
LblStatus.Text = ex.Message.ToString()
End Try
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Session.Item("UserID") = Nothing Then
Response.Redirect("TssLogin.aspx")
Else
If IsPostBack = False Then
TxtDocID.Text = Request.QueryString("IssueID")
Load_Grid()
End If
End If
End Sub
End Class
How to Put Commas in Amount
Public Function CommaSeparatedAmt(ByVal Amount As String) As String
Dim strAmt As String = ""
Dim s As String = ""
Dim s1 As String = ""
Dim s2 As String = ""
Dim AmtString As String
Dim DecAmt As String
If Amount.Contains(".") Then
Dim strArr As String() = Split(Amount, ".")
AmtString = strArr(0)
DecAmt = strArr(1)
Else
AmtString = Amount
DecAmt = "00"
End If
Select Case AmtString.Length
Case Is < 4 strAmt = AmtString Case 4 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) strAmt = s + "," + s1 Case 5 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) strAmt = s + "," + s1 Case 6 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 2) s2 = AmtString.ToString.Substring(3, 3) strAmt = s + "," + s1 + "," + s2 Case 7 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) s2 = AmtString.ToString.Substring(4, 3) strAmt = s + "," + s1 + "," + s2 Case 8 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) s2 = AmtString.ToString.Substring(5, 3) strAmt = s + "," + s1 + "," + s2 Case 9 s = AmtString.ToString.Substring(0, 3) s1 = AmtString.ToString.Substring(3, 3) s2 = AmtString.ToString.Substring(6, 3) strAmt = s + "," + s1 + "," + s2 Case Else strAmt = AmtString End Select Return strAmt & "." & DecAmt End Function Convert amount in words Public Class ClsRupees Public Function Return_Rupee_String(ByVal Number As Double) As String Dim IntNumber As Long Dim FloatNumber As Decimal If Int(Number) <> Number Then
IntNumber = Int(Number)
FloatNumber = Format((Number - IntNumber), "0.00") * 100
Return_Rupee_String = Convert_To_String(IntNumber)
Return_Rupee_String = UCase("Rs. " + " " + Return_Rupee_String + " AND " & " Paise " & " " + Convert_To_String(FloatNumber) + " Only/-")
Else
Return_Rupee_String = UCase("Rs. " + " " + Convert_To_String(Number) + " Only / -")
End If
End Function
Public Function Convert_To_String(ByVal Number As Double) As String
Dim Cr, La, Th, Hu, Te As Integer
Dim RString As String
Dim sFraction As Long
Dim Fracpos As Long
RString = ""
Number = Math.Abs(Number)
Fracpos = InStr(1, Trim(Number), ".")
If Fracpos > 0 Then
sFraction = Mid(Trim(Number), Fracpos + 1, Len(Trim(Number) - Fracpos))
End If
If Number >= 10000000 Then
Cr = Number \ 10000000
Number = Number Mod 10000000
RString = RString + Return_String(Int(Cr)) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number \ 100000
Number = Number Mod 100000
RString = RString + Return_String(Int(La)) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number \ 1000
Number = Number Mod 1000
RString = RString + Return_String(Int(Th)) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number \ 100
Number = Number Mod 100
RString = RString + Return_String(Int(Hu)) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Return_String(Int(Te)) End If If Val(sFraction) <> 0 Then
If Val(Left(sFraction, 1)) <> 0 Then
If Val(sFraction) < 10 Then RString = RString + " AND " + Trim(Get_Paise(Val(sFraction * 10))) Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If End If Convert_To_String = RString End Function Public Function Return_String(ByVal PassNum As Decimal) As String Dim TaRupee As New DSRupeesTableAdapters.RupeesTableAdapter Dim RupeeTable As New DSRupees.RupeesDataTable RupeeTable = TaRupee.GetDataByAmount(PassNum) If RupeeTable.Rows.Count > 0 Then
Return_String = RupeeTable(0).AmountString.ToString()
Else
Return_String = Under_Hundred_String(PassNum)
End If
RupeeTable.Dispose()
TaRupee.Dispose()
End Function
Private Function Under_Hundred_String(ByVal num As Integer) As String
Dim FirstString As String = ""
Dim SecondString As String = ""
Dim FirstPart As Integer
Dim SecondPart As Integer
Dim RsTable As New DSRupees.RupeesDataTable
Dim TARupees As New DSRupeesTableAdapters.RupeesTableAdapter
FirstPart = (Int(num / 10) * 10)
SecondPart = (num - FirstPart)
'-----------------
RsTable = TARupees.GetDataByAmount(Decimal.Parse(FirstPart))
If RsTable.Rows.Count > 0 Then
FirstString = RsTable(0).AmountString.ToString()
End If
'-----
RsTable = TARupees.GetDataByAmount(Decimal.Parse(SecondPart))
If RsTable.Rows.Count > 0 Then
SecondString = RsTable(0).AmountString.ToString()
End If
Under_Hundred_String = FirstString + " " + SecondString
End Function
Private Function Get_Paise(ByVal Number As Double) As String
Dim Cr As Double
Dim La As Double
Dim Th As Double
Dim Hu As Double
Dim Te As Double
Dim RString As String = ""
If Number >= 10000000 Then
Cr = Number / 10000000
Number = Number Mod 10000000
RString = RString + Trim(Return_String(Int(Cr))) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number / 100000
Number = Number Mod 100000
RString = RString + Trim(Return_String(Int(La))) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number / 1000
Number = Number Mod 1000
RString = RString + Trim(Return_String(Int(Th))) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number / 100
Number = Number Mod 100
RString = RString + Trim(Return_String(Int(Hu))) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Trim(Return_String(Int(Te))) End If Get_Paise = RString End Function End Class Directly Print Report Public CrReport As Object CrystalReportViewer1.ReportSource = CrReport Dim Myform As New FrmSuppLedgerPreview Dim DSPrint As New DSCLedgerPrint DSPrint.Tables.Clear() DSPrint.Tables.Add(Print_Detail()) Dim XReport As New CrSLDetail XReport.SetDataSource(DSPrint) Myform.CrReport = XReport Myform.Show(Me) How to apply transaction in typed dataset Imports Microsoft.VisualBasic Imports System.Data.SqlClient Namespace DSBatchTableAdapters Partial Public Class BatchMasterTableAdapter Dim _Transaction As SqlTransaction Public Property Transaction() As SqlTransaction Get Return _Transaction End Get Set(ByVal value As SqlTransaction) _Transaction = value _connection = _Transaction.Connection If _adapter Is Nothing Then Me.InitAdapter() End If For Each command As SqlCommand In Me.CommandCollection command.Transaction = _Transaction Next Me.Adapter.InsertCommand.Transaction = _Transaction Me.Adapter.UpdateCommand.Transaction = _Transaction Me.Adapter.DeleteCommand.Transaction = _Transaction End Set End Property Public Sub SetCommandTimeOut(ByVal PassTime As Integer) For Each Command As SqlCommand In Me.CommandCollection Command.CommandTimeout = PassTime Next End Sub End Class Partial Public Class BatchChildTableAdapter Dim _Transaction As SqlTransaction Public Property Transaction() As SqlTransaction Get Return _Transaction End Get Set(ByVal value As SqlTransaction) _Transaction = value _connection = _Transaction.Connection If _adapter Is Nothing Then Me.InitAdapter() End If For Each command As SqlCommand In Me.CommandCollection command.Transaction = _Transaction Next Me.Adapter.InsertCommand.Transaction = _Transaction Me.Adapter.UpdateCommand.Transaction = _Transaction Me.Adapter.DeleteCommand.Transaction = _Transaction End Set End Property Public Sub SetCommandTimeOut(ByVal PassTime As Integer) For Each Command As SqlCommand In Me.CommandCollection Command.CommandTimeout = PassTime Next End Sub End Class End Namespace How to dictinct record from the data table by data view Dim CustTable As New DataTable Dim DTable As New DataTable CustTable = Return_CustomerTable() Dim DView As New DataView(CustTable) DView.Sort = "Name" DTable = DTV.ToTable() How to track the checkbox status of the check list box Dim Count As Decimal = 0 If ChkCC.CheckedItems.Count > 0 Then
For Count = 0 To ChkCC.Items.Count - 1
If ChkCC.GetItemChecked(Count) = True Then
Count = Count + 1
End If
Next
End If
MsgBox(Count & " items checked.")
How to dictinct record from the data table by data view
Dim Col(0) As String
Col(0) = "CustName"
Dim CustTable As New DataTable
Dim DTable As New DataTable
CustTable = Return_CustomerTable()
Dim DTV As New DataView(CustTable)
DTable = DTV.ToTable(True, Col)
Public Function CommaSeparatedAmt(ByVal Amount As String) As String
Dim strAmt As String = ""
Dim s As String = ""
Dim s1 As String = ""
Dim s2 As String = ""
Dim AmtString As String
Dim DecAmt As String
If Amount.Contains(".") Then
Dim strArr As String() = Split(Amount, ".")
AmtString = strArr(0)
DecAmt = strArr(1)
Else
AmtString = Amount
DecAmt = "00"
End If
Select Case AmtString.Length
Case Is < 4 strAmt = AmtString Case 4 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) strAmt = s + "," + s1 Case 5 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) strAmt = s + "," + s1 Case 6 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 2) s2 = AmtString.ToString.Substring(3, 3) strAmt = s + "," + s1 + "," + s2 Case 7 s = AmtString.ToString.Substring(0, 1) s1 = AmtString.ToString.Substring(1, 3) s2 = AmtString.ToString.Substring(4, 3) strAmt = s + "," + s1 + "," + s2 Case 8 s = AmtString.ToString.Substring(0, 2) s1 = AmtString.ToString.Substring(2, 3) s2 = AmtString.ToString.Substring(5, 3) strAmt = s + "," + s1 + "," + s2 Case 9 s = AmtString.ToString.Substring(0, 3) s1 = AmtString.ToString.Substring(3, 3) s2 = AmtString.ToString.Substring(6, 3) strAmt = s + "," + s1 + "," + s2 Case Else strAmt = AmtString End Select Return strAmt & "." & DecAmt End Function Convert amount in words Public Class ClsRupees Public Function Return_Rupee_String(ByVal Number As Double) As String Dim IntNumber As Long Dim FloatNumber As Decimal If Int(Number) <> Number Then
IntNumber = Int(Number)
FloatNumber = Format((Number - IntNumber), "0.00") * 100
Return_Rupee_String = Convert_To_String(IntNumber)
Return_Rupee_String = UCase("Rs. " + " " + Return_Rupee_String + " AND " & " Paise " & " " + Convert_To_String(FloatNumber) + " Only/-")
Else
Return_Rupee_String = UCase("Rs. " + " " + Convert_To_String(Number) + " Only / -")
End If
End Function
Public Function Convert_To_String(ByVal Number As Double) As String
Dim Cr, La, Th, Hu, Te As Integer
Dim RString As String
Dim sFraction As Long
Dim Fracpos As Long
RString = ""
Number = Math.Abs(Number)
Fracpos = InStr(1, Trim(Number), ".")
If Fracpos > 0 Then
sFraction = Mid(Trim(Number), Fracpos + 1, Len(Trim(Number) - Fracpos))
End If
If Number >= 10000000 Then
Cr = Number \ 10000000
Number = Number Mod 10000000
RString = RString + Return_String(Int(Cr)) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number \ 100000
Number = Number Mod 100000
RString = RString + Return_String(Int(La)) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number \ 1000
Number = Number Mod 1000
RString = RString + Return_String(Int(Th)) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number \ 100
Number = Number Mod 100
RString = RString + Return_String(Int(Hu)) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Return_String(Int(Te)) End If If Val(sFraction) <> 0 Then
If Val(Left(sFraction, 1)) <> 0 Then
If Val(sFraction) < 10 Then RString = RString + " AND " + Trim(Get_Paise(Val(sFraction * 10))) Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If Else RString = RString + " AND " + Trim(Get_Paise(Val(sFraction))) End If End If Convert_To_String = RString End Function Public Function Return_String(ByVal PassNum As Decimal) As String Dim TaRupee As New DSRupeesTableAdapters.RupeesTableAdapter Dim RupeeTable As New DSRupees.RupeesDataTable RupeeTable = TaRupee.GetDataByAmount(PassNum) If RupeeTable.Rows.Count > 0 Then
Return_String = RupeeTable(0).AmountString.ToString()
Else
Return_String = Under_Hundred_String(PassNum)
End If
RupeeTable.Dispose()
TaRupee.Dispose()
End Function
Private Function Under_Hundred_String(ByVal num As Integer) As String
Dim FirstString As String = ""
Dim SecondString As String = ""
Dim FirstPart As Integer
Dim SecondPart As Integer
Dim RsTable As New DSRupees.RupeesDataTable
Dim TARupees As New DSRupeesTableAdapters.RupeesTableAdapter
FirstPart = (Int(num / 10) * 10)
SecondPart = (num - FirstPart)
'-----------------
RsTable = TARupees.GetDataByAmount(Decimal.Parse(FirstPart))
If RsTable.Rows.Count > 0 Then
FirstString = RsTable(0).AmountString.ToString()
End If
'-----
RsTable = TARupees.GetDataByAmount(Decimal.Parse(SecondPart))
If RsTable.Rows.Count > 0 Then
SecondString = RsTable(0).AmountString.ToString()
End If
Under_Hundred_String = FirstString + " " + SecondString
End Function
Private Function Get_Paise(ByVal Number As Double) As String
Dim Cr As Double
Dim La As Double
Dim Th As Double
Dim Hu As Double
Dim Te As Double
Dim RString As String = ""
If Number >= 10000000 Then
Cr = Number / 10000000
Number = Number Mod 10000000
RString = RString + Trim(Return_String(Int(Cr))) + " " + "CRORE "
End If
If Number >= 100000 Then
La = Number / 100000
Number = Number Mod 100000
RString = RString + Trim(Return_String(Int(La))) + " " + "LAKH "
End If
If Number >= 1000 Then
Th = Number / 1000
Number = Number Mod 1000
RString = RString + Trim(Return_String(Int(Th))) + " " + "THOUSAND "
End If
If Number >= 100 Then
Hu = Number / 100
Number = Number Mod 100
RString = RString + Trim(Return_String(Int(Hu))) + " " + "HUNDRED "
End If
If Number < 100 Then Te = Number RString = RString + Trim(Return_String(Int(Te))) End If Get_Paise = RString End Function End Class Directly Print Report Public CrReport As Object CrystalReportViewer1.ReportSource = CrReport Dim Myform As New FrmSuppLedgerPreview Dim DSPrint As New DSCLedgerPrint DSPrint.Tables.Clear() DSPrint.Tables.Add(Print_Detail()) Dim XReport As New CrSLDetail XReport.SetDataSource(DSPrint) Myform.CrReport = XReport Myform.Show(Me) How to apply transaction in typed dataset
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Namespace DSBatchTableAdapters
Partial Public Class BatchMasterTableAdapter
Dim _Transaction As SqlTransaction
Public Property Transaction() As SqlTransaction
Get
Return _Transaction
End Get
Set(ByVal value As SqlTransaction)
_Transaction = value
_connection = _Transaction.Connection
If _adapter Is Nothing Then
Me.InitAdapter()
End If
For Each command As SqlCommand In Me.CommandCollection
command.Transaction = _Transaction
Next
Me.Adapter.InsertCommand.Transaction = _Transaction
Me.Adapter.UpdateCommand.Transaction = _Transaction
Me.Adapter.DeleteCommand.Transaction = _Transaction
End Set
End Property
Public Sub SetCommandTimeOut(ByVal PassTime As Integer)
For Each Command As SqlCommand In Me.CommandCollection
Command.CommandTimeout = PassTime
Next
End Sub
End Class
Partial Public Class BatchChildTableAdapter
Dim _Transaction As SqlTransaction
Public Property Transaction() As SqlTransaction
Get
Return _Transaction
End Get
Set(ByVal value As SqlTransaction)
_Transaction = value
_connection = _Transaction.Connection
If _adapter Is Nothing Then
Me.InitAdapter()
End If
For Each command As SqlCommand In Me.CommandCollection
command.Transaction = _Transaction
Next
Me.Adapter.InsertCommand.Transaction = _Transaction
Me.Adapter.UpdateCommand.Transaction = _Transaction
Me.Adapter.DeleteCommand.Transaction = _Transaction
End Set
End Property
Public Sub SetCommandTimeOut(ByVal PassTime As Integer)
For Each Command As SqlCommand In Me.CommandCollection
Command.CommandTimeout = PassTime
Next
End Sub
End Class
End Namespace
How to dictinct record from the data table by data view
Dim CustTable As New DataTable
Dim DTable As New DataTable
CustTable = Return_CustomerTable()
Dim DView As New DataView(CustTable)
DView.Sort = "Name"
DTable = DTV.ToTable()
How to track the checkbox status of the check list box
Dim Count As Decimal = 0
If ChkCC.CheckedItems.Count > 0 Then
For Count = 0 To ChkCC.Items.Count - 1
If ChkCC.GetItemChecked(Count) = True Then
Count = Count + 1
End If
Next
End If
MsgBox(Count & " items checked.")
How to dictinct record from the data table by data view
Dim Col(0) As String
Col(0) = "CustName"
Dim CustTable As New DataTable
Dim DTable As New DataTable
CustTable = Return_CustomerTable()
Dim DTV As New DataView(CustTable)
DTable = DTV.ToTable(True, Col)
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon