Imports System '----------------------------------------------------------------- ' ' Copyright(c) Thymus Solutions Ltd. All rights reserved. ' ' ' Module For All Common Functions used in this Projects. ' ' ' 13/07/06 ' ' ' Felix ' '----------------------------------------------------------------- Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.Sql Imports System.Data.SqlClient Imports System.IO Imports System.Configuration Imports System.Web Imports System.Drawing.Imaging Imports System.Drawing Imports System.Drawing.Drawing2D Imports System.Web.HttpServerUtility Imports System.Decimal Public Module ModCommon Public g_User As String Public g_Company As String Public g_FinYear As String Public CatCount As Integer Public CategoryList As String Public Category(10) As String Public NoDataAvail As String = "N.A." Public Enum Dimensions Width Height End Enum Public Enum AnchorPosition Top Center Bottom Left Right End Enum '---------------------------------------------------------------------------------------------------------------------------- ' ' To Display the Message ' ' ' Query to Fill ComboBox ' ' ' exact Column to be filled ' ' ' DropDownList to be filled ' ' ' Nothing ' 13/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- Public PBDT As New DataTable Public CatDistinct As DataTable Public Sub GetCatDistinct() If IsNothing(CatDistinct) Then CatDistinct = ReturnDataTable("Select * from CategoryDistinct order by Category_Name asc") End If End Sub Public Sub ReturnSearchTable() Try If PBDT.Rows.Count = 0 Then Dim Con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 'Define connection variable for connecting to SQL-Server Con.Open() 'Open the Connection Dim cmd As New SqlCommand("SELECT Brand_Master.Brand_kid, Brand_Master.Brand_Name, Product_Master.Product_Kid, Product_Master.Product_Code, Product_Master.Product_Name, Product_Master.Product_ModelNo, Product_Master.Product_Image FROM Product_Master LEFT OUTER JOIN Brand_Master ON Product_Master.Product_BrandId = Brand_Master.Brand_Kid where Product_Master.Product_Approved ='y' and Product_Master.Product_Isdeleted ='0' order by Product_Name", Con) 'Define command variable to execute the query Dim adp As New SqlDataAdapter 'Define DataReader variable for storing the output of the query adp.SelectCommand = cmd adp.Fill(PBDT) Con.Close() Else Exit Sub End If Catch ex As Exception End Try End Sub Public Function FgenerateTags(ByVal Kid As String, ByVal PName As String) As String() CatCount = 0 'If (Kid = "") Then ' Kid = RemoveLiterals(Session("ProductCode")) 'End If If (PName = "") Then PName = ReturnValue("Select Product_Name from Product_Master where Product_Kid ='" & Kid & "'") End If Dim strReturn(3) As String Dim strReturn1 As String Dim keyWords As String keyWords = ReturnValue("Select Keyword_auto from Product_Keyword where Keyword_ProductId='" & RemoveLiterals(Kid) & "'") strReturn1 = PName + " - " Dim catid As String catid = ReturnValue("Select Product_CategoryID from Product_Master where Product_Kid= '" & Kid & "'and Product_IsDeleted='0'") ' get categoryid according productid CategoryList = "" FillCategoryName(catid) strReturn1 += CategoryList strReturn(0) = strReturn1 ' For Title including productname - category herarchy strReturn(1) = ReturnValue("Select Product_Description from Product_Master where Product_Kid= '" & Kid & "'and Product_IsDeleted='0'") ' CategoryList ' For meta description Content Category Herarchy strReturn(2) = keyWords ' For Meta Keyword Content From Product_Keyword strReturn(3) = CategoryList Return strReturn End Function Public Sub FillCategoryName(ByVal Catid As String) Try Dim dt As DataTable dt = ReturnDataTable("Select * From Category_Master Where Category_Kid ='" & Catid & "' and Category_Isdeleted = '0'") Dim ParentId As String ParentId = dt.Rows(0).Item(3).ToString() If ParentId = "Root Category" Then Dim j As Integer Category(CatCount) = dt.Rows(0).Item(2).ToString() & " " For j = 0 To CatCount CategoryList += Category(j) CatCount = CatCount - 1 Next Exit Sub Else Category(CatCount) = dt.Rows(0).Item(2).ToString() & ", " CatCount = CatCount + 1 FillCategoryName(dt.Rows(0).Item(3).ToString()) End If dt.Clear() Catch ex As Exception End Try End Sub Public Sub Fill_DDList(ByVal StrQry As String, ByVal strColumn As String, ByVal DDList As DropDownList) Try Dim Con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 'Define connection variable for connecting to SQL-Server Con.Open() 'Open the Connection Dim cmd As New SqlCommand(StrQry, Con) 'Define command variable to execute the query Dim dr As SqlDataReader 'Define DataReader variable for storing the output of the query dr = cmd.ExecuteReader 'Assign the output of query to DataReader variable DDList.Items.Clear() 'Clear Previous Items in the DropDownList DDList.Items.Add("Select") 'Add Empty string as First Item of DDList Do While dr.Read 'Adding DataReader Values to DDL DDList.Items.Add(dr.Item(strColumn).ToString) Loop If Con.State = ConnectionState.Open Then Con.Close() ' If Connection Exists Then Close Con = Nothing ' Set Connnection Object as nothing cmd = Nothing ' Set Command Object as nothing dr = Nothing ' Set DataReader Object as nothing Catch ex As Exception CreateMessageAlert(DDList.Page, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub '---------------------------------------------------------------------------------------------------------------------------- ' ' To Fill the Grid with All Records ' ' ' PartType_Name ' ' ' Nothing ' 12/07/06 ' Rajesh ' '---------------------------------------------------------------------------------------------------------------------------- Public Sub Fill_Grid(ByVal StrQry As String, ByVal gv As GridView) Try Dim Con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 'Define connection variable for connecting to SQL-Server Con.Open() 'Open the Connection Dim cmd As New SqlCommand(StrQry, Con) 'Define command variable to execute the query Dim dr As SqlDataReader 'Define DataReader variable for storing the output of the query dr = cmd.ExecuteReader 'Assign the output of query to DataReader variable gv.DataSource = dr 'Set the DataReader Variable as DataSource for the Grid gv.DataBind() If Con.State = ConnectionState.Open Then Con.Close() ' If Connection Exists Then Close Con = Nothing ' Set Connnection Object as nothing cmd = Nothing ' Set Command Object as nothing dr = Nothing ' Set DataReader Object as nothing Catch ex As Exception ' CreateMessageAlert(gv.Page, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub '---------------------------------------------------------------------------------------------------------------------------- ' ' To Display the Message ' ' ' Name of Web Page ' ' ' Message to Display ' ' ' StrKeyVal ' ' ' Nothing ' 13/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- 'Public Sub CreateMessageAlert(ByRef aspxPage As System.Web.UI.Page, ByVal strMessage As String, ByVal strKey As String) ' Try ' Dim strScript As String = "" 'Assign the Script, for the Display of Message ' If (Not aspxPage.IsStartupScriptRegistered(strKey)) Then 'If startup script not registered ' aspxPage.RegisterStartupScript(strKey, strScript) 'Register it ' End If ' Catch ex As Exception ' End Try 'End Sub Public Sub CreateMessageAlert(ByRef aspxPage As System.Web.UI.Page, ByVal strMessage As String, ByVal strKey As String) Try If (Not aspxPage.IsStartupScriptRegistered(strKey)) Then ScriptManager.RegisterStartupScript(aspxPage, aspxPage.GetType, "StrKeyVal", "alert('" & strMessage & "')", True) End If Catch ex As Exception End Try End Sub Public Sub CreateMessageUCAlert(ByRef ascxPage As System.Web.UI.UserControl, ByVal strMessage As String, ByVal strKey As String) Try If (Not ascxPage.Page.IsStartupScriptRegistered(strKey)) Then ScriptManager.RegisterStartupScript(ascxPage, ascxPage.GetType, "StrKeyVal", "alert('" & strMessage & "')", True) End If Catch ex As Exception End Try End Sub Public Function CreateMessageConfirm(ByRef aspxPage As System.Web.UI.Page, ByVal strMessage As String, ByVal strKey As String) As Boolean Try 'Dim b As Boolean 'Dim strScript As String = "" 'Assign the Script, for the Display of Message If (Not aspxPage.IsStartupScriptRegistered(strKey)) Then 'If startup script not registered ScriptManager.RegisterStartupScript(aspxPage, aspxPage.GetType, "StrKeyVal", "confirm('" & strMessage & "')", True) 'aspxPage.RegisterStartupScript(strKey, strScript) 'Register it Else Return False End If Catch ex As Exception End Try End Function '---------------------------------------------------------------------------------------------------------------------------- ' ' For Checking the Validity of ConnectionString, If valid, it returns true otherwise it returns false ' Nothing ' True or False ' 13/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- Public Function IsValidConnectionString() As Boolean IsValidConnectionString = False 'Default Value Dim cn As New SqlConnection 'Define a connection variable for Connectivity with SQL-Server cn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File Try cn.Open() 'Open the Connection IsValidConnectionString = True 'Return True Catch E As Exception IsValidConnectionString = False 'Return False Finally cn.Close() 'Close the Connection End Try cn = Nothing 'Destroy the connection variable End Function Public Sub ExecuteQuery(ByVal StrQry As String) Try Dim Con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 'Define connection variable for connecting to SQL-Server If Con.State = ConnectionState.Closed Then Con.Open() 'Open the Connection End If Dim cmd As New SqlCommand(StrQry, Con) 'Define command variable to execute the query cmd.ExecuteNonQuery() ' Executes the command 'If Con.State = ConnectionState.Open Then Con.Close() ' If Connection Exists Then Close ' Con = Nothing ' Set Connnection Object as nothing cmd = Nothing ' Set Command Object as nothing Catch ex As Exception ' MsgBox(ex.Message, MsgBoxStyle.Information) 'Display the Error Message End Try End Sub '---------------------------------------------------------------------------------------------------------------------------- ' ' To Check existance of record ' ' ' ' Boolean ' 13/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- Public Function IsAlreadyPresent(ByVal StrQry As String) As Boolean Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = Data.CommandType.Text ' Set Command Type IsAlreadyPresent = sqlCmd.ExecuteScalar() ' To Execute the command sqlCmd.Dispose() ' Dispose command object SqlCon.Close() ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception IsAlreadyPresent = False ' if Exception comes return False 'MsgBox(ex.Message) End Try End Function Public Function IsAlreadyPresentVO(ByVal StrQry As String) As Boolean Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionVoffice")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = Data.CommandType.Text ' Set Command Type IsAlreadyPresentVO = sqlCmd.ExecuteScalar() ' To Execute the command sqlCmd.Dispose() ' Dispose command object SqlCon.Close() ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception IsAlreadyPresentVO = False ' if Exception comes return False 'MsgBox(ex.Message) End Try End Function '---------------------------------------------------------------------------------------------------------------------------- ' ' Returns DataTable from the query ' ' ' ' Boolean ' 14/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- Public Function GetRandomPasswordUsingGUID(ByVal length As Integer) As String 'Get the GUID Dim guidResult As String = System.Guid.NewGuid().ToString() 'Remove the hyphens guidResult = guidResult.Replace("-", String.Empty) 'Make sure length is valid If length <= 0 OrElse length > guidResult.Length Then Throw New ArgumentException("Length must be between 1 and " & guidResult.Length) End If 'Return the first length bytes Return guidResult.Substring(0, length) End Function '---------------------------------------------------------------------------------------------------------------------------- ' ' Returns Integer Value from the query ' ' ' ' Record Count Integer Value ' 14/07/06 ' Rajesh ' '---------------------------------------------------------------------------------------------------------------------------- Public Function GenerateCode(ByVal StrQry As String) As Integer Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = CommandType.Text ' Set Command Type sqlCmd.ExecuteScalar() ' To Execute the command If IsDBNull(sqlCmd.ExecuteScalar) = False Then If sqlCmd.ExecuteScalar = 0 Then 'If Return Values is 0 Return 1 'Returns 1 Else Return sqlCmd.ExecuteScalar + Val(1) 'Else Returns No.Of.Records Count +1 End If Else Return 1 End If ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception 'MsgBox(ex.Message) End Try End Function Public Function GenerateUserName(ByVal StrQry As String) As String Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = CommandType.Text ' Set Command Type sqlCmd.ExecuteScalar() ' To Execute the command If (sqlCmd.ExecuteScalar) = " Then 'If Return Values is 0" Then Return 1 'Returns 1 Else Return sqlCmd.ExecuteScalar + Val(1) 'Else Returns No.Of.Records Count +1 End If Return "" ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception 'MsgBox(ex.Message) End Try End Function Public Function ReturnDataTable(ByVal strQuery As String) As Data.DataTable Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim sqlDataTable As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object If SqlCon.State = ConnectionState.Open Then SqlCon.Close() End If SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet sqlDataTable = SqlDataSet.Tables("TableName") ' Get datatable from dataset 'sqlCmd.ExecuteNonQuery() ' Executes Command Return sqlDataTable ' Returns datatable If SqlCon.State = ConnectionState.Open Then SqlCon.Close() End If ' SqlCon.Close() '' Close Connection Object ' SqlCon.Dispose() '' dispose connection Oblect ' sqlCmd.Dispose() '' dispose command object ' SqlAdapter.Dispose() '' dispose adapter Object ' SqlDataSet.Dispose() '' dispose dataset Object 'sqlDataTable.Dispose() '' dispose datatable Object Catch ex As Exception ReturnDataTable = Nothing 'MsgBox(ex.Message, MsgBoxStyle.Information) 'Display the Error Message End Try End Function Public Function Valid_Date(ByVal ddlDay As Integer, ByVal ddlMonth As Integer, ByVal ddlYear As Integer) As Boolean If ddlMonth <> 2 Then If ddlDay = 31 Then If ddlMonth = 2 Or ddlMonth = 4 Or ddlMonth = 6 Or ddlMonth = 9 Or ddlMonth = 11 Then Return False End If End If Else If ddlDay > 29 Then Return False End If End If Return True End Function Public Sub Fill_Combo(ByVal strQuery As String, ByVal ddl As DropDownList) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command ddl.Items.Clear() ' Clear the Items of DropDownList ddl.Items.Insert(0, New ListItem("Select", "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 ddl.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1).ToString.Trim, DT.Rows(i).Item(0).ToString.Trim)) '' Add Items(code,Exact Value) into DropDownList Next i ddl.SelectedIndex = ddl.Items.IndexOf(ddl.Items.FindByValue("")) '' Assigning 0 th Index to DropDownList SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(ddl.Page, ex.Message, "StrKeyVal") 'Display the Error Message ddl = Nothing End Try End Sub Public Sub Fill_ListBox(ByVal strQuery As String, ByVal lst As ListBox) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command lst.Items.Clear() ' Clear the Items of DropDownList lst.Items.Insert(0, New ListItem("Select", "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 lst.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1).ToString.Trim, DT.Rows(i).Item(0).ToString.Trim)) '' Add Items(code,Exact Value) into DropDownList Next i lst.SelectedIndex = lst.Items.IndexOf(lst.Items.FindByValue("")) '' Assigning 0 th Index to DropDownList SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(lst.Page, ex.Message, "StrKeyVal") 'Display the Error Message lst = Nothing End Try End Sub Public Sub FillComboWithOthers(ByVal strQuery As String, ByVal ddl As DropDownList) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command ddl.Items.Clear() ' Clear the Items of DropDownList ddl.Items.Insert(0, New ListItem("Select", "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 ddl.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1), DT.Rows(i).Item(0))) '' Add Items(code,Exact Value) into DropDownList Next i ddl.Items.Insert(ddl.Items.Count, New ListItem("Others", 1)) SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(ddl.Page, ex.Message, "StrKeyVal") 'Display the Error Message ddl = Nothing End Try End Sub Public Sub FillComboWithSelectJJ(ByVal strQuery As String, ByVal ddl As DropDownList, ByVal strSelect As String) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command ddl.Items.Clear() ' Clear the Items of DropDownList ddl.Items.Insert(0, New ListItem(strSelect, "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 ddl.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1), DT.Rows(i).Item(0))) '' Add Items(code,Exact Value) into DropDownList Next i 'ddl.Items.Insert(ddl.Items.Count, New ListItem("Others", "Others")) SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(ddl.Page, ex.Message, "StrKeyVal") 'Display the Error Message ddl = Nothing End Try End Sub Public Sub FillComboWithOthersJJ(ByVal strQuery As String, ByVal ddl As DropDownList, ByVal strSelect As String) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command ddl.Items.Clear() ' Clear the Items of DropDownList ddl.Items.Insert(0, New ListItem(strSelect, "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 ddl.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1), DT.Rows(i).Item(0))) '' Add Items(code,Exact Value) into DropDownList Next i ddl.Items.Insert(ddl.Items.Count, New ListItem("Add New", "Others")) SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(ddl.Page, ex.Message, "StrKeyVal") 'Display the Error Message ddl = Nothing End Try End Sub Public Sub FillComboWithOthersTextOnlyJJ(ByVal strQuery As String, ByVal ddl As DropDownList, ByVal strSelect As String) Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim SqlAdapter As New SqlDataAdapter ' Declare Dataadapter Object Dim SqlDataSet As New Data.DataSet ' Declare Dataset Object Dim DT As New Data.DataTable ' Declare Datatable Oblect Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object Dim i As Integer SqlCon.Open() ' Open Connection SqlAdapter.SelectCommand = sqlCmd ' Set command to Adapter SqlAdapter.Fill(SqlDataSet, "TableName") ' Links Adapter with DataSet DT = SqlDataSet.Tables("TableName") ' Get datatable from dataset sqlCmd.ExecuteNonQuery() ' Executes Command ddl.Items.Clear() ' Clear the Items of DropDownList ddl.Items.Insert(0, New ListItem(strSelect, "Select")) ' Insert Blank Row at index 0 For i = 0 To DT.Rows.Count - 1 ddl.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(0), DT.Rows(i).Item(0))) '' Add Items(code,Exact Value) into DropDownList Next i ddl.Items.Insert(ddl.Items.Count, New ListItem("Others", "Others")) SqlCon.Close() '' Close Connection Object SqlCon.Dispose() '' Dispose Connection Oblect sqlCmd.Dispose() '' Dispose command object SqlAdapter.Dispose() '' Dispose adapter Object SqlDataSet.Dispose() '' Dispose dataset Object DT.Dispose() '' Dispose datatable Object Catch ex As Exception CreateMessageAlert(ddl.Page, ex.Message, "StrKeyVal") 'Display the Error Message ddl = Nothing End Try End Sub Public Sub AddClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = False 'Disable the Add Button btnSave.Enabled = True 'Enable the Save Button btnEdit.Enabled = False 'Disable the Edit Button btnDelete.Enabled = False 'Disable the Delete Button btnCancel.Enabled = True 'Enable the Cancel Button btnSearch.Enabled = False 'Enable the Search Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub SaveClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = True 'Enable the Add Button btnSave.Enabled = False 'Disable the Save Button btnEdit.Enabled = False 'Disable the Edit Button btnDelete.Enabled = False 'Disable the Delete Button btnCancel.Enabled = False 'Disable the Cancel Button btnSearch.Enabled = True 'Enable the Search Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub EditClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = True 'Enable the Add Button btnSave.Enabled = False 'Disable the Save Button btnEdit.Enabled = False 'Disable the Edit Button btnDelete.Enabled = False 'Disable the Delete Button btnCancel.Enabled = False 'Disable the Cancel Button btnSearch.Enabled = True 'Enable the Search Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub DeleteClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = True 'Enable the Add Button btnSave.Enabled = False 'Disable the Save Button btnEdit.Enabled = False 'Disable the Edit Button btnDelete.Enabled = False 'Disable the Delete Button btnCancel.Enabled = False 'Disable the Cancel Button btnSearch.Enabled = True 'Enable the Search Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub CancelClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = True 'Enable the Add Button btnSave.Enabled = False 'Disable the Save Button btnEdit.Enabled = False 'Disable the Edit Button btnDelete.Enabled = False 'Disable the Delete Button btnCancel.Enabled = False 'Disable the Cancel Button btnSearch.Enabled = True 'Enable the Search Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub SearchClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, ByRef btnSearch As Button) Try btnAdd.Enabled = False btnSave.Enabled = False btnEdit.Enabled = False btnDelete.Enabled = False btnCancel.Enabled = True btnSearch.Enabled = False Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub AddClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = False 'Disable the Add Button btnSave.Visible = True 'Enable the Save Button btnEdit.Visible = False 'Disable the Edit Button btnDelete.Visible = False 'Disable the Delete Button btnCancel.Visible = True 'Enable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = False 'Enable the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub SaveClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = True 'Enable the Add Button btnSave.Visible = False 'Disable the Save Button btnEdit.Visible = False 'Disable the Edit Button btnDelete.Visible = False 'Disable the Delete Button btnCancel.Visible = False 'Disable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = True 'Enable the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub EditClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = True 'Enable the Add Button btnSave.Visible = False 'Disable the Save Button btnEdit.Visible = False 'Disable the Edit Button btnDelete.Visible = False 'Disable the Delete Button btnCancel.Visible = False 'Disable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = True 'Enable the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub DeleteClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = True 'Enable the Add Button btnSave.Visible = False 'Disable the Save Button btnEdit.Visible = False 'Disable the Edit Button btnDelete.Visible = False 'Disable the Delete Button btnCancel.Visible = False 'Disable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = True 'visible the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub CancelClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = True 'Enable the Add Button btnSave.Visible = False 'Disable the Save Button btnEdit.Visible = False 'Disable the Edit Button btnDelete.Visible = False 'Disable the Delete Button btnCancel.Visible = False 'Disable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = True 'Enable the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub SearchClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = False btnSave.Visible = False btnEdit.Visible = False btnDelete.Visible = False btnCancel.Visible = True If Not IsNothing(btnSearch) Then btnSearch.Visible = False 'Enable the Search Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub GridClick(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Enabled = False 'Disable the Add Button btnSave.Enabled = False 'Disable the Save Button btnEdit.Enabled = True 'Enable the Edit Button btnDelete.Enabled = True 'Enable the Delete Button btnCancel.Enabled = True 'Enable the Cancel Button btnSearch.Enabled = False 'Enable the Cancel Button Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub GridClickVisible(ByRef aspxPage As System.Web.UI.Page, ByRef btnAdd As Button, ByRef btnSave As Button, ByRef btnEdit As Button, ByRef btnDelete As Button, ByRef btnCancel As Button, Optional ByRef btnSearch As Button = Nothing) Try btnAdd.Visible = False 'Disable the Add Button btnSave.Visible = False 'Disable the Save Button btnEdit.Visible = True 'Enable the Edit Button btnDelete.Visible = True 'Enable the Delete Button btnCancel.Visible = True 'Enable the Cancel Button If Not IsNothing(btnSearch) Then btnSearch.Visible = False 'Enable the Cancel Button End If Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Sub Public Sub EnableDisableButtons(ByRef aspxPage As System.Web.UI.Page) 'Dim cTRL As Control = aspxPage.FindControl(aspxPage.ToString) Dim CTRL As System.Web.UI.Control For Each CTRL In aspxPage.Page.Controls If (CTRL.GetType().ToString().Equals("System.Web.UI.WebControls.Button")) Then 'If TypeOf cTRL Is Button Then If CTRL.ID.ToString.ToUpper = "BTNADD" Then CType(CTRL, Button).Enabled = True If CTRL.ID.ToString.ToUpper = "BTNDELETE" Then CType(CTRL, Button).Enabled = True If CTRL.ID.ToString.ToUpper = "BTNSAVE" Then CType(CTRL, Button).Enabled = True If CTRL.ID.ToString.ToUpper = "BTNSEARCH" Then CType(CTRL, Button).Enabled = True If CTRL.ID.ToString.ToUpper = "BTNCANCEL" Then CType(CTRL, Button).Enabled = True 'If Ctl.ID.ToString.ToUpper = "BTNADD" Then Ctl.Enabled = True 'End If End If Next CTRL End Sub '---------------------------------------------------------------------------------------------------------------------------- ' ' Returns Boolean ' ' ' ' ' ' Boolean ' 31/07/06 ' Felix ' '---------------------------------------------------------------------------------------------------------------------------- Public Function IsValidMail(ByVal EmailAddress As String, ByVal aspxpage As System.Web.UI.Page) As Boolean Try EmailAddress = EmailAddress.Trim.ToString Dim strRegex As String = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" Dim re As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(strRegex) If (re.IsMatch(EmailAddress)) Then Return (True) Else Return (False) Catch ex As Exception CreateMessageAlert(aspxpage, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Function Public Function ReturnValue(ByVal StrQry As String) As String Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = CommandType.Text ' Set Command Type Try sqlCmd.ExecuteScalar().ToString() ' To Execute the command Catch ex As Exception Return "" End Try If (sqlCmd.ExecuteScalar).ToString() = "" Then 'If Return Values is 0" Then Return "" 'Returns "" Else Return sqlCmd.ExecuteScalar.ToString() 'Else Return Value from database End If ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception 'MsgBox(ex.Message) End Try End Function Public Function Returnsinglename(ByRef aspxPage As System.Web.UI.Page, ByVal strQuery As String) As String Returnsinglename = "" Try Dim s As String Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object Dim sqlCmd As New SqlCommand(strQuery, SqlCon) SqlCon.Open() s = sqlCmd.ExecuteScalar If s = Nothing Then Returnsinglename = "" Else Returnsinglename = s End If SqlCon.Close() Catch ex As Exception Returnsinglename = "" CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") End Try End Function Public Sub Fill_Date(ByVal ddlDay As DropDownList, ByVal ddlMonth As DropDownList, ByVal ddlYear As DropDownList) ddlDay.Items.Clear() ddlMonth.Items.Clear() ddlYear.Items.Clear() Dim i As Integer Dim j As String ddlDay.Items.Add("DD") For i = 1 To 31 j = Format(i, "00") ddlDay.Items.Add(j) Next 'ddlDay.Items.Add("---") ddlDay.SelectedIndex = ddlDay.Items.IndexOf(ddlDay.Items.FindByText(Format(Day(Date.Today), "00"))) ddlMonth.Items.Add("MM") For i = 1 To 12 j = Format(i, "00") ddlMonth.Items.Add(j) Next 'ddlMonth.Items.Add("---") ddlMonth.SelectedIndex = ddlMonth.Items.IndexOf(ddlMonth.Items.FindByText(Format(Month(Date.Today), "00"))) Dim l As Long l = 2000 ddlYear.Items.Add("YYYY") For l = 2000 To 2030 ddlYear.Items.Add(l.ToString) Next 'ddlYear.Items.Add("---") ddlYear.SelectedIndex = ddlYear.Items.IndexOf(ddlYear.Items.FindByText((Year(Date.Today)))) End Sub Public Function Check_Date(ByVal ddlDay As Integer, ByVal ddlMonth As Integer, ByVal ddlYear As Integer) As Boolean Try If ddlMonth <> 2 Then 'if month is not feb If ddlDay = 31 Then 'if the day is 31 If ddlMonth = 2 Or ddlMonth = 4 Or ddlMonth = 6 Or ddlMonth = 9 Or ddlMonth = 11 Then 'if month is 2,4,6,9,11 Return False 'returns false End If End If Else 'if month is feb If (ddlYear Mod 100 = 0) Or (ddlYear Mod 4 = 0) Then 'if year is divisible by 100 or 4 If ddlDay > 29 Then 'if day is above 29 Return False 'returns false End If Else 'else if year is not leap year If ddlDay > 28 Then 'if day is above 28 Return False 'returns false End If End If End If Return True Catch ex As Exception End Try End Function Public Sub CheckRights(ByRef aspxPage As System.Web.UI.Page, ByVal UserID As String, ByVal FormName As String) Try Dim strQuery As String Dim dr As SqlDataReader aspxPage.Session("Access_Flag") = False aspxPage.Session("Add_Flag") = False aspxPage.Session("Modify_Flag") = False aspxPage.Session("Delete_Flag") = False strQuery = "select UserAccessRights_AccessFlag ,UserAccessRights_AddFlag,UserAccessRights_ModifyFlag,UserAccessRights_DeleteFlag from UserAccessRights where UserAccessRights_AdminUserId='" & UserID & "' and UserAccessRights_menuname='" & FormName & "'" Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object SqlCon.Open() Dim sqlCmd As New SqlCommand(strQuery, SqlCon) dr = sqlCmd.ExecuteReader If dr.Read Then If dr.GetString(0).Trim = "1" Then aspxPage.Session("Access_Flag") = True Else aspxPage.Session("Access_Flag") = False End If If dr.GetString(1).Trim = "1" Then aspxPage.Session("Add_Flag") = True Else aspxPage.Session("Add_Flag") = False End If If dr.GetString(2).Trim = "1" Then aspxPage.Session("Modify_Flag") = True Else aspxPage.Session("Modify_Flag") = False End If If dr.GetString(3).Trim = "1" Then aspxPage.Session("Delete_Flag") = True Else aspxPage.Session("Delete_Flag") = False End If End If dr.Close() dr = Nothing sqlCmd = Nothing SqlCon.Close() SqlCon = Nothing Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") End Try End Sub Public Function SupplierCheckRights(ByRef aspxPage As System.Web.UI.Page, ByVal UserID As String, ByVal FormName As String) As Boolean Try Dim strQuery As String Dim dr As SqlDataReader strQuery = "select UserAccessRights_ModifyFlag from UserAccessRights where UserAccessRights_AdminUserId='" & UserID & "' and UserAccessRights_menuname='" & FormName & "'" Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare connection Object SqlCon.Open() Dim sqlCmd As New SqlCommand(strQuery, SqlCon) dr = sqlCmd.ExecuteReader If dr.Read Then If dr.GetString(0).Trim = "1" Then Return True Else Return False End If End If dr.Close() dr = Nothing sqlCmd = Nothing SqlCon.Close() SqlCon = Nothing Return False Catch ex As Exception CreateMessageAlert(aspxPage, ex.Message, "StrKeyVal") End Try End Function Public Function NewPrimaryKey(ByVal aspxpage As System.Web.UI.Page) As String NewPrimaryKey = "" Try Dim mycon As New SqlConnection() mycon.ConnectionString = ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File mycon.Open() Dim myCommand As New SqlCommand("New_Kid", mycon) myCommand.CommandType = CommandType.StoredProcedure Dim retValParam As New SqlParameter("@sKey", SqlDbType.Char, 9) retValParam.Direction = ParameterDirection.Output retValParam.IsNullable = True retValParam.Size = 9 myCommand.Parameters.Add(retValParam) Dim reader As SqlDataReader = myCommand.ExecuteReader() NewPrimaryKey = retValParam.Value.ToString myCommand.Dispose() If Not reader.IsClosed Then reader.Close() If mycon.State = ConnectionState.Open Then mycon.Close() Catch Ex As Exception CreateMessageAlert(aspxpage, Ex.Message, "StrKeyVal") NewPrimaryKey = "" End Try End Function Public Function NewPrimaryKeyUserControl(ByVal usercontrol As System.Web.UI.UserControl) As String NewPrimaryKeyUserControl = "" Try Dim mycon As New SqlConnection() mycon.ConnectionString = ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File mycon.Open() Dim myCommand As New SqlCommand("New_Kid", mycon) myCommand.CommandType = CommandType.StoredProcedure Dim retValParam As New SqlParameter("@sKey", SqlDbType.Char, 9) retValParam.Direction = ParameterDirection.Output retValParam.IsNullable = True retValParam.Size = 9 myCommand.Parameters.Add(retValParam) Dim reader As SqlDataReader = myCommand.ExecuteReader() NewPrimaryKeyUserControl = retValParam.Value.ToString myCommand.Dispose() If Not reader.IsClosed Then reader.Close() If mycon.State = ConnectionState.Open Then mycon.Close() Catch Ex As Exception 'CreateMessageAlert(aspxpage, Ex.Message, "StrKeyVal") NewPrimaryKeyUserControl = "" End Try End Function Public Function NewPrimaryKeyVO(ByVal aspxpage As System.Web.UI.Page) As String NewPrimaryKeyVO = "" Try Dim mycon As New SqlConnection() mycon.ConnectionString = ConfigurationManager.AppSettings("ConnectionVoffice") 'Read the ConnectionString from web.config File mycon.Open() Dim myCommand As New SqlCommand("New_Kid", mycon) myCommand.CommandType = CommandType.StoredProcedure Dim retValParam As New SqlParameter("@sKey", SqlDbType.Char, 9) retValParam.Direction = ParameterDirection.Output retValParam.IsNullable = True retValParam.Size = 9 myCommand.Parameters.Add(retValParam) Dim reader As SqlDataReader = myCommand.ExecuteReader() NewPrimaryKeyVO = retValParam.Value.ToString myCommand.Dispose() If Not reader.IsClosed Then reader.Close() If mycon.State = ConnectionState.Open Then mycon.Close() Catch Ex As Exception CreateMessageAlert(aspxpage, Ex.Message, "StrKeyVal") NewPrimaryKeyVO = "" End Try End Function Public Sub Fill_Time(ByVal ddlHour As DropDownList, ByVal ddlMin As DropDownList, ByVal ddlAMPM As DropDownList) Dim i As Integer ddlHour.Items.Clear() ddlHour.Items.Add("HH") For i = 1 To 12 ddlHour.Items.Add(Format(i, "00")) Next i ddlMin.Items.Clear() ddlMin.Items.Add("MM") For i = 0 To 59 ddlMin.Items.Add(Format(i, "00")) Next i ddlAMPM.Items.Clear() ddlAMPM.Items.Add("AM") ddlAMPM.Items.Add("PM") Dim t As TimeSpan Dim mn As Integer t = Date.Now.TimeOfDay If t.Hours = 0 Then mn = 12 ddlAMPM.SelectedIndex = ddlAMPM.Items.IndexOf(ddlAMPM.Items.FindByText("AM")) ElseIf t.Hours > 12 Then mn = t.Hours - 12 ddlAMPM.SelectedIndex = ddlAMPM.Items.IndexOf(ddlAMPM.Items.FindByText("PM")) ElseIf t.Hours = 12 Then mn = t.Hours ddlAMPM.SelectedIndex = ddlAMPM.Items.IndexOf(ddlAMPM.Items.FindByText("PM")) Else mn = t.Hours ddlAMPM.SelectedIndex = ddlAMPM.Items.IndexOf(ddlAMPM.Items.FindByText("AM")) End If ddlHour.SelectedIndex = ddlHour.Items.IndexOf(ddlHour.Items.FindByText(Format(mn, "00"))) ddlMin.SelectedIndex = ddlMin.Items.IndexOf(ddlMin.Items.FindByText(Format(t.Minutes, "00"))) End Sub Public Function ReturnDays(ByVal ddlMonth As Integer, ByVal ddlYear As Integer) As Integer Dim Days As Integer If (ddlMonth) = 1 Or (ddlMonth) = 3 Or (ddlMonth) = 5 Or (ddlMonth) = 7 Or (ddlMonth) = 8 Or (ddlMonth) = 10 Or (ddlMonth) = 12 Then Days = 31 End If If (ddlMonth) = 4 Or (ddlMonth) = 6 Or (ddlMonth) = 9 Or (ddlMonth) = 11 Then Days = 30 End If If (ddlMonth) = 2 And ((ddlYear) Mod CInt(4)) = 0 Then Days = 29 Else If (ddlMonth) = 2 And ((ddlYear) Mod CInt(4)) <> 0 Then Days = 28 End If End If Return Days End Function Public Function ReturnDoubleValue(ByVal StrQry As String) As Double Try Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) ' Declare Connection Variable Dim sqlCmd As New SqlCommand(StrQry, SqlCon) ' Declare Command Variable SqlCon.Open() ' Opens Connection sqlCmd.CommandType = CommandType.Text ' Set Command Type sqlCmd.ExecuteScalar() ' To Execute the command If IsDBNull(sqlCmd.ExecuteScalar) Then 'If Return Values is 0" Then Return 0 'Returns "" Else Return sqlCmd.ExecuteScalar 'Else Return Value from database End If ' Dispose connection Object sqlCmd = Nothing SqlCon = Nothing Catch ex As Exception 'MsgBox(ex.Message) End Try End Function 'Sub MessageBox(ByVal strMessage As String) ' Dim s As String ' Dim p As System.Web.UI.Page ' Try ' If (IsDBNull(HttpContext.Current.ToString) = True) Then Return 0 ' s = "" ' p = CType(HttpContext.Current.Handler, System.Web.UI.Page) ' If (Not (p.IsStartupScriptRegistered("MessageBox"))) Then ' p.RegisterStartupScript("MessageBox", s.ToString()) ' End If ' Catch ex As Exception ' End Try 'End Sub Sub FillModule(ByVal node As TreeNode, ByVal ModuleId As String) Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File Dim connection As SqlConnection connection = New SqlConnection(connString) Dim command As SqlCommand If ModuleId <> "0" Then command = New SqlCommand("select ModuleID,ModuleName from tblModule where moduleid='" & ModuleId & "'", connection) Else command = New SqlCommand("select ModuleID,ModuleName from tblModule", connection) End If Dim adapter As SqlDataAdapter Dim menu As DataSet adapter = New SqlDataAdapter(command) menu = New DataSet() adapter.Fill(menu) Dim newNode As TreeNode Dim row As DataRow If menu.Tables.Count > 0 Then For Each row In menu.Tables(0).Rows newNode = New TreeNode(row("ModuleName").ToString(), row("ModuleID").ToString()) newNode.PopulateOnDemand = True newNode.SelectAction = TreeNodeSelectAction.Expand newNode.CollapseAll() 'If IsAlreadyPresent("select count(*) from tblmodule,user_accessright where tblmodule.moduleid=user_accessright.moduleid and access_flag='1' and tblmodule.moduleid='" & row("moduleid") & "' and user_accessright.UserCode='" & g_User & "'") = True Then node.ChildNodes.Add(newNode) 'End If Next End If End Sub Sub FillMenu(ByVal node As TreeNode) Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File Dim connection As SqlConnection connection = New SqlConnection(connString) Dim menuID As String menuID = node.Value Dim command As SqlCommand command = New SqlCommand("select mainMenuTitle,mainURL,mainMenuID from tblMainMenu where moduleid='" & menuID & "' order by mainMenuTitleOrder", connection) Dim adapter As SqlDataAdapter Dim menu As DataSet adapter = New SqlDataAdapter(command) menu = New DataSet() adapter.Fill(menu) Dim newNode As TreeNode Dim row As DataRow If menu.Tables.Count > 0 Then For Each row In menu.Tables(0).Rows newNode = New TreeNode(row("mainMenuTitle").ToString(), row("mainMenuID").ToString()) newNode.CollapseAll() If IsDBNull(row("mainURL")) = False Then If row("mainURL").ToString <> "" Then newNode.Target = "_parent" newNode.NavigateUrl = row("mainURL").ToString 'If IsAlreadyPresent("select count(*) from tblMainMenu,user_accessright where menuname=mainmenutitle and tblMainmenu.moduleid='" & menuID & "' and access_flag='1' and user_accessright.UserCode='" & g_User & "'") = True Then node.ChildNodes.Add(newNode) newNode.PopulateOnDemand = False newNode.SelectAction = TreeNodeSelectAction.Select 'End If Else 'If IsAlreadyPresent("select count(*) from tblsubmenu,user_accessright where submenutitle=menuname and access_flag='1' and parentmenuid='" & row("mainMenuID") & "' and user_accessright.UserCode='" & g_User & "'") = True Then node.ChildNodes.Add(newNode) newNode.PopulateOnDemand = True newNode.SelectAction = TreeNodeSelectAction.Expand 'End If End If Else 'If IsAlreadyPresent("select count(*) from tblsubmenu,user_accessright where submenutitle=menuname and access_flag='1' and parentmenuid='" & row("mainMenuID") & "' and user_accessright.UserCode='" & g_User & "'") = True Then node.ChildNodes.Add(newNode) newNode.PopulateOnDemand = True newNode.SelectAction = TreeNodeSelectAction.Expand 'End If End If Next End If End Sub Public Sub Fill_ComboWithSelect(ByVal strQuery As String, ByRef ddl As DropDownList, ByVal strSelect As String) Try Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim dt As DataTable Dim adp As SqlDataAdapter adp = New SqlDataAdapter(strQuery, con) adp.SelectCommand.CommandType = CommandType.Text dt = New DataTable adp.Fill(dt) ddl.DataSource = dt ddl.DataValueField = dt.Columns(0).ColumnName ddl.DataTextField = dt.Columns(1).ColumnName ddl.DataBind() ddl.Items.Insert(0, New ListItem(strSelect, "Select")) con.Close() con.Dispose() Catch ex As Exception con.Close() con.Dispose() End Try End Sub Sub FillSubMenu(ByVal node As TreeNode) Dim menuID As String menuID = node.Value Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Read the ConnectionString from web.config File Dim connection As SqlConnection = New SqlConnection(connString) Dim command As SqlCommand = New SqlCommand("select subMenuTitle,URL,subMenuID from tblSubMenu where parentMenuID =" & menuID & " order by subMenuOrder", connection) Dim adapter As SqlDataAdapter = New SqlDataAdapter(command) Dim titles As DataSet = New DataSet() adapter.Fill(titles) Dim row As DataRow If titles.Tables.Count > 0 Then For Each row In titles.Tables(0).Rows Dim newNode As TreeNode = New TreeNode(row("subMenuTitle").ToString(), row("subMenuID").ToString()) newNode.PopulateOnDemand = False newNode.CollapseAll() If IsDBNull(row("URL")) = False Then If row("URL").ToString <> "" Then newNode.Target = "_parent" newNode.NavigateUrl = row("URL").ToString 'If IsAlreadyPresent("select count(*) from tblsubmenu,user_accessright where submenutitle=menuname and access_flag='1' and parentmenuid='" & menuID & "' and user_accessright.UserCode='" & g_User & "'") = True Then node.ChildNodes.Add(newNode) 'End If End If End If newNode.SelectAction = TreeNodeSelectAction.Select Next End If End Sub Function RemoveLiterals(ByVal str As String) As String Try Dim s As String = "" Dim i As Long Dim l As Long Dim ret As String = "" l = str.Length If l <= 7 Then ret = str Else For i = 0 To l - 1 If i + 4 <= l Then If str.Substring(i, 4) = "<" Then s = s & "<" i = i + 3 ElseIf str.Substring(i, 4) = ">" Then s = s & ">" i = i + 3 Else s = s & str.Substring(i, 1) End If Else s = s & str.Substring(i) Exit For End If Next ret = s End If Return ret Catch ex As Exception End Try End Function Public Function Image_upload(ByRef fileup As FileUpload, ByRef strpath As String) As Boolean Try If fileup.HasFile Then Dim filetype As String = Path.GetExtension(fileup.FileName) If filetype = ".gif" Or filetype = ".png" Or filetype = ".jpg" Or filetype = ".jpeg" Then Dim filepath As String = strpath & fileup.FileName Dim ans As Boolean = System.IO.File.Exists(filepath) If ans Then Return False Else fileup.SaveAs(filepath) Return True End If Else Return False End If Else Return False End If Catch ex As Exception End Try End Function ''=======================For Upload All Image in Jpeg format and user define size BY Pankaj Mishra 'Public Function Image_upload(ByRef fileup As FileUpload, ByVal i As Integer, ByVal folderpath As String, ByVal existfile As String) As String ' Try ' Dim filetype As String = Path.GetExtension(fileup.FileName) ' Dim j As Integer = Len(filetype) ' Dim filename As String = fileup.FileName ' Dim file As String = Mid(fileup.FileName, 1, Len(filename) - j) ' If existfile = "" Then ' While IsFileExisting(filename, folderpath) ' function for checking file already exist ' i = i + 1 ' filename = file & i & filetype ' End While ' Else ' filename = existfile ' If FileIsLocked(folderpath & filename) = False Then ' System.IO.File.SetAttributes(folderpath & filename, FileAttributes.Normal) ' End If ' End If ' Dim filepath As String = folderpath & filename ' fileup.SaveAs(filepath) ' fileup.Dispose() ' 'set a working directory ' Dim WorkingDirectory As String = folderpath ' 'create a image object containing a verticel photograph ' Dim imgPhotoVert As Image = Image.FromFile(WorkingDirectory & filename) ' Dim imgPhotoHoriz As Image = Image.FromFile(WorkingDirectory & filename) ' Dim imgPhoto As Image = Nothing ' If imgPhotoVert.Height >= 650 Then ' imgPhoto = ConstrainProportions(imgPhotoVert, 650, Dimensions.Width) ' ElseIf imgPhotoVert.Width >= 650 Then ' imgPhoto = ConstrainProportions(imgPhotoVert, 650, Dimensions.Height) ' Else ' imgPhoto = ConstrainProportions(imgPhotoVert, imgPhotoVert.Width, Dimensions.Width) ' End If ' imgPhotoVert.Dispose() ' imgPhotoHoriz.Dispose() ' imgPhoto.Save(WorkingDirectory & filename) ' imgPhoto.Dispose() ' Return filename ' Catch ex As Exception ' End Try 'End Function Public Function FileIsLocked(ByVal strFullFileName As String) As Boolean Dim blnReturn As Boolean = False Dim fs As System.IO.FileStream Try fs = System.IO.File.Open(strFullFileName, IO.FileMode.OpenOrCreate, IO.FileAccess.Read, IO.FileShare.None) fs.Close() Catch ex As System.IO.IOException blnReturn = True End Try Return blnReturn End Function Public Function ConstrainProportions(ByVal imgPhoto As Image, ByVal Size As Integer, ByVal Dimension As Dimensions, ByVal fixlength As Integer) As Image Dim sourceWidth As Integer = imgPhoto.Width Dim sourceHeight As Integer = imgPhoto.Height Dim sourceX As Integer = 0 Dim sourceY As Integer = 0 Dim destX As Integer = 0 Dim destY As Integer = 0 Dim nPercent As Single = 0 Dim destWidth As Integer = 0 'CInt((sourceWidth * nPercent)) Dim destHeight As Integer = 0 'CInt((sourceHeight * nPercent)) Select Case Dimension Case Dimensions.Width nPercent = (CSng(Size) / CSng(sourceWidth)) destWidth = CInt((sourceWidth * nPercent)) destHeight = CInt((sourceHeight * nPercent)) If destHeight >= fixlength Then nPercent = (CSng(fixlength) / CSng(sourceHeight)) destWidth = CInt((sourceWidth * nPercent)) destHeight = CInt((sourceHeight * nPercent)) End If Exit Select Case Else nPercent = (CSng(Size) / CSng(sourceHeight)) destWidth = CInt((sourceWidth * nPercent)) destHeight = CInt((sourceHeight * nPercent)) If destWidth >= fixlength Then nPercent = (CSng(fixlength) / CSng(sourceWidth)) destWidth = CInt((sourceWidth * nPercent)) destHeight = CInt((sourceHeight * nPercent)) End If Exit Select End Select Dim bmPhoto As New Bitmap(destWidth, destHeight, PixelFormat.Format24bppRgb) bmPhoto.SetResolution(imgPhoto.HorizontalResolution, imgPhoto.VerticalResolution) Dim grPhoto As Graphics = Graphics.FromImage(bmPhoto) grPhoto.InterpolationMode = InterpolationMode.HighQualityBicubic grPhoto.DrawImage(imgPhoto, New Rectangle(destX - 1, destY - 1, destWidth + 1, destHeight + 1), New Rectangle(sourceX, sourceY, sourceWidth - 1, sourceHeight - 1), GraphicsUnit.Pixel) grPhoto.Dispose() Return bmPhoto End Function Public Function Image_upload1(ByRef fileup As FileUpload, ByRef strpath As String) As Boolean Try If fileup.HasFile Then Dim filetype As String = Path.GetExtension(fileup.FileName) If filetype = ".pdf" Then Dim filepath As String = strpath & fileup.FileName Dim ans As Boolean = System.IO.File.Exists(filepath) If ans Then Return False Else fileup.SaveAs(filepath) Return True End If Else Return False End If Else Return False End If Catch ex As Exception End Try End Function '----------------------------------Add By Ramendra---------- Public Function Final_FileUpload(ByRef fileup As FileUpload, ByVal i As Integer, ByVal folderpath As String) As String Try Dim filetype As String = Path.GetExtension(fileup.FileName) Dim j As Integer = Len(filetype) Dim filename As String = fileup.FileName Dim file As String = Mid(fileup.FileName, 1, Len(filename) - j) While IsFileExisting(filename, folderpath) ' function for checking file already exist i = i + 1 filename = file & i & filetype End While Dim filepath As String = folderpath & filename fileup.SaveAs(filepath) Return filename Catch ex As Exception Return "" End Try End Function Public Function IsFileExisting(ByVal filename As String, ByVal folderpath As String) As Boolean Dim filepath As String = folderpath & filename Dim ans As Boolean = System.IO.File.Exists(filepath) Return ans End Function '------------------------------Check NULL Value Functions---------------------------------- Function chkNull(ByVal Text As String) As String If Text = "" Then Return NoDataAvail Else Return "" + Text + "" End If End Function Function chkDBNull(ByVal sender As System.Object) As String If IsDBNull(sender) Then Return NoDataAvail Else Return CType(sender, String) End If End Function Function chkDateDBNull(ByVal sender As System.Object) As String If IsDBNull(sender) Then Return "''" Else Return CType(sender, String) End If End Function Function chkNum(ByVal Text As String) As Decimal If Text = "NULL" Or Text = "" Then Return 0 Else Return CType(Text, Decimal) End If End Function Function chkdate(ByVal Text As String) As String If Text = "" Then Return "NULL" Else Return "'" + Text + "'" End If End Function Public Sub bottomCategory(ByVal catid As String, ByRef gv As GridView) Dim dt As DataTable If IsNothing(CatDistinct) Then GetCatDistinct() End If dt = CatDistinct HttpContext.Current.Session("CategoryDT") = CatDistinct 'If IsNothing(HttpContext.Current.Session("CategoryDT")) Then ' dt = ReturnDataTable("select * from CategoryDistinct order by category_name asc") ' HttpContext.Current.Session("CategoryDT") = dt 'Else ' dt = CType(HttpContext.Current.Session("CategoryDT"), DataTable) 'End If dt.DefaultView.RowFilter = "Category_ParentId ='" & catid & "'" 'dt.AcceptChanges() 'Dim con As New SqlConnection 'con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Dim Strr As String 'Dim lnk As New LinkButton 'Strr = "select * from CategoryDistinct where Category_ParentId ='" & catid & "' order by category_name asc" 'If con.State = Data.ConnectionState.Open Then ' con.Close() 'End If 'con.Open() 'Dim adp As New SqlDataAdapter(Strr, con) 'Dim ds As New DataSet 'adp.Fill(ds) If dt.Rows.Count > 0 Then gv.Columns(1).Visible = True gv.Columns(2).Visible = True gv.Columns(3).Visible = True gv.DataSource = dt.DefaultView.ToTable gv.DataBind() gv.Columns(1).Visible = False gv.Columns(2).Visible = False gv.Columns(3).Visible = False End If 'ds.Clear() 'con.Close() End Sub Public Sub showmessage(ByVal msg As String, ByVal lbl As Label) lbl.Visible = True lbl.Text = msg End Sub Function GenerateSuppCode() Dim code As String Dim dt As DataTable Dim arrCode(3) As Integer Dim FinCode As String 'Dim i As Integer Dim count As Integer dt = ReturnDataTable("Select Supplier_Code From Supplier_Master order by Supplier_Code ") If dt.Rows.Count > 0 Then count = dt.Rows.Count - 1 code = dt.Rows(count).Item("Supplier_Code").ToString() If code = "" Then arrCode(1) = 65 arrCode(2) = 65 arrCode(3) = 65 FinCode = "" FinCode = Chr(arrCode(1)) & Chr(arrCode(2)) & Chr(arrCode(3)) Return FinCode End If If code = Nothing Or code = "" Then arrCode(1) = 65 arrCode(2) = 65 arrCode(3) = 65 Else arrCode(1) = Asc(Mid(code, 1, 1)) arrCode(2) = Asc(Mid(code, 2, 1)) arrCode(3) = Asc(Mid(code, 3, 1)) If arrCode(3) = 90 Then If arrCode(2) = 90 Then arrCode(1) = arrCode(1) + 1 arrCode(2) = 65 arrCode(3) = 65 Else arrCode(2) = arrCode(2) + 1 End If arrCode(3) = 65 Else arrCode(3) = arrCode(3) + 1 End If End If FinCode = "" FinCode = Chr(arrCode(1)) & Chr(arrCode(2)) & Chr(arrCode(3)) GenerateSuppCode = FinCode Else arrCode(1) = 65 arrCode(2) = 65 arrCode(3) = 65 FinCode = "" FinCode = Chr(arrCode(1)) & Chr(arrCode(2)) & Chr(arrCode(3)) GenerateSuppCode = FinCode End If End Function Function GenerateProductCode(ByVal SupplierID As String) As String Dim code As String 'Dim arrCode(3) As Integer Dim suppliercode As String Dim FinCode As String 'Dim dt As DataTable 'dt = ReturnDataTable("Select Product_Code From Product_Master where Product_SupplierID ='" & RemoveLiterals(SupplierID.ToString.Trim) & "'and Product_Isdeleted = '0' order by Product_ApprovedDate") Dim CurrentCode As String CurrentCode = ReturnValue("SELECT MAX(CONVERT(int, SUBSTRING(Product_Code, 4, 6))) AS Code FROM Product_Master WHERE Product_SupplierID ='" & RemoveLiterals(SupplierID.ToString.Trim) & "' AND Product_IsDeleted = '0' AND Product_Approved = 'Y'") If CurrentCode <> "" Then code = CurrentCode ' dt.Rows(dt.Rows.Count - 1).Item("Product_Code").ToString() 'code = "ABK9999" suppliercode = ReturnValue("Select Supplier_Code From Supplier_Master Where Supplier_Kid ='" & RemoveLiterals(SupplierID.ToString.Trim) & "'and Supplier_IsDeleted ='0' order by Supplier_Code") If code = Nothing Or code = "" Then CurrentCode = "001" Else 'CurrentCode = code.Substring(3) If Convert.ToInt32(CurrentCode) < 999 Then CurrentCode = (Convert.ToInt32(CurrentCode) + 1).ToString("000") Else CurrentCode = (Convert.ToInt32(CurrentCode) + 1).ToString End If End If FinCode = "" FinCode = suppliercode & CurrentCode '& Chr(arrCode(1)) & Chr(arrCode(2)) & Chr(arrCode(3)) GenerateProductCode = FinCode Else suppliercode = ReturnValue("Select Supplier_Code From Supplier_Master Where Supplier_Kid ='" & RemoveLiterals(SupplierID.ToString.Trim) & "'and Supplier_IsDeleted ='0'") 'arrCode(1) = "48" 'arrCode(2) = "48" 'arrCode(3) = "49" 'FinCode = "" FinCode = suppliercode & "001" 'Chr(arrCode(1)) & Chr(arrCode(2)) & Chr(arrCode(3)) GenerateProductCode = FinCode End If End Function Public Function DecodeURL(ByVal Str As String) Try Dim Dstr As String Dstr = "" Dim i As Integer For i = 0 To Str.ToString.Length - 1 Dim bt As Byte If Str.Chars(i) = "," Then Exit For End If If Str.Chars(i) = "1" Then bt = Str.Substring(i, 3) i = i + 2 Else bt = Str.Substring(i, 2) i = i + 1 End If Dstr = Dstr + Convert.ToChar(bt) Next Return Dstr Catch ex As Exception End Try End Function Public Function EncodeURL(ByVal Str As String) As String Dim en As New UTF8Encoding Dim bt() As Byte = en.GetBytes(RemoveLiterals(Str)) Dim i As Integer Dim EStr As String EStr = "" For i = 0 To bt.Length - 1 EStr = EStr + bt(i).ToString Next Return EStr End Function Public Function AdminUserLog(ByVal str As String, ByVal usertype As String) As DataSet Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter Dim ObjDS As DataSet cn.Open() Try objAdapter = New SqlDataAdapter("AdminUserLog_Proc", cn) objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_UserName", SqlDbType.NVarChar, 200)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_Usertype", SqlDbType.NVarChar, 200)) objAdapter.SelectCommand.Parameters("@AdminUserLog_UserName").Value = str objAdapter.SelectCommand.Parameters("@AdminUserLog_Usertype").Value = usertype ObjDS = New DataSet objAdapter.Fill(ObjDS) Return ObjDS Catch ex As Exception Throw ex Finally cn.Close() cn.Dispose() End Try End Function Public Function UserLogReport(ByVal str As String, ByVal usertype As String) As DataSet Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter Dim ObjDS As DataSet cn.Open() Try objAdapter = New SqlDataAdapter("UserLogReport_Proc", cn) objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_UserName", SqlDbType.NVarChar, 200)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_UserType", SqlDbType.NVarChar, 50)) objAdapter.SelectCommand.Parameters("@AdminUserLog_UserName").Value = str objAdapter.SelectCommand.Parameters("@AdminUserLog_UserType").Value = usertype ObjDS = New DataSet objAdapter.Fill(ObjDS) Return ObjDS Catch ex As Exception Throw ex Finally cn.Close() cn.Dispose() End Try End Function Public Function FindUniqueRecord(ByVal dt As DataTable, ByVal Supplier_Kid As String) As DataTable Try Dim htable As New Hashtable Dim duplist As New ArrayList For Each dr As DataRow In dt.Rows If htable.Contains(dr("Supplier_Kid")) Then duplist.Add(dr) Else htable.Add(dr("Supplier_Kid"), String.Empty) End If Next For Each dr1 As DataRow In duplist dt.Rows.Remove(dr1) Next Return dt Catch ex As Exception End Try End Function Public Sub InsertAdminUserLog(ByVal kid As String, ByVal username As String, ByVal IP As String, ByVal status As Integer, ByVal usertype As String, ByVal form As String, ByVal mode As String) Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter cn.Open() Dim tran As System.Data.SqlClient.SqlTransaction tran = cn.BeginTransaction(IsolationLevel.Serializable) Try objAdapter = New SqlDataAdapter("Insert_AdminUserLog_Proc", cn) objAdapter.SelectCommand.Transaction = tran objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_Kid", SqlDbType.NVarChar)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_UserName", SqlDbType.VarChar, 200)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_LoginDateTime", SqlDbType.DateTime)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_LogoutDateTime", SqlDbType.DateTime)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_ClientIP", SqlDbType.VarChar, 50)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_Status", SqlDbType.Int)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@AdminUserLog_UserType", SqlDbType.NVarChar, 50)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FormName", SqlDbType.VarChar)) objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar)) objAdapter.SelectCommand.Parameters("@AdminUserLog_Kid").Value = kid objAdapter.SelectCommand.Parameters("@AdminUserLog_UserName").Value = username If mode = "Insert" Then objAdapter.SelectCommand.Parameters("@AdminUserLog_LoginDateTime").Value = System.DateTime.Now objAdapter.SelectCommand.Parameters("@AdminUserLog_LogoutDateTime").Value = System.DBNull.Value End If If mode = "Update" Then objAdapter.SelectCommand.Parameters("@AdminUserLog_LoginDateTime").Value = System.DBNull.Value objAdapter.SelectCommand.Parameters("@AdminUserLog_LogoutDateTime").Value = System.DateTime.Now End If objAdapter.SelectCommand.Parameters("@AdminUserLog_ClientIP").Value = IP objAdapter.SelectCommand.Parameters("@AdminUserLog_Status").Value = status objAdapter.SelectCommand.Parameters("@AdminUserLog_UserType").Value = usertype objAdapter.SelectCommand.Parameters("@FormName").Value = form objAdapter.SelectCommand.Parameters("@Mode").Value = mode objAdapter.SelectCommand.ExecuteNonQuery() tran.Commit() Catch ex As Exception tran.Rollback() Throw ex Finally cn.Close() cn.Dispose() End Try End Sub Public Function GetProductInformation(ByVal fieldName As String, ByVal value As String) As DataTable Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter Dim ObjDS As DataTable cn.Open() Try objAdapter = New SqlDataAdapter("ProductDisplay_Proc", cn) objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure If fieldName = "Product_ModelNo" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_ModelNo", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_ModelNo").Value = value ElseIf fieldName = "Brand_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Brand_Name", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Brand_Name").Value = value ElseIf fieldName = "Product_Code" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_Code", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_Code").Value = value ElseIf fieldName = "Product_CategoryId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_CategoryId", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_CategoryId").Value = RemoveLiterals(value) ElseIf fieldName = "Product_BrandId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_BrandId", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_BrandId").Value = RemoveLiterals(value) ElseIf fieldName = "Product_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_Name", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_Name").Value = value ''''''------updated by Prachi - 18/08/2009 - to implement default search------ ElseIf fieldName = "Default" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Default", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Default").Value = value ''''''------end update------ End If ObjDS = New DataTable objAdapter.Fill(ObjDS) Return ObjDS Catch ex As Exception Throw ex Finally cn.Close() cn.Dispose() End Try End Function Public Function GetProductInformation_Buyer(ByVal fieldName As String, ByVal value As String) As DataTable Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter Dim ObjDS As DataTable cn.Open() Try objAdapter = New SqlDataAdapter("ProductDisplay_Proc", cn) objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure If fieldName = "Product_ModelNo" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_ModelNo", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_ModelNo").Value = value ElseIf fieldName = "Brand_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Brand_Name", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Brand_Name").Value = value ElseIf fieldName = "Product_Code" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_Code", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_Code").Value = value ElseIf fieldName = "Product_CategoryId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_CategoryId", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_CategoryId").Value = value ElseIf fieldName = "Product_BrandId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_BrandId", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_BrandId").Value = value ElseIf fieldName = "Product_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_Name", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_Name").Value = value ElseIf fieldName = "Product_KId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_KId", SqlDbType.NVarChar, 100)) objAdapter.SelectCommand.Parameters("@Product_KId").Value = value End If ObjDS = New DataTable objAdapter.Fill(ObjDS) Return ObjDS Catch ex As Exception Throw ex Finally cn.Close() cn.Dispose() End Try End Function Public Function SelectCategory_BrandName(ByVal field As String, ByVal value As String) As DataTable Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) Dim objAdapter As SqlDataAdapter Dim ObjDS As DataTable cn.Open() Try objAdapter = New SqlDataAdapter("CategoryName_Proc", cn) objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure If field = "Category_KId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Category_KId", SqlDbType.NVarChar)) objAdapter.SelectCommand.Parameters("@Category_KId").Value = value ElseIf field = "Brand_KId" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Brand_KId", SqlDbType.NVarChar)) objAdapter.SelectCommand.Parameters("@Brand_KId").Value = value ElseIf field = "Product_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Product_Name", SqlDbType.NVarChar)) objAdapter.SelectCommand.Parameters("@Product_Name").Value = value ElseIf field = "KeyWord_Name" Then objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@KeyWord_Name", SqlDbType.NVarChar)) objAdapter.SelectCommand.Parameters("@KeyWord_Name").Value = value End If ObjDS = New DataTable objAdapter.Fill(ObjDS) Return ObjDS Catch ex As Exception Throw ex Finally cn.Close() cn.Dispose() End Try End Function '*********************Product Price Calculation **************** Public Function Producttotalprice(ByVal pid As String) As Decimal Try '==========================Installation/Commissioning. Dim Install As Decimal = 0.0 If con.State = ConnectionState.Open Then con.Close() End If con.Open() Dim qry6 As String = "SELECT ProductInstallationCommissioning.InstallationComm_IsRequired, ProductInstallationCommissioning.InstallationComm_IsChargeable,ProductInstallationCommissioning.InstallationComm_Amount, ProductInstallationCommissioning.InstallationComm_IsMaintenanceTraining,ProductInstallationCommissioning.InstallationComm_IsAMCAvailable FROM ProductInstallationCommissioning INNER JOIN Product_Master ON ProductInstallationCommissioning.InstallationComm_ProductId = Product_Master.Product_Kid WHERE InstallationComm_ProductId = '" & pid & "' and InstallationComm_IsDeleted='0'" Dim dr6 As DataTable = ReturnDataTable(qry6) If dr6.Rows.Count > 0 Then If dr6.Rows(0).Item("InstallationComm_IsRequired").ToString = "N" Then 'Label74.InnerHtml = "Not Applicable" Else Install = CType(dr6.Rows(0).Item("InstallationComm_Amount").ToString, Decimal) End If Else End If dr6.Clear() '=============================ProductVat/ExciseDuty/EduCess Dim VAT As Decimal = 0.0 Dim ExciseDuty As Decimal = 0.0 Dim EduCess As Decimal = 0.0 Dim HigherEduCess As Decimal = 0.0 Dim TotalPriceVat As Decimal = 0.0 Dim TotalWithOutT As Decimal = 0.0 Dim Total As Decimal = 0.0 Dim othercharge As Decimal = 0.0 Dim qry7 As String = "SELECT ProductVat.ProductVat_VAT,ProductVat.ProductVat_CST,ProductVat.ProductVat_EduCess, ProductVat.ProductVat_HigherEduCess, ProductVat.ProductVat_ExciseDuty, ProductVat.ProductVat_CSTAgainstC,ISNULL(Product_Master.Product_MarketPrice,'0.0') as Product_MarketPrice, ISNULL(Product_Master.Product_PackagingCharge, N'0.0') AS Product_PackagingCharge, ISNULL(Product_Master.Product_ShippingCharges, N'0.0') AS Product_ShippingCharges ,ISNULL(ProductVat.ProductVat_OtherCharges,N'0.0') as ProductVat_OtherCharges FROM Product_Master LEFT OUTER JOIN ProductVat ON Product_Master.Product_Kid = ProductVat.ProductVat_ProductId WHERE Product_Master.Product_Kid = '" & pid & "' and Product_Isdeleted='0' and Product_Approved='Y'" Dim dr7 As DataTable = ReturnDataTable(qry7) If dr7.Rows.Count > 0 Then Total = Round(CType(dr7.Rows(0).Item("Product_MarketPrice").ToString, Decimal) + CType(dr7.Rows(0).Item("Product_PackagingCharge").ToString, Decimal), 2) '+ dr7("Product_ShippingCharges") If dr7.Rows(0).Item("ProductVat_ExciseDuty").ToString <> "No" And dr7.Rows(0).Item("ProductVat_ExciseDuty").ToString <> "" Then ExciseDuty = ((CType(dr7.Rows(0).Item("ProductVat_ExciseDuty").ToString, Decimal) * Total) / 100) ExciseDuty = Round(ExciseDuty, 2) 'dr7("ProductVat_ExciseDuty").ToString & " %" If dr7.Rows(0).Item("ProductVat_EduCess").ToString <> "No" And dr7.Rows(0).Item("ProductVat_EduCess").ToString <> "" Then EduCess = Round(((ExciseDuty * CType((dr7.Rows(0).Item("ProductVat_EduCess").ToString), Decimal) / 100)), 2) End If If dr7.Rows(0).Item("ProductVat_HigherEduCess").ToString <> "No" And dr7.Rows(0).Item("ProductVat_HigherEduCess").ToString <> "" Then HigherEduCess = Round(((ExciseDuty * CType((dr7.Rows(0).Item("ProductVat_HigherEduCess").ToString), Decimal) / 100)), 2) End If End If If dr7.Rows(0).Item("ProductVat_VAT").ToString <> "No" And dr7.Rows(0).Item("ProductVat_VAT").ToString <> "" Then VAT = ((Total + ExciseDuty + EduCess + HigherEduCess) * CType((dr7.Rows(0).Item("ProductVat_VAT").ToString), Decimal) / 100) VAT = Round(VAT, 2) ''dr7.Rows(0).Item("ProductVat_VAT").ToString & " %" End If If dr7.Rows(0).Item("ProductVat_OtherCharges").ToString <> "" And dr7.Rows(0).Item("ProductVat_OtherCharges").ToString <> "No" Then othercharge = Round(CType((dr7.Rows(0).Item("ProductVat_OtherCharges").ToString), Decimal), 2) End If If VAT <> 0.0 Then TotalPriceVat = Round((Total + ExciseDuty + EduCess + HigherEduCess + VAT + Install), 2) Return TotalPriceVat + othercharge End If If Total <> 0.0 Then TotalWithOutT = Round((Total + ExciseDuty + EduCess + HigherEduCess + Install), 2) Return TotalWithOutT + othercharge End If Else Total = Round(CType(dr7.Rows(0).Item("Product_MarketPrice").ToString, Decimal) + CType(dr7.Rows(0).Item("Product_PackagingCharge").ToString, Decimal), 2) '+ dr7.Rows(0).Item("Product_ShippingCharges") If VAT <> 0.0 Then TotalPriceVat = Round((Total + ExciseDuty + EduCess + HigherEduCess + VAT + Install), 2) Return TotalPriceVat + othercharge End If If Total <> 0.0 Then TotalWithOutT = Round((Total + ExciseDuty + EduCess + HigherEduCess + Install), 2) Return TotalWithOutT + othercharge End If End If dr7.Clear() con.Close() Catch ex As Exception End Try End Function End Module