Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Mail Imports System.Math Imports System.IO Imports System.EnterpriseServices Imports System.Drawing Imports System.Web.Security Imports System.Resources.Tools Imports System.Configuration Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Web.UI.WebControls.WebParts Imports System.Web.UI.HtmlControls Imports System.Data.Sql Imports System.Data.OleDb Imports System.Text Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\supplier.xls; Extended Properties=Excel 8.0" 'Dim con As New OleDbConnection(str) 'If con.State = ConnectionState.Open Then ' con.Close() 'End If 'con.Open() 'Dim cmd As New OleDbCommand() 'Dim adt As New OleDbDataAdapter("select * from [Sheet1$]", con) 'Dim ds As New DataSet 'adt.Fill(ds) 'Dim i As Integer = 0 'Dim suplierid As String 'Dim suplieradid As String 'If ds.Tables(0).Rows.Count > 0 Then ' For i = 0 To ds.Tables(0).Rows.Count - 1 ' suplierid = ReturnValue("select supplier_kid from supplier_master where supplier_code='" & ds.Tables(0).Rows(i).Item("Code").ToString & "'") ' suplieradid = ReturnValue("select supplier_AdminUserid from supplier_master where supplier_code='" & ds.Tables(0).Rows(i).Item("Code").ToString & "'") ' ExecuteQuery("update adminuser set adminuser_isdeleted='1' where adminuser_kid='" & suplieradid & "'") ' ExecuteQuery("update Supplier_master set Supplier_isdeleted='1' where supplier_kid='" & suplierid & "'") ' Next 'End If 'con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Sonal ''Session("g_Company") = "00006Z4" ''Session("g_FinYear") = "000010N" ''Session("g_User") = "000008I" '' '' ''Dim dt As DataTable ' '' '' ''Dim dt1 As DataTable ' '' '' ''Dim dt2 As DataTable '' '' ''dt = ReturnDataTable("SELECT Category_Kid, Category_Code, Category_Name FROM Category_Master WHERE Category_Code = N'CTG/2007/1'") '' '' ''Dim Con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString")) 'Define connection variable for connecting to SQL-Server '' '' ''If Con.State = ConnectionState.Open Then '' '' '' Con.Close() 'Open the Connection '' '' ''End If '' '' ''Con.Open() '' '' ''Dim i As Integer '' '' '' '' ''Dim j As Integer '' '' ''For i = 0 To dt.Rows.Count - 1 '' '' '' If dt.Rows.Count > 0 Then '' '' '' Dim cod As String '' '' '' cod = GenerateBankCode() '' '' '' Dim cmd As New SqlCommand("Update Category_Master set Category_Code='" & cod & "' where Category_Kid='" & dt.Rows(i).Item("Category_Kid").ToString & "' ", Con) 'Define command variable to execute the query '' '' '' cmd.ExecuteNonQuery() '' '' '' ' System.Threading.Thread.Sleep(2000) '' '' '' End If '' '' ''Next ' Dim SqlAdapter As New SqlDataAdapter("SELECT top(2) TechSpec_ParameterId FROM ProductTechnicalSpecification where (TechSpec_ParameterId IS NULL) and TechSpec_parameter like'" & dt.Rows(i).Item("TechSpec_Parameter").ToString & "%'", Con) ' Declare Dataadapter Object ' Dim SqlDataSet As New Data.DataSet ' SqlAdapter.Fill(SqlDataSet) ' ' dt1 = ReturnDataTable("SELECT top(2) TechSpec_ParameterId FROM ProductTechnicalSpecification where (TechSpec_ParameterId IS NULL) and TechSpec_parameter='" & dt.Rows(i).Item("TechSpec_Parameter").ToString & "'") ' '' 'System.Threading.Thread.Sleep(500) 'If dt.Rows.Count > 0 Then ' Dim cmd As New SqlCommand("Update AdminUser set AdminUser_Password1='" & Encrypt(dt.Rows(i).Item("AdminUser_Password").ToString) & "' where AdminUser_Kid='" & dt.Rows(i).Item("AdminUser_Kid").ToString & "' ", Con) 'Define command variable to execute the query ' cmd.ExecuteNonQuery() ' ' System.Threading.Thread.Sleep(2000) 'End If 'ExecuteQuery("Update ProductTechnicalSpecification set TechSpec_ParameterId='" & dt.Rows(i).Item("TechSpec_KID").ToString & "' where TechSpec_parameter='" & dt.Rows(i).Item("TechSpec_Parameter").ToString & "' ") 'dt1 = ReturnDataTable("select distinct TechSpec_Parameter from dbo.ProductTechnicalSpecification_Master1 where TechSpec_CategoryId = '" & dt.Rows(i).Item("TechSpec_CategoryId").ToString & "'") 'For j = 0 To dt1.Rows.Count - 1 ' ' InsertMaster(dt1.Rows(j).Item("TechSpec_parameter").ToString, dt.Rows(i).Item("TechSpec_CategoryId").ToString, Session("g_Company").ToString, Session("g_User").ToString, Session("g_FinYear").ToString) ''Next '' Next con.Close() ''Dim ad As New SqlDataAdapter("str", con) ''Dim ds As DataSet End Sub 'Sonal Public Function GenerateBankCode() As String Try Dim i As String Dim qry As String Dim code As String i = "1" i = Format(Val(i), "000") While True code = "CTG" & "/" & Year(Date.Today) & "/" & Format(Month(Date.Today), "00") & "/" & Day(Date.Today) & "/" & i qry = "select count(*) from Category_Master where Category_Code='" & code & "'" If IsAlreadyPresent(qry) Then i = Val(i) + 1 i = Format(Val(i), "000") Else Return code Exit Function End If End While Catch ex As Exception CreateMessageAlert(Me, ex.Message, "StrKeyVal") 'Display the Error Message End Try End Function Public Function ReturnDataTable1(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 ReturnDataTable1 = Nothing 'MsgBox(ex.Message, MsgBoxStyle.Information) 'Display the Error Message End Try End Function Public Function InsertMaster(ByVal strParameter As String, ByVal strCategoryID As String, ByVal strCompanyID As String, ByVal strUserID As String, ByVal strFinancialYearID As String) As String Dim KID As String KID = NewPrimaryKey(Me) 'Dim strMatch As String 'strMatch = ReturnValue("SELECT TechSpec_Kid FROM ProductTechnicalSpecification_Master WHERE TechSpec_CategoryId = '" & strCategoryID.Trim & "' AND TechSpec_IsDeleted = '0' AND (soundex(TechSpec_parameter) = soundex('" & strParameter.Trim & "'))") 'If strMatch <> "" Then ' Return "false" 'End If If IsAlreadyPresent("Select count(*) from ProductTechnicalSpecification_Master where TechSpec_Parameter='" & strParameter.Trim & "' OR TechSpec_Parameter like'" & strParameter.Trim & "%' OR TechSpec_Parameter like'%" & strParameter.Trim & "' OR TechSpec_Parameter like'%" & strParameter.Trim & "%' and TechSpec_CategoryId ='" & strCategoryID.ToString & "'") Then Return "false" Exit Function End If Dim con As New SqlConnection() con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString") 'Read the connection string from web.config Dim cmd As New SqlCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "ProductTechnicalSpecificationMaster_Proc" cmd.Connection = con cmd.Parameters.Add(New SqlParameter("@TechSpec_Kid", SqlDbType.NVarChar, 10)).Value = KID cmd.Parameters.Add(New SqlParameter("@TechSpec_Code", SqlDbType.NVarChar, 25)).Value = Generate_Code("Select count(*) from ProductTechnicalSpecification_Master where TechSpec_Code='", "TSM").ToString() cmd.Parameters.Add(New SqlParameter("@TechSpec_Parameter", SqlDbType.NVarChar, 200)).Value = strParameter cmd.Parameters.Add(New SqlParameter("@TechSpec_CategoryId", SqlDbType.NVarChar, 10)).Value = strCategoryID cmd.Parameters.Add(New SqlParameter("@TechSpec_CompanyId", SqlDbType.NVarChar, 10)).Value = Trim(strCompanyID) cmd.Parameters.Add(New SqlParameter("@TechSpec_UserId", SqlDbType.NVarChar, 10)).Value = Trim(strUserID) cmd.Parameters.Add(New SqlParameter("@TechSpec_FinancialYearId", SqlDbType.NVarChar, 10)).Value = strFinancialYearID.Trim cmd.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 50)).Value = "Product Technical Specification Master" cmd.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 10)).Value = "Insert" Try con.Open() cmd.ExecuteNonQuery() con.Close() Return KID Catch ex As Exception con.Close() Return "false" End Try Return True End Function End Class