hye guys,
im new into this forum. Im so dumb to find this solution. i have a set of queries stored in database but its not a stored procedure. The fileds in the Db consist of Method,and filter and such.. the method we used as selected value from checkboxlist. After we select the method then we execute the query and filter the values by using filter field. For example, i have 2 methods which are bycampus and bycollege.
If i choose both method and each method i only choose 1 values, then there's no problem.
But the problem is, if i choose 1 method with multivalues. For examples, i choose bycampus methods and the values is : (1)New York campus and (2)London campus.
So, the queries become like this :
select s.campusid from student s
where (s.campusid in (1) and s.campusid in (2))
So, how to adjust the code behind so that i got a correct query like this:
select s.campusid from student s
where (s.campusid in (1,2))
And this is my codebehind :
im new into this forum. Im so dumb to find this solution. i have a set of queries stored in database but its not a stored procedure. The fileds in the Db consist of Method,and filter and such.. the method we used as selected value from checkboxlist. After we select the method then we execute the query and filter the values by using filter field. For example, i have 2 methods which are bycampus and bycollege.
If i choose both method and each method i only choose 1 values, then there's no problem.
But the problem is, if i choose 1 method with multivalues. For examples, i choose bycampus methods and the values is : (1)New York campus and (2)London campus.
So, the queries become like this :
select s.campusid from student s
where (s.campusid in (1) and s.campusid in (2))
So, how to adjust the code behind so that i got a correct query like this:
select s.campusid from student s
where (s.campusid in (1,2))
And this is my codebehind :
VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports System.ComponentModel
Imports System.Net.Mail
Partial Class _Default
Inherits System.Web.UI.Page
Dim dr As SqlDataReader
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim methodstring As String
Dim j As Integer
Dim chk(j) As CheckBox
Dim name(j) As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim uname As String = Class1.GetNTAccount
Label2.Text = uname.ToUpper
Dim conn, conn2 As New SqlConnection
Dim com, com2 As New SqlCommand
conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
conn.Open()
'Session("methodstring") = methodstring
Dim sql As String
sql = "select method, lookup, description from emailstudent where method in ( " & Session("methodstring") & ")"
com.Connection = conn
'com.Parameters.Clear()
'com.Parameters.AddWithValue("methodstring", Session("methodstring"))
com.CommandText = sql
dr = com.ExecuteReader
If dr.HasRows = True Then
While dr.Read()
Dim lbl As New Label
lbl.Text = dr("description")
PlaceHolder1.Controls.Add(lbl)
Dim mybreak = New LiteralControl
mybreak.Text = "<BR>"
PlaceHolder1.Controls.Add(mybreak)
conn2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim dr2 As SqlDataReader
Dim sql2 As String
sql2 = dr("lookup")
com2.CommandText = sql2
com2.Connection = conn2
conn2.Open()
dr2 = com2.ExecuteReader
If dr2.HasRows = True Then
While dr2.Read()
Dim chk As New CheckBox
chk.ID = "cb" & " " & dr("method") & " " & dr2.Item(0).ToString
PlaceHolder1.Controls.Add(chk)
PlaceHolder1.Controls.Add(New LiteralControl(dr2.Item(1).ToString))
Dim mybreak2 = New LiteralControl
mybreak2.Text = "<BR>"
PlaceHolder1.Controls.Add(mybreak2)
End While
End If
dr2.Close()
conn2.Close()
Dim mybreak3 As New LiteralControl
mybreak3.Text = "<BR><BR>"
PlaceHolder1.Controls.Add(mybreak3)
End While
End If
dr.Close()
conn.Close()
Button2.Visible = True
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim conn3, conn4, conn5 As New SqlConnection
Dim com3, com4, com5 As New SqlCommand
Dim dr3, dr4 As SqlDataReader
conn3.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
conn3.Open()
'Session("methodstring") = methodstring
Dim sql As String
sql = "select method, lookup, description from emailstudent where method in (" & Session("methodstring") & ") "
com3.CommandText = sql
com3.Connection = conn3
dr3 = com3.ExecuteReader
'MsgBox(sql)
'If dr.HasRows = True Then
Dim sqlmain As String = "select distinct(s.studentid),s.EMail,sa.SemesterCode " _
& " from Student s" _
& " left join semester sem on sem.SemesterID=s.IntakeSemesterID" _
& " left join SemestersAttended sa on sa.StudentID=s.StudentID" _
& " left join ClassesTaken as ct on ct.semesterid=sa.semesterid" _
& " left join class as c on c.classid =ct.ClassID" _
& " left join program as p on s.program=p.programid" _
& " left join college as cl on cl.collegeid = p.collegeid" _
& " left join appliedtograduate as apg on apg.SemesterID =sem.semesterid" _
& " left join sex as sx on sx.sexcode=s.sex" _
& " where(s.StudentStatus Is null)" _
& " and (sem.startdate <= getdate() and sem.EndDate >= getdate())"
While dr3.Read()
conn4.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
'Dim dr2 As SqlDataReader
Dim sql2 As String
sql2 = dr3("lookup")
com4.CommandText = sql2
com4.Connection = conn4
conn4.Open()
dr4 = com4.ExecuteReader
While dr4.Read
For Each ctrl As Control In Panel1.Controls
Dim chk As New CheckBox
'If dr3("method") = chk.ID Then
'End If
chk.ID = "cb" & " " & dr3("method") & " " & dr4.Item(0).ToString
Dim check As CheckBox = DirectCast(Panel1.FindControl("cb" & " " & dr3("method") & " " & dr4.Item(0).ToString), CheckBox)
If check.Checked = True Then
Dim s1 As String = "cb" & " " & dr3("method") & " " & dr4.Item(0).ToString
Dim index As Integer
index = s1.LastIndexOf(" ")
Dim s2 As String = s1.Substring((index + 1))
s1 = s1.Substring(0, index)
index = s1.LastIndexOf(" ")
Dim s3 As String = s1.Substring((index + 1))
s1 = s1.Substring(0, index)
index = s1.LastIndexOf(" ")
If s3 = dr3("method") Then
conn5.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim sqlmain2 As String
sqlmain2 = "select filter from emailstudent where method = '" & dr3("method") & "'"
'MsgBox(dr3("method"))
com5.CommandText = sqlmain2
com5.Connection = conn5
conn5.Open()
Dim filter As String = com5.ExecuteScalar.ToString
'Dim campusfilter As String = filter & "(" & s2 & ") "
'MsgBox(campusfilter)
sqlmain = sqlmain & "(" & filter & " + @s2 + "
MsgBox(sqlmain)
conn5.Close()
End If
Exit For
End If
Next
End While
'*********************************************************************************************************
dr4.Close()
conn4.Close()
End While
conn3.Close()
dr3.Close()
conn5.Open()
Dim group As String = "group by s.studentid,s.name,s.email,sa.semestercode"
sqlmain = sqlmain & " " & group & " "
MsgBox(sqlmain)
com5.CommandText = sqlmain
com5.Connection = conn5
dr = com5.ExecuteReader
Dim email As New StringBuilder
If dr.HasRows = True Then
While dr.Read()
email.Append(dr.Item("email"))
email.Append(", ")
End While
End If
If email.Length > 0 Then
Dim emailstring As String = email.ToString.Remove(email.Length - 2, 2)
TextBox1.Text = emailstring
Panel1.Visible = False
MultiView1.SetActiveView(View1)
End If
Session("query") = sqlmain.ToString
'MsgBox(sqlmain)
conn5.Close()
End Sub
Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim conn As New SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
conn.Open()
Dim com As New SqlCommand
Dim dt As New DataTable
Dim sqlmain As String = Session("query")
com.Connection = conn
com.CommandText = sqlmain
da.SelectCommand = com
da.Fill(ds, "studentlist")
dt = ds.Tables("studentlist")
'MsgBox(sqlmain)
Dim Client As New SmtpClient() 'This constructor will set the SMTP mail server's address and its port
'Dim textbox3 As TextBox
'Client.DeliveryMethod = SmtpDeliveryMethod.PickupDirectoryFromIis
'Client.DeliveryMethod = SmtpDeliveryMethod.PickupDirectoryFromIis
'Build Email Message
Dim email As New MailMessage
'Dim emailsender As String = Server.HtmlEncode(DropDownList1.SelectedValue)
email.Subject = Server.HtmlEncode(TextBox2.Text)
email.IsBodyHtml = True
'TextBox3.Text = DropDownList1.SelectedValue
email.From = New MailAddress(DropDownList1.SelectedValue)
email.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure
email.DeliveryNotificationOptions = DeliveryNotificationOptions.OnSuccess
email.Body = Server.HtmlEncode(FreeTextBox1.Text)
Dim emailaddress As String = Server.HtmlEncode(TextBox1.Text)
email.To.Add(emailaddress)
'For i As Int32 = 0 To dt.Rows.Count - 1
' Dim emailaddress As String = Server.HtmlEncode(RTrim(dt.Rows(i).Item(2).ToString))
' email.To.Add(New MailAddress(emailaddress))
' If emailaddress <> "" Then
' email.To.Clear()
' 'Client.Send(email)
' End If
'Next
'MsgBox(freetextbox)
'Send Email
Client.Send(email)
'Label2.Visible = True
''Next
''Else
Client.Send(email)
'emailmessage.Text = "Email inform that applicant has been offered will be sent if the applicant has been offered"
'End If
Label1.Visible = True
conn.Close()
End Sub
End Class
Last edited:
