ASP.NET (VB) Tips รวมเทคนิก

################# sql command select null field #############################

WHERE (car_reserve.car_regis_id is null)

################# how to create Buttom Using Javascript ######## add java script to ASP:Content

************************
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.Button2.Attributes.Add("onClick", "return popWin2();")
Me.Button3.Attributes.Add("onClick", "return popWin3();")
End Sub

###################### how to create Link Using Javascript ###############################

########## how to add confirm column to gridview ################################

<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Delete"
OnClientClick='return confirm("Are you sure you want to delete this entry?");'
Text="Delete" />
</ItemTemplate>
</asp:TemplateField>

################### how to resolve ######################################

Must declare the scalar variable "@sender".

resolve : Just declare DataKeyNames="slno" in the GridView property. It will fix the issue. Thanks.

##########################################################################

The version of SQL Server in use does not support datatype 'date'.

resolve : change 'date' to 'datetime'

############################ How to use CustomValidator ###################

Look in Default10.aspx

<asp:CustomValidator ID="CustomValidator1" runat="server" ControlToValidate="eq_idTextBox"
OnServerValidate="user" Display="Dynamic" ErrorMessage="CustomValidator">* หมายเลขครุภัณฑ์ซ้ำ</asp:CustomValidator>

********************************

Protected Sub user(ByVal source As Object, ByVal args As ServerValidateEventArgs)
Dim strconn As String
Dim sqlproducts As String
strconn = WebConfigurationManager.ConnectionStrings("sot_prefix").ConnectionString
sqlproducts = "select count(login_id) as loginid from teacher where login_id='" & txt1.Text & "'"
Dim conn As New SqlConnection(strconn)
conn.Open()
Dim com As New SqlCommand(sqlproducts, conn)
Dim dr As SqlDataReader = com.ExecuteReader
dr.Read()
If dr("loginid") < 1 Then
Label3.ForeColor = Drawing.Color.SeaGreen
Label3.Text = "ชื่อ Login นี้ว่าง ใช้งานได้ครับ"
args.IsValid = True
Else
Label3.ForeColor = Drawing.Color.Red
Label3.Text = "ชื่อ Login นี้มีคนใช้งานแล้วครับ"
args.IsValid = False
End If
dr.Close()
conn.Close()
End Sub

**************************************

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Page.IsValid Then
Label2.Text = "Ok"
Else
Label2.Text = ""
End If
End Sub

############## how to insert javascript to webform ################################

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<script language=javascript>
function popWin(llInp)
{
window.open("webform2.aspx","aa","width=300,height=300");
return false;
}
</script>
....................
function popWin(cont)
{
window.open('calender.aspx?id=' + cont ,'aa','left=200,top=200,width=200,height=250');
return false;
}

############################## how to reload parent form ######################

window.opener.location.reload();
window.opener.location.reload('reserv_room.aspx');

################ how to return value from child form to parent form ######################

<script language="javascript">
function mainValues()
{ var lsString;
lsString=window.document.Form1['TextBox1'].value;
window.opener.document.getElementById('ctl00_ContentPlaceHolder1_TextBox1').value = lsString;
window.close();
}
</script>

******************************
window.opener.document.getElementById('<%= Request("id") %>').value = lsString;
**********************************

<a href="#" onclick="javascript:popWin('ctl00_ContentPlaceHolder1_TextBox1');"> <b>ปฏิทิน</b></a>
<a href="#" onclick="javascript:popWin('ctl00_ContentPlaceHolder1_TextBox1');"> <b>ปฏิทิน</b></a>

########################## How to open modal windows ######################

<body onblur = "window.focus( )">

########################### How to Using Left function in Select SQL command ####################

left(room_term.day_use,3)=@day_use

where room_term.day_use is field in table room_term
and @day_use is variable

####################### How to Using Insert parameter ################################

SqlDataSource2.InsertParameters("time9").DefaultValue = "~/upload/empty.jpg"
SqlDataSource2.InsertParameters("time10").DefaultValue = "~/upload/empty.jpg"
SqlDataSource2.InsertParameters("time11").DefaultValue = "~/upload/empty.jpg"
SqlDataSource2.InsertParameters("time12").DefaultValue = "~/upload/empty.jpg"
SqlDataSource2.InsertParameters("time13").DefaultValue = "~/upload/empty.jpg"
SqlDataSource2.InsertParameters("status").DefaultValue = ""
SqlDataSource2.Insert()

################################ How to Using Delete parameters ######################

SqlDataSource1.DeleteParameters("room_id").DefaultValue = keyid.ToString()
SqlDataSource1.Delete()
SqlDataSource2.DeleteParameters("room_id").DefaultValue = keyid.ToString()
SqlDataSource2.Delete()

################################ How to Check Duplicate primary key ################

Try
คำสั่ง
Catch ex As Exception
If (ex.Message.Contains("Violation of PRIMARY KEY")) Then
TextBox4.Text = "ข้อมูลซ้ำกรุณาตรวจสอบ"
End If
End Try

################################# How to Convert String to Integer #############################

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
TextBox5.Text = CInt(TextBox3.Text) + CInt(TextBox4.Text)
End Sub

######################### How to Using Selected Row in Gridview #######################

Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim row As GridViewRow = GridView1.SelectedRow
Label2.Text = row.Cells(2).Text
'MsgBox(row.Cells(3).Text)
End Sub

########################## How to remove empty string #############################

Trim(DropDownList2.Text)

########################## How to update Database from Dataset ##########################

Dim strconn As String
Dim sqlstr2 As String
strconn = WebConfigurationManager.ConnectionStrings("sot_prefix").ConnectionString
sqlstr2 = "SELECT * from plan_detail "
Dim adapter As SqlDataAdapter
adapter = New SqlDataAdapter(sqlstr2, strconn)
Dim ds As New DataSet()
Dim objautogen As New SqlCommandBuilder(adapter)
adapter.Fill(ds, "pj")
adapter.Update(ds, "pj")
adapter.Dispose()

######################--- How to Using Transaction ----##########################

conn.Open()
Dim myTrans = conn.BeginTransaction()
Dim com As New SqlCommand()
com.Connection = conn
com.Transaction = myTrans
Try
com.CommandText = sqlstr
Dim row_affected As Integer = com.ExecuteNonQuery()
Label1.Text = ""
TextBox2.Text = ""
TextBox7.Text = ""
adapter.Update(ds, "pj") ' Update plan_detail
adapter.Dispose()
myTrans.commit()
GridView1.DataBind()
Catch ex As Exception
myTrans.rollback()
If (ex.Message.Contains("Violation of PRIMARY KEY")) Then
Label1.Text = "ข้อมูลซ้ำกรุณาตรวจสอบ"
Else
Label1.Text = ex.Message
End If
Finally
conn.Close()
End Try

########################## How to Inner join sub select query ########################

SelectCommand="SELECT plan_balance.server, plan_balance.type_id, plan_balance.balance_id, plan_balance.project_id, prj.project_name, plan_balance.date_plan, plan_balance.date_div, plan_balance.login_id, plan_balance.detail, plan_balance.salary, plan_balance.payment, plan_balance.supply, plan_balance.material, plan_balance.hardgood, plan_balance.amount_plan, plan_balance.available_plan, plan_balance.amount, plan_balance.date_money, plan_balance.available_money, plan_balance.year FROM plan_balance INNER JOIN (select project_id,project_name from plan_detail where year='2552') as prj ON plan_balance.project_id = prj.project_id WHERE (plan_balance.type_id = @type_id) AND (plan_balance.year = @year) ORDER BY plan_balance.project_id, plan_balance.date_plan">

###################### How to select from dataset #################################

Dim arrrow() As DataRow
strexp = "equip_id='" & ds.Tables("equip_detail").Rows(ppp)(0) & "'"
arrrow = ds.Tables("borrow_detail").Select(strexp)
If arrrow.Length > 0 Then 'if 1
For x As Integer = 0 To arrrow.Length - 1 'for 2
If mdate = arrrow(x)(3).ToString() Then ' if 2
'If mdate = ds.Tables("borrow").Rows(x)(3).ToString() Then ' if 2
Dim tstart = arrrow(x)(4)
Dim tend = arrrow(x)(5)
'Dim tstart As Integer = ds.Tables("borrow").Rows(x)(4)
'Dim tend As Integer = ds.Tables("borrow").Rows(x)(5)
For y As Integer = tstart To tend - 1
objrow1(y - 6) = "~/upload/reserv.jpg"
Next y 'y
'Label3.Text += ds.Tables("borrow").Rows(x)(0).ToString() & "<br />"
End If 'end if 2
Next x 'end for 2
End If 'end if 1

######################## How to Using DataSet #################################

Dim tbreport As New DataTable("report")
Dim cequip_id As New DataColumn("equip_id", System.Type.GetType("System.String"))
tbreport.Columns.Add(cequip_id)
Dim objrow2 As DataRow ' create datarow
objrow2 = tbreport.NewRow()
tbreport.Rows.Add(objrow2)
objrow2("equip_id") = ""

######################### how to Close Windows ###############################

Close using click
Label2.Text = "<a href='javascript:self.close()'> ปิดหน้าต่าง (คลิก)</a>"
Close Auto
Response.Write("<script language='javascript'> { window.close();}</script>")
Close Auto No Confirm
Response.Write("<script language='javascript'> { window.open('','_parent','');window.close();}</script>")
Close auto no confirm in 5 second
Label2.Text = "บันทึกข้อมูลเสร็จสมบูรณ์"
Response.Write("<script language='javascript'> {setTimeout('window.open(\'\',\'_parent\',\'\');window.close();',5000)}</script>")

#################### how to refference control in FromView ############################

Protected Sub Calendar1_SelectionChanged(sender As Object, e As EventArgs)
If FormView1.CurrentMode = FormViewMode.Insert Then
Dim tb As TextBox = CType(FormView1.FindControl("bdateTextBox"), TextBox)
Dim tb0 As TextBox = CType(FormView1.FindControl("TextBox3"), TextBox)
Dim calen As Calendar = CType(FormView1.FindControl("calendar1"), Calendar)
tb.Text = calen.SelectedDate.AddYears(543)
tb0.Text = calen.SelectedDate

End If
End Sub

Protected Sub Calendar2_SelectionChanged(sender As Object, e As EventArgs)
'If FormView1.CurrentMode = FormViewMode.Edit Then
Dim tb As TextBox = CType(FormView1.FindControl("bdateTextBox"), TextBox)
Dim tb0 As TextBox = CType(FormView1.FindControl("bdateTextBox0"), TextBox)
Dim calen As Calendar = CType(FormView1.FindControl("calendar2"), Calendar)
tb.Text = calen.SelectedDate.AddYears(543)
tb0.Text = calen.SelectedDate
'End If
End Sub

################## Using Dataset #########################################

Dim strconn As String
Dim sqlstr As String
strconn = WebConfigurationManager.ConnectionStrings("sot_prefix").ConnectionString
sqlstr = "SELECT project_name from plan_detail where project_id='" & Trim(TextBox1.Text) & "'"
Dim adapter As SqlDataAdapter
adapter = New SqlDataAdapter(sqlstr, strconn)
Dim ds As New DataSet()
adapter.Fill(ds, "pj")

###################################################################

Dim provider As String = "Provider=SQLOLEDB;"
provider += "Data Source=(local)\SQLExpress;"
provider += "Initial Catalog=Northwind;"
provider += "Integrated Security=SSPI"
Dim conn As New OleDbConnection(provider)
Dim sql As String = "SELECT * FROM Customers WHERE Country = 'UK'"
Dim adapter As OleDbDataAdapter
adapter = New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()
adapter.Fill(ds, "customers")
<%
Dim num_cols As Integer = ds.Tables("customers").Columns.Count
For i As Integer = 0 To num_cols - 1
Response.Write("<th>" & ds.Tables("customers").Columns(i).ToString() & "</th>")
Next
%>
</tr>
<%
Dim num_rows As Integer = ds.Tables("customers").Rows.Count
For i As Integer = 0 To num_rows - 1
Response.Write("<tr>")
For j As Integer = 0 To num_cols - 1
Response.Write("<td>" & ds.Tables("customers").Rows(i)(j).ToString())
Response.Write(" </td>")
Next
Response.Write("</tr>")
Next
%>

#################### Using Dataadapter #########################################

<%
Dim path As String = Server.MapPath("~/Northwind.mdb")
Dim provider As String
provider = "Provider=Microsoft.Jet.OleDb.4.0;"
provider += "Data Source=" & path
Dim conn As New OleDbConnection(provider)
Dim sql As String = "SELECT * FROM Customers"
Dim command As OleDbCommand
command = New OleDbcommand(sql, conn)
Dim reader As OleDbDataReader
conn.Open()
reader = command.ExecuteReader()
Response.Write("<table border=""1"">")
While(reader.Read())
Response.Write("<tr><td>")
Response.Write(reader("CompanyName"))
Response.Write("</td><td>" & reader("Phone"))
Response.Write("</td></tr>")
End While
Response.Write("</table>")
%>

####################### how to signout *********************************************

FormsAuthentication.SignOut();
Roles.DeleteCookie();
Session.Clear();

###################### Gridview How to Custom Delete button ########################

<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CommandName="DeleteMod" OnClientClick='return confirm("ลบแล้วจะไม่สามารถเรียกคืนได้ คุณแน่ใจหรือไม่ ?");' CommandArgument='<%#Eval("uid")%>' Text="Button" />
</ItemTemplate>
</asp:TemplateField>

********************

Private Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
If e.CommandName = "DeleteMod" Then
Dim Index1 As String = e.CommandArgument.ToString
Try
SqlDataSource1.DeleteParameters("uid").DefaultValue = Index1
SqlDataSource1.Delete()
Label2.Text = ""
Catch ex As Exception
Label2.Text = "ไม่สามารถลบข้อมูล เนื่องจากมีการอ้างอิงจากตารางอื่น"
End Try
End If
End Sub

######################### อ่านข้อมูลจาก HyperLink in Gridview ********************

convert hyperlink field to template

ตัวอย่างที่ 1 1 ตัวแปร

<asp:TemplateField HeaderText="ไฟล์เอกสาร (คลิกเปิดอ่าน)">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# Eval("filename") %>' Target="_blank" Text='<%# Eval("filename") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>

ตัวอย่างที่ 2 มากกว่า 1 ตัวแปร

<asp:TemplateField HeaderText="ไฟล์เอกสาร (คลิกเปิดอ่าน)">
<ItemTemplate>
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl='<%# String.Format("openfile_popup.aspx?filename={0}&id_no={1}&upload_date={2}", Eval("filename"), Eval("id_no"), Eval("upload_date")) %>' Target="_blank" Text='<%# Eval("filename") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>

*********** vb code

Dim tb As HyperLink = CType(row.FindControl("HyperLink1"), HyperLink)
Session("mfilename") = tb.Text

################ how to add row into gridview reserve data ********************

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim total As Single = 0
        Dim table As DataTable = GetTable()
        Dim dr As DataRow
        ' Access Rows property on DataTable.
        For Each row As GridViewRow In GridView2.Rows
            dr = table.NewRow()
            dr(0) = row.Cells(1).Text
            dr(1) = row.Cells(2).Text
            dr(2) = row.Cells(3).Text
            dr(3) = row.Cells(4).Text
            dr(4) = row.Cells(5).Text
            dr(5) = row.Cells(5).Text * row.Cells(4).Text
            total = total + dr(5)
            table.Rows.Add(dr)
        Next
        dr = table.NewRow()
        dr(0) = TextBox1.Text
        dr(1) = Label1.Text
        dr(2) = Label2.Text
        dr(3) = TextBox3.Text
        dr(4) = Label3.Text
        dr(5) = TextBox3.Text * Label3.Text
        total = total + dr(5)
        table.Rows.Add(dr)
        GridView2.DataSource = table
        GridView2.DataBind()
        Label4.Text = total
    End Sub

    Function GetTable() As DataTable
        Dim table As New DataTable
        table.Columns.Add("receipt", GetType(String))
        table.Columns.Add("product_id", GetType(String))
        table.Columns.Add("product_name", GetType(String))
        table.Columns.Add("numsale", GetType(Integer))
        table.Columns.Add("pricesale", GetType(Single))
        table.Columns.Add("sum", GetType(Single))
        Return table
    End Function

##################### Filter data to report #############################

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetDataBy1"
TypeName="Ithesis.ithesisDataSetTableAdapters.DataTable2TableAdapter">
<SelectParameters>
<asp:Parameter Name="id_no" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>

Code VB

Button1_Click(sender, e)
Me.ObjectDataSource1.SelectParameters("id_no").DefaultValue = Session("unamestd")
Me.ObjectDataSource1.Select()
Me.ReportViewer1.LocalReport.Refresh()

########## How to bypass validation for a button in ASP.NET? #######################

<asp:Button runat="Server" ... CausesValidation="False" />

##################### how to add checkbox into Gridview and programming *******************

<asp:TemplateField HeaderText="เลือก">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>

*******************

For Each grdrow As GridViewRow In GridView2.Rows ' for 1
chkbox = CType(grdrow.FindControl("checkbox1"), CheckBox)
If chkbox.Checked Then
Label4.Text = grdrow.Cells(1).Text ' login_id
Label5.Text = grdrow.Cells(2).Text ' room_id
Label6.Text = grdrow.Cells(4).Text ' day_use
mdayreserv = Label6.Text
mdayreserv = mdayreserv.AddYears(-543)
Label8.Text = grdrow.Cells(5).Text ' time_use
sqlstr = "update room_reserv set status_cancel='v',tran_datetime='" & DateTime.Now.ToString("s") & "' where (login_id='"
sqlstr += Label4.Text & "')and(room_id='" & Label5.Text & "')and(time_use='" & Label8.Text & "')"
sqlstr += " and (day_use='" & mdayreserv & "') and (status_use<>'v')"
Dim com As New SqlCommand(sqlstr, conn)
row_affected = com.ExecuteNonQuery()
If row_affected >= 1 Then
'Response.Redirect("~/members/yourreserv.aspx")
Else
Label7.Text = "เกิดข้อผิดพลาดในการบันทึกข้อมูลกรุณาลองใหม่"
End If
End If
Next ' next 1

*********************************************

################# Connection strings for MySQL with ADO.NET ODBC ############

Dim strconn As String
strconn = "Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;uid=chiawchan;pwd=1111;database=chiawchan;port=3306"
Dim conn As New OdbcConnection(strconn)
Dim sqlcmd As String = "SELECT * FROM `table 3`"
Dim cmd As New OdbcCommand(sqlcmd, conn)
Dim adapter As New OdbcDataAdapter(cmd)
Dim ds As New DataSet()
adapter.Fill(ds, "product")
GridView1.DataSource = ds.Tables("product")
GridView1.DataBind()

################# การใส่ลำดับใน Gridview ############

1. เพิ่มคอลัมน์ในตาราง

2. เพิ่มคอลัมน์ใน Gridview แล้ว convert to template

3. เขียนโค้ด

Private Sub GridView1_DataBound(sender As Object, e As EventArgs) Handles GridView1.DataBound
Dim rowx As Integer = 0
Dim idx As Integer = 1
Dim ID As Label
For Each row As GridViewRow In GridView1.Rows
ID = CType(GridView1.Rows(rowx).FindControl("Label1"), Label)
ID.Text = idx
idx += 1
rowx += 1
Next
End Sub

รายละเอียด: เขียนโดย Super User; เผยแพร่เมื่อ: 12 มิถุนายน 2562; ฮิต: 2968