Question Helppp!!! How to split parameterized queries in code behind?

MissZy

New member
Joined
Oct 5, 2010
Messages
3
Programming Experience
1-3
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 :
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:

MissZy

New member
Joined
Oct 5, 2010
Messages
3
Programming Experience
1-3
and im very sure the problems if from here :

sqlmain = sqlmain & "(" & filter & " + @s2 + "

but i do not know how to split the value if i choose the method BYCAMPUS = 1 and 2
 
Top Bottom