SQL :: การเรียกใช้ Class

Post by Goborijung at 2019-10-05 08:38:14 | ID: 143

Imports System.Data
Imports System.Data.SqlClient

Public Class _Default
    Inherits System.Web.UI.Page

    Dim conn As New classConn

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Put user code to initialize the page here
        Call myDataSet()        
        'Call myDataReader()
        'Call myDataTable()
        'Call myQueryExecuteScalar()
        'Call myExecuteNonQuery()
        'Call myExecuteTransaction()
    End Sub

    '*** DataSet ***'
    Sub myDataSet()
        Dim strSQL As String
        Dim ds As DataSet
        strSQL = "SELECT TOP 10 * FROM ACCheckStock "
        ds = conn.QueryDataSet(strSQL)
        'Me.GridView1.DataSource = ds.Tables(0).DefaultView
        Me.GridView1.DataSource = ds.Tables(0)
        Me.GridView1.DataBind()

        '*** Bind Rows ***'
        'If ds.Tables(0).Rows.Count > 0 Then
        '    Me.lblCustomerID.Text = ds.Tables(0).Rows(0)("CustomerID")
        '    Me.lblName.Text = ds.Tables(0).Rows(0)("Name")
        '    Me.lblEmail.Text = ds.Tables(0).Rows(0)("Email")
        '    Me.lblCountryCode.Text = ds.Tables(0).Rows(0)("CountryCode")
        '    Me.lblBudget.Text = ds.Tables(0).Rows(0)("Budget")
        '    Me.lblUsed.Text = ds.Tables(0).Rows(0)("Used")
        'End If
        conn.Close()
    End Sub

    '*** DataReader ***'
    Sub myDataReader()
        Dim strSQL As String
        Dim dtReader As SqlDataReader
        strSQL = "SELECT * FROM Users "
        dtReader = conn.QueryDataReader(strSQL)
        'Me.myDataGrid1.DataSource = dtReader
        'Me.myDataGrid1.DataBind()

        '*** Bind Rows ***'
        'If dtReader.HasRows = True Then
        '    Me.lblCustomerID.Text = dtReader.Item("CustomerID")
        '    Me.lblName.Text = dtReader.Item("Name")
        '    Me.lblEmail.Text = dtReader.Item("Email")
        '    Me.lblCountryCode.Text = dtReader.Item("CountryCode")
        '    Me.lblBudget.Text = dtReader.Item("Budget")
        '    Me.lblUsed.Text = dtReader.Item("Used")
        'End If
        conn.Close()
    End Sub

    '*** DataTable ***'
    Sub myDataTable()
        Dim strSQL As String
        Dim dt As DataTable
        strSQL = "SELECT * FROM Users "
        dt = conn.QueryDataTable(strSQL)
        'Me.myDataGrid3.DataSource = dt
        'Me.myDataGrid3.DataBind()

        '*** Bind Rows ***'
        If dt.Rows.Count > 0 Then
            'Me.lblCustomerID.Text = dt.Rows(0)("CustomerID")
            'Me.lblName.Text = dt.Rows(0)("Name")
            'Me.lblEmail.Text = dt.Rows(0)("Email")
            'Me.lblCountryCode.Text = dt.Rows(0)("CountryCode")
            'Me.lblBudget.Text = dt.Rows(0)("Budget")
            'Me.lblUsed.Text = dt.Rows(0)("Used")
        End If
        conn.Close()
    End Sub

    '*** ExecuteNonQuery ***'
    Sub myExecuteNonQuery()
        Dim strSQL1, strSQL2, strSQL3 As String

        '*** Insert ***'
        strSQL1 = "INSERT INTO Users (uid,uname) " &
        " VALUES('uid003','mad tookmun')"
        If conn.QueryExecuteNonQuery(strSQL1) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        conn.Close()

        '*** Update ***'
        strSQL2 = "UPDATE Users SET uname = 'goborijung' WHERE uid = 'uid002' "
        If conn.QueryExecuteNonQuery(strSQL2) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        conn.Close()

        '*** Delete ***'
        strSQL3 = "DELETE FROM Users WHERE uid = 'uid001' "
        If conn.QueryExecuteNonQuery(strSQL3) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        conn.Close()
    End Sub

End Class

SQL :: เชื่อมต่อ SQL SERVER ผ่าน Master Page ASPX

Post by Goborijung at 2019-10-22 11:09:43 | ID: 162

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site1.Master" CodeBehind="Order.aspx.vb" Inherits="TPF_Web.Order" %>

<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder2" runat="server">
    this Content..
    <asp:Label ID="lblText" runat="server" Text="Label"></asp:Label>
    <%
        Dim conn As SqlConnection
        Dim strconn As String = "Server=S410717NB0201MSSQLSERVER2;Uid=sa;PASSWORD=gik8nv@tpf;database=GSSv2_Prod;Max Pool Size=400;Connect Timeout=600;"
        conn = New SqlConnection(strconn)

        With conn 
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strconn
            .Open()
        End With

        Dim sql As String = ""
        sql = "SELECT * FROM [User]"

        Dim da As New SqlDataAdapter(sql, conn )
        Dim ds As New DataSet
        da.Fill(ds, "User")

        If ds.Tables("User").Rows.Count > 0 Then
            Dim thisTable = ds.Tables("User")
            Response.Write("<br>date is Exist")
            Response.Write("<br>RowCount: " & ds.Tables("User").Rows.Count)
            Response.Write("<br>UserName: " & thisTable.Rows(0).Item("UserName") & "<br>")

            For i As Integer = 0 To thisTable.Rows.Count - 1
                Response.Write("<br>UserName: " & thisTable.Rows(i).Item("UserName") & " | " & thisTable.Rows(i).Item("Password"))
            Next

        End If

    %>
</asp:Content>

SQL :: เชื่อมต่อฐานข้อมูล SQL SERVER ผ่าน ASPX โดยตรง

Post by Goborijung at 2019-10-21 16:23:57 | ID: 161

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">

    Dim objConn As SqlConnection
    Dim objCmd As SqlCommand

    Sub Page_Load(sender As Object, e As EventArgs)
        Dim strConnString As String
        strConnString = "Server=S410717NB0201MSSQLSERVER2;Uid=sa;PASSWORD=gik8nv@tpf;database=GSSv2_Prod;Max Pool Size=400;Connect Timeout=600;"
        objConn = New SqlConnection(strConnString)

        With objConn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConnString
            .Open()
        End With


        If objConn.State = ConnectionState.Open Then
            Me.lblText.Text = "SQL Server Connected"
        Else
            Me.lblText.Text = "SQL Server Connect Failed"
        End If
    End Sub

    Sub Page_UnLoad()
        objConn.Close()
        objConn = Nothing
    End Sub

</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - SQL Server</title>
</head>
<body>
	<form id="form1" runat="server">
    <asp:Label id="lblText" runat="server"></asp:Label>
        <%
            Dim sql As String = ""
            sql = "SELECT * FROM [User]"

            Dim da As New SqlDataAdapter(sql, objConn)
            Dim ds As New DataSet
            da.Fill(ds, "User")

            If ds.Tables("User").Rows.Count > 0 Then
                Response.Write("<br>date is Exist")
            End If

        %>
	</form>
</body>
</html>

SQL SERVER :: การสร้าง Database Diagram

Post by Goborijung at 2019-10-05 09:03:33 | ID: 144

1. คลิกขวาที่ Database Diagram > New Database Diagram
2. เลือกตารางที่เราต้องการจะสร้างความสัมพันธ์
3. ลากฟิวส์ ID ของ Categories ไปยังฟิวส์ FK ของ Products
4. กด Save Diagrams เป็นอันเสร็จเรียบร้อยจร้าาา

การสร้างตัวแปรในระดับ Form และ Local

Post by Goborijung at 2019-09-30 13:56:27 | ID: 123

Dim strWebFormLevel As String = "ตัวแปรระดับฟอร์ม"

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  Response.Write("<br> ค่าของตัวแปร: " & strWebFormLevel)
  
  Dim strLocalLevel As String = "ตัวแปรระดับ Local"
  Response.Write("<br> ค่าของตัวแปร: " & strLocalLevel)
End Sub

การเขียนลุปด้วยคำสั่ง For

Post by Goborijung at 2019-09-30 13:40:48 | ID: 122

Dim i As Integer
For i = 1 To 5
    Dim strVal As String = "ตัวแปรระดับ Block"
    Response.Write("<font size=" & i & ">Hellp ASP " & strVal & "</font><br>")
Next

การใช้งานตัวแปรแบบ Session

Post by Goborijung at 2019-09-30 15:04:59 | ID: 126

<asp:Button ID="cmdClearByName" runat="server" Text="ล้างค่าแบบระบุชื่อ" />
<asp:Button ID="cmdClearAll" runat="server" Text="ล้างค่าทั้งหมด" />

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Session("FullName") = "Thaivb.Net"
        Session("Email") = "thaivb.net@gmail.com"
        Session.Timeout = 10 'นาที

        Response.Write("SID : " & CStr(Session.SessionID) & "<br>")
        Response.Write("ชื่อ-สกุล : " & CStr(Session("FullName")) & "<br>")
        Response.Write("อีเมล์ : " & CStr(Session("Email")))

        Dim i As Integer
        For i = 0 To Session.Count - 1
            Response.Write("<br>" & Session.Contents(i))
        Next

        For i = 0 To Session.Count - 1
            Response.Write("<br>" & Session.Keys(i))
        Next

End Sub

Protected Sub cmdClearByName_Click(sender As Object, e As EventArgs) Handles cmdClearByName.Click
        Session.Remove("FullName")
        Session.Remove("Email")
        Response.Write("<br><br>ชื่อ-สกุล : " & CStr(Session("FullName")) & "<br>")
        Response.Write("อีเมล์ : " & CStr(Session("Email")))
End Sub

Protected Sub cmdClearAll_Click(sender As Object, e As EventArgs) Handles cmdClearAll.Click
        Session.Clear()
        'Session.RemoveAll()
        Response.Write("<br><br>ชื่อ-สกุล : " & CStr(Session("FullName")) & "<br>")
        Response.Write("อีเมล์ : " & CStr(Session("Email")))
End Sub

ตัวอย่างการสร้าง Listbox

Post by Goborijung at 2019-09-30 12:57:11 | ID: 119

<asp:Label ID="Label1" runat="server"  Width="200px"></asp:Label><br />
<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="true" Height="117px" Width="136px">
    <asp:ListItem>VB</asp:ListItem>
    <asp:ListItem>VC#</asp:ListItem>
    <asp:ListItem>ASP</asp:ListItem>
    <asp:ListItem>PHP</asp:ListItem>
    <asp:ListItem>JAVA</asp:ListItem>
    <asp:ListItem>C</asp:ListItem>
    <asp:ListItem>Python</asp:ListItem>
</asp:ListBox>

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

  If Page.IsPostBack = True Then
      Label1.Text = "รายการที่คุณคลิกเลือก : " & ListBox1.SelectedItem.Text
  End If

End Sub

ตัวอย่างการสร้าง Textbox

Post by Goborijung at 2019-09-30 12:49:43 | ID: 116

<asp:TextBox ID="Textbox1" runat="server"></asp:TextBox>

'** Page.IsPostBack เป็น Event ที่เกิดจากการ Submit Form หรือการคลิกปุ่มใด ๆ บนเว็บไซต์ **'

If Page.IsPostBack = True Then
  Response.Write("<br>" & Textbox1.Text)
End If