Imports System
Imports System.Data
Imports System.Data.SqlClient
Partial Class AutoKeyWord
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            FillFactoryCombo("SELECT distinct Product_Master.Product_Kid, Product_Master.Product_Name + ' - ' + Product_Master.Product_Code + ' - ' + isnull(Product_Master.Product_ModelNo,'') AS [Product Name]   FROM Product_Master LEFT OUTER JOIN Product_Keyword ON Product_Keyword.Keyword_ProductId = Product_Master.Product_Kid WHERE  (Product_AutoKeywordFlag='0') and (Product_Master.Product_Isdeleted='0') and  LEN(Product_Description)>'60' order by [Product Name]", DropDownList1)

        End If
        Label1.Visible = False

    End Sub

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        TextBox1.Text = ReturnValue("Select Product_Description from Product_Master where Product_Kid ='" & DropDownList1.SelectedItem.Value & "'")
        TextBox2.Text = ReturnValue("Select Keyword_Auto from Product_Keyword where Keyword_ProductId ='" & DropDownList1.SelectedItem.Value & "'")
    End Sub

    Public Sub FillFactoryCombo(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 ProductName - ProductCode - Product ModelNo", "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.SelectedIndex = ddl.Items.IndexOf(ddl.Items.FindByValue(""))     '' Assigning 0 th Index to DropDownList

            'If flag = "F" Then
            '    ddl.Items.Add(New ListItem("Add New Factory", "Add New"))
            'ElseIf flag = "W" Then
            '    ddl.Items.Add(New ListItem("Add New WareHouse", "Add New"))
            'ElseIf flag = "C" Then
            '    ddl.Items.Add(New ListItem("Add New Client", "Add New"))
            '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
            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

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Keyid As String
        Keyid = ReturnValue("Select Keyword_Kid from Product_Keyword where Keyword_Productid ='" & RemoveLiterals(DropDownList1.SelectedItem.Value) & "'")
        Dim keyWordPresent As String
        keyWordPresent = ReturnValue("Select Keyword_auto from Product_Keyword where Keyword_ProductId='" & RemoveLiterals(DropDownList1.SelectedItem.Value) & "'")
        Dim KeyAuto As String
        KeyAuto = TextBox2.Text
        If Keyid = "" Then
            ExecuteQuery("Insert into Product_Keyword (Keyword_Kid, Keyword_ProductId, Keyword_Name, Keyword_Auto, Keyword_Flag) values ('" & NewPrimaryKey(Me.Page) & "','" & RemoveLiterals(Request.QueryString("ProductCode")) & "', '' , '" & KeyAuto & "','1')")
            ExecuteQuery("Update Product_Master set Product_AutoKeywordFlag='1' where Product_Kid ='" & RemoveLiterals(DropDownList1.SelectedItem.Value) & "'")
        ElseIf Keyid <> "" Then
            ExecuteQuery("Update Product_Keyword set Keyword_Flag='1', Keyword_Auto ='" & KeyAuto & "' where Keyword_Kid ='" & Keyid & "'")
            ExecuteQuery("Update Product_Master set Product_AutoKeywordFlag='1' where Product_Kid ='" & RemoveLiterals(DropDownList1.SelectedItem.Value) & "'")
        End If
        FillFactoryCombo("SELECT distinct Product_Master.Product_Kid, Product_Master.Product_Name + ' - ' + Product_Master.Product_Code + ' - ' + isnull(Product_Master.Product_ModelNo,'') AS [Product Name]   FROM Product_Master LEFT OUTER JOIN Product_Keyword ON Product_Keyword.Keyword_ProductId = Product_Master.Product_Kid WHERE  (Product_AutoKeywordFlag='0') and (Product_Master.Product_Isdeleted='0')", DropDownList1)
        DropDownList1.ClearSelection()
        TextBox1.Text = ""
        TextBox2.Text = ""
        showmessage("Record Saved Successfully.", Label1)
    End Sub
End Class