บันทึกข้อมูลแล้วไม่ลงฐานข้อมูลคับรบกวนทีคับ SQL Server 2005, Web (ASP.NET), VB.NET

Imports System.Data.SqlClient
Imports System.Data
Partial Class webpage10
    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
            Dim CurrYear As Integer = DateTime.Today.Year
            ddl_Year.Items.Add((CurrYear - 1).ToString())
            ddl_Year.Items.Add(CurrYear.ToString())
            ddl_Year.Items.Add((CurrYear + 1).ToString())
            ddl_Year.Items.Add((CurrYear + 2).ToString())

            Dim CurrDay As DateTime = DateTime.Today
            ddl_Day.SelectedValue = (CurrDay.Day).ToString()
            ddl_Month.SelectedValue = (CurrDay.Month).ToString()
            ddl_Year.SelectedValue = (CurrDay.Year).ToString()

           

        End If
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    Protected Sub GetData()
        Dim CondStr As String = ""
        If txt_Search.Text <> "" Then
            CondStr = " and (a.G_memberID like '" & txt_Search.Text & "%' OR a.G_borrowName like '" & txt_Search.Text & "%')"
        End If

        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()


        Dim da As New SqlDataAdapter("select a.*,b.H_FileName from Table_borrow a " & _
" join Table_device b on(b.H_deviceID=a.G_deviceID) where 1=1 " & CondStr, conn)
        Dim ds As New DataSet()
        da.Fill(ds)
        conn.Close()

        GridView1.DataSource = ds
        GridView1.DataBind()

        txt_Count.Text = GridView1.Rows.Count.ToString()
    End Sub

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

    End Sub

    Protected Sub Btn_Add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn_Add.Click
        If Trim(txt_G_deviceID.Text) = "" Then
            lbl_G_memberID.Visible = True
            txt_G_deviceID.Focus()
            Return
        End If
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        Dim cmd As New SqlCommand()
        cmd.CommandText = "INSERT INTO Table_borrow(G_memberID,G_borrowName,G_deviceID, G_deviceID1,G_deviceID2,G_deviceID3,G_deviceID4,G_storeName, G_storeName1,G_storeName2,G_storeName3,G_storeName4, G_numberTel,G_gowhere,G_borrowDate,G_Date,G_FileName)" & _
            " VALUES(@G_memberID,@G_borrowName,@G_deviceID,@G_deviceID1, @G_deviceID2,@G_deviceID3,@G_deviceID4,@G_storeName,@G_storeName1, @G_storeName2,@G_storeName3,@G_storeName4,@G_numberTel, @G_Gowhere,@G_borrowDate,@G_Date,@G_FileName) "

        cmd.Parameters.Add("@G_memberID", SqlDbType.VarChar).Value = txt_G_memberID.Text 'ddl_member.SelectedValue txt_G_memberID.Text
        cmd.Parameters.Add("@G_borrowName", SqlDbType.VarChar).Value = txt_G_borrowName.Text
        cmd.Parameters.Add("@G_deviceID", SqlDbType.VarChar).Value = txt_G_deviceID.Text
        cmd.Parameters.Add("@G_deviceID1", SqlDbType.VarChar).Value = txt_G_deviceID1.Text
        cmd.Parameters.Add("@G_deviceID2", SqlDbType.VarChar).Value = txt_G_deviceID2.Text
        cmd.Parameters.Add("@G_deviceID3", SqlDbType.VarChar).Value = txt_G_deviceID3.Text
        cmd.Parameters.Add("@G_deviceID4", SqlDbType.VarChar).Value = txt_G_deviceID4.Text
        cmd.Parameters.Add("@G_storeName", SqlDbType.VarChar).Value = txt_G_storeName.Text
        cmd.Parameters.Add("@G_storeName1", SqlDbType.VarChar).Value = txt_G_storeName1.Text
        cmd.Parameters.Add("@G_storeName2", SqlDbType.VarChar).Value = txt_G_storeName2.Text
        cmd.Parameters.Add("@G_storeName3", SqlDbType.VarChar).Value = txt_G_storeName3.Text
        cmd.Parameters.Add("@G_storeName4", SqlDbType.VarChar).Value = txt_G_storeName4.Text
        cmd.Parameters.Add("@G_numberTel", SqlDbType.VarChar).Value = txt_G_numberTel.Text
        cmd.Parameters.Add("@G_Gowhere", SqlDbType.VarChar).Value = txt_G_gowhere.Text
        cmd.Parameters.Add("@G_borrowDate", SqlDbType.VarChar).Value = (Convert.ToDateTime(ddl_Day.SelectedValue & "/" & ddl_Month.SelectedValue & "/" & ddl_Year.SelectedValue))
        cmd.Parameters.Add("@G_Date", SqlDbType.DateTime).Value = DateTime.Now

        If Me.FileUpload1.HasFile Then
            Me.FileUpload1.SaveAs(Server.MapPath("Picture/" & FileUpload1.FileName))
            cmd.Parameters.Add("@G_FileName", SqlDbType.VarChar).Value = "Picture/" & FileUpload1.PostedFile.FileName
        Else
            cmd.Parameters.Add("@G_FileName", SqlDbType.VarChar).Value = ""
        End If


        cmd.Connection = conn
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect("ข้อมูลการยืม.aspx")
        GetData()
    End Sub

    Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
        GridView1.EditIndex = -1
        GetData()
    End Sub

    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        GridView1.EditIndex = e.NewEditIndex
        GetData()
    End Sub

    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        Dim cmd As New SqlCommand() 'C_roomID=@C_roomID,
        cmd.CommandText = " UPDATE Table_borrow set G_memberID=@G_memberID, G_borrowName=@G_borrowName, G_deviceID=@G_deviceID,G_deviceID1=@G_deviceID1,G_deviceID2=@G_deviceID2, G_deviceID3=@G_deviceID3,G_deviceID4=@G_deviceID4,G_storeName=@G_storeName, G_storeName1=@G_storeName1,G_storeName2=@G_storeName2,G_storeName3=@G_storeName3, G_storeName4=@G_storeName4,G_numberTel=@G_numberTel,G_Gowhere=@G_Gowhere, G_borrowDate=@G_borrowDate,G_Date=@G_Date " & _
        " where G_memberID=@memberID "
        'CType(GridView1.Rows(e.RowIndex).FindControl("txt_C_roomID"), TextBox).Text
        cmd.Parameters.Add("@G_memberID", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_memberID"), TextBox).Text
        cmd.Parameters.Add("@G_borrowName", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_borrowName"), TextBox).Text

        cmd.Parameters.Add("@G_deviceID", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_deviceID"), TextBox).Text
        cmd.Parameters.Add("@G_deviceID1", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_deviceID1"), TextBox).Text
        cmd.Parameters.Add("@G_deviceID2", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_deviceID2"), TextBox).Text
        cmd.Parameters.Add("@G_deviceID3", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_deviceID3"), TextBox).Text
        cmd.Parameters.Add("@G_deviceID4", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_deviceID4"), TextBox).Text
        cmd.Parameters.Add("@G_storeName", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_storeName"), TextBox).Text
        cmd.Parameters.Add("@G_storeName1", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_storeName1"), TextBox).Text
        cmd.Parameters.Add("@G_storeName2", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_storeName2"), TextBox).Text
        cmd.Parameters.Add("@G_storeName3", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_storeName3"), TextBox).Text
        cmd.Parameters.Add("@G_storeName4", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_storeName4"), TextBox).Text
        cmd.Parameters.Add("@G_numberTel", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_numberTel"), TextBox).Text
        cmd.Parameters.Add("@G_Gowhere", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_Gowhere"), TextBox).Text
        cmd.Parameters.Add("@G_borrowDate", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_borrowDate"), TextBox).Text
        cmd.Parameters.Add("@G_Date", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("txt_G_Date"), TextBox).Text

        cmd.Parameters.Add("@memberID", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("hf_G_memberID"), HiddenField).Value


        cmd.Connection = conn
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

        GridView1.EditIndex = -1
        GetData()
    End Sub

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        Dim cmd As New SqlCommand() 'C_roomID=@C_roomID,
        cmd.CommandText = " DELETE  FROM Table_borrow " & _
        " where G_memberID=@memberID "

        cmd.Parameters.Add("@memberID", SqlDbType.VarChar).Value = CType(GridView1.Rows(e.RowIndex).FindControl("lbl_G_memberID"), Label).Text

        cmd.Connection = conn
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

        GetData()
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Response.Redirect("wf_reportborrow.aspx?borrowID=" & GridView1.SelectedDataKey.Value)
    End Sub

    Protected Sub ddl_Month_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddl_Month.SelectedIndexChanged

    End Sub

    Private Sub getData2(ByVal user As String)

        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_member where A_memberID = @memberID ", conn)


        Dim sqlDa As New SqlDataAdapter(sqlCmd)


        sqlCmd.Parameters.AddWithValue("@memberID", user)
        sqlCmd.Parameters.AddWithValue("@deviceID", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_borrowName.Text = table.Rows(0)("A_membernameL").ToString()  'Where ColumnName is the Field from the DB that you want to display 
            txt_G_numberTel.Text = table.Rows(0)("A_MemberTel").ToString()

        Else

            MsgBox("คุณใส่รหัสบุคลากรไม่ถูกต้อง", 48, "Warnning")
        End If

       


        conn.Close()

    End Sub



    Protected Sub txt_G_memberID_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_memberID.TextChanged
        getData2(txt_G_memberID.Text)
    End Sub

    Protected Sub txt_G_numberTel_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_numberTel.TextChanged

    End Sub

    Private Sub getData3(ByVal user As String)

        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_device where H_deviceID = @deviceID", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@deviceID", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName.Text = table.Rows(0)("H_storeName").ToString()
        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")

        End If

       
        conn.Close()

    End Sub

    Protected Sub txt_G_storeName_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_storeName.TextChanged

    End Sub

    Protected Sub txt_G_deviceID_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID.TextChanged
        getData3(txt_G_deviceID.Text)
    End Sub

  

    
    Private Sub getData4(ByVal user As String)
        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_device where H_deviceID = @G_deviceID1", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@G_deviceID1", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName1.Text = table.Rows(0)("H_storeName").ToString()
        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")
       
        End If
        conn.Close()
    End Sub


    Protected Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID1.TextChanged
        getData4(txt_G_deviceID1.Text)
    End Sub


    Private Sub getData5(ByVal user As String)
        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_device where H_deviceID = @G_deviceID2", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@G_deviceID2", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName2.Text = table.Rows(0)("H_storeName").ToString()

        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")
        End If
        conn.Close()

    End Sub

    Protected Sub TextBox3_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID2.TextChanged
        getData5(txt_G_deviceID2.Text)

    End Sub

    Private Sub getData6(ByVal user As String)
        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_device where H_deviceID = @G_deviceID3", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@G_deviceID3", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName3.Text = table.Rows(0)("H_storeName").ToString()

        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")

        End If
        conn.Close()


    End Sub

    Protected Sub TextBox4_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID3.DataBinding

    End Sub

    Protected Sub TextBox4_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID3.TextChanged
        getData6(txt_G_deviceID3.Text)

    End Sub

    Private Sub getData7(ByVal user As String)
        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  Table_device where H_deviceID = @G_deviceID4", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@G_deviceID4", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName4.Text = table.Rows(0)("H_storeName").ToString()

        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")
        End If
        conn.Close()

    End Sub


    Protected Sub TextBox5_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_G_deviceID4.TextChanged
        getData7(txt_G_deviceID4.Text)


    End Sub

    Protected Sub ddl_member_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddl_member.SelectedIndexChanged
        getData2(ddl_member.SelectedValue)

    End Sub


    Private Sub getData8(ByVal user As String)
        Dim table As New DataTable()
        Dim connStr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString.ToString()
        Dim conn As New SqlConnection(connStr)
        conn.Open()
        Dim sqlCmd As New SqlCommand("SELECT * from  from  Table_member where A_memberID = @memberID ", conn)

        Dim sqlDa As New SqlDataAdapter(sqlCmd)

        sqlCmd.Parameters.AddWithValue("@A_memberID", user)
        sqlDa.Fill(table)
        If table.Rows.Count > 0 Then
            txt_G_storeName.Text = table.Rows(0)("H_storeName").ToString()

        Else
            MsgBox("คุณใส่รหัสครุภัณฑ์ไม่ถูกต้อง", 48, "Warnning")
        End If
        conn.Close()

    End Sub

    
    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "SendReturn" Then
            Dim row As GridViewRow = GridView1.Rows(e.CommandArgument)
            Response.Redirect("webpage11.aspx?BowrowID=" & GridView1.DataKeys(row.RowIndex).Value)
        End If
    End Sub
End Class