Ok, I inherited some code written in vb that is part of a web application.
My overall objective is to be able to take multiple names from a "LastName"
text box and use those names in my SQL query against my database. Currently
the way it is coded, the text box will pass one name only to the next page,
which then gets formed into the SQL query. I will provide some examples of
the code that is used to perform this task...
Ok, when you put in a last name, it is assigned to the sLastname variable
with the following code:
Dim sLastname As String = Trim(Request.Form("txtLastname"))
If Len(sLastname) 0 Then
sURL += "LastName=" & sLastname & "&"
End If
The html behind that form is as follows:
<td>Last Name</td>
<td><textarea style="font-family: Verdana, Arial, Helvetica,
sans-serif; color: #000000;" name="txtLastName" rows=3 cols=17
id="txtLastName"></textarea></td>
</tr>
After everything is evaluated, the corresponing URL is built with the
following code:
sURL = "Students.aspx?" & sURL
Response.Redirect(sURL)
So for an example, if I put in the last Name Washington, the final URL after
you hit the Search button would look like:
sURL "Students.aspx?LastName=Washington" String
The above line is a copy from the visual studio 2003 debugging mode
variable watch for the variable sURL
So now, we would jump over into students.aspx with the ?LastName=Washington
appendix
The first couple of lines in the Students.aspx.vb page are as follows:
Dim iQueryStringStart As Integer = Request.RawUrl.IndexOf("?")
Dim sQueryString As String = ""
If iQueryStringStart 0 Then
sQueryString = Request.RawUrl.Substring(iQueryStringStart)
End If
If Request.Form.Count = 0 Then
Dim sNickName As String =
UCase(Trim(Request.QueryString("NickName")))
Dim sFirstName As String =
UCase(Trim(Request.QueryString("FirstName")))
Dim sLastname As String =
UCase(Trim(Request.QueryString("Lastname")))
then later on down the page
sBody = GetBody(sNickName, _
sFirstName, _
sLastname, _
sMatricDate, _
sSourceCode, _
sDegreeProgram, _
sCitizenship, _
sCity, _
sState, _
sCountry, _
sPhDAreaOfStudy, _
sApplComp, _
sApplResponse, _
sStudentStatus, _
sCondCalculus, _
sOrientationFee, _
sEmbarkApp, _
sSpecialAccept, _
sGAResident, _
sAsstRequested, _
sAsstOffered, _
sLocalCity, _
sLocalState, _
bPosted, _
sQueryString)
Then to jump into the GetBody function...
Function GetBody(ByVal sNickName As String, _
ByVal sFirstName As String, _
ByVal sLastname As String, _
ByVal sMatricDate As String, _
ByVal sSourceCode As String, _
ByVal sDegreeProgram As String, _
ByVal sCitizenship As String, _
ByVal sCity As String, _
ByVal sState As String, _
ByVal sCountry As String, _
ByVal sPhDAreaOfStudy As String, _
ByVal sApplComp As String, _
ByVal sApplResponse As String, _
ByVal sStudentStatus As String, _
ByVal sCondCalculus As String, _
ByVal sOrientationFee As String, _
ByVal sEmbarkApp As String, _
ByVal sSpecialAccept As String, _
ByVal sGAResident As String, _
ByVal sAsstRequested As String, _
ByVal sAsstOffered As String, _
ByVal sLocalCity As String, _
ByVal sLocalState As String, _
ByVal bPosted As Boolean, _
ByVal sQueryString As String)
Dim sRetVal As String = ""
sRetVal += "<table cellpadding=2 cellspacing=0 border=0
width=""100%"">" & vbCr
sRetVal += "<tr>" & vbCr
sRetVal += "<td>" & vbCr
sRetVal += "<form name=frmStudents method=post
action=""Students.aspx" & sQueryString & """>" & vbCr
sRetVal += "<p style=""margin-left:5pt;"">" & vbCr
sRetVal += BTN_BACK & vbCr
sRetVal += "<input type=image name=btnPost alt=""Post""
src=""images/ico_post.gif"">" & vbCr
sRetVal += "<input type=image name=btnDelete alt=""Delete""
src=""images/ico_delete.gif"">" & vbCr
sRetVal += BTN_CANCEL & vbCr
sRetVal += "</p>" & vbCr
Try
Dim cn As OleDbConnection = OpenDatabase(OLEDB_PROVIDER &
gsConnectionString)
Dim sStudentSQLWC As String = GetStudentSQLWC(sNickName, _
sFirstName, _
sLastname, _
sMatricDate, _
sSourceCode, _
sDegreeProgram, _
sCitizenship, _
sCity, _
sState, _
sCountry, _
sPhDAreaOfStudy, _
sApplComp, _
sApplResponse, _
sStudentStatus, _
sCondCalculus, _
sOrientationFee, _
sEmbarkApp, _
sSpecialAccept, _
sGAResident, _
sAsstRequested, _
sAsstOffered, _
sLocalCity, _
sLocalState, _
bPosted)
Dim sSQL As String = ""
sSQL += "SELECT COUNT(*) AS iCnt "
sSQL += "FROM Students "
sSQL += sStudentSQLWC
Dim iCnt As Integer = GetSingleResult(cn, sSQL, "iCnt",
VariantType.Integer)
sSQL = ""
sSQL += "SELECT StudentId, "
sSQL += " Posted, "
sSQL += " LastName, "
sSQL += " FirstName, "
sSQL += " DegreeProgram, "
sSQL += " MatricDate, "
sSQL += " StudentStatus "
sSQL += "FROM Students "
sSQL += sStudentSQLWC
sSQL += "ORDER BY LastName, "
sSQL += " FirstName, "
sSQL += " StudentStatus"
and then to see the GetStudentSQLWC function...
Function GetStudentSQLWC(ByVal sNickName As String, _
ByVal sFirstName As String, _
ByVal sLastname As String, _
ByVal sMatricDate As String, _
ByVal sSourceCode As String, _
ByVal sDegreeProgram As String, _
ByVal sCitizenship As String, _
ByVal sCity As String, _
ByVal sState As String, _
ByVal sCountry As String, _
ByVal sPhDAreaOfStudy As String, _
ByVal sApplComp As String, _
ByVal sApplResponse As String, _
ByVal sStudentStatus As String, _
ByVal sCondCalculus As String, _
ByVal sOrientationFee As String, _
ByVal sEmbarkApp As String, _
ByVal sSpecialAccept As String, _
ByVal sGAResident As String, _
ByVal sAsstRequested As String, _
ByVal sAsstOffered As String, _
ByVal sLocalCity As String, _
ByVal sLocalState As String, _
ByVal bPosted As Boolean) As String
Dim sRetVal As String = ""
sRetVal += SQLWC("NickName", sNickName, VariantType.String)
sRetVal += SQLWC("FirstName", sFirstName, VariantType.String)
sRetVal += SQLWC("Lastname", sLastname, VariantType.String)
sRetVal += SQLWC("MatricDate", sMatricDate, VariantType.String)
sRetVal += SQLWC("SourceCode", sSourceCode, VariantType.String)
sRetVal += SQLWC("DegreeProgram", sDegreeProgram, VariantType.String)
sRetVal += SQLWC("Citizenship", sCitizenship, VariantType.String)
sRetVal += SQLWC("City", sCity, VariantType.String)
sRetVal += SQLWC("State", sState, VariantType.String)
sRetVal += SQLWC("Country", sCountry, VariantType.String)
sRetVal += SQLWC("PhDAreaOfStudy", sPhDAreaOfStudy,
VariantType.String)
sRetVal += SQLWC("ApplComp", sApplComp, VariantType.Integer)
sRetVal += SQLWC("ApplResponse", sApplResponse, VariantType.String)
sRetVal += SQLWC("StudentStatus", sStudentStatus, VariantType.String)
sRetVal += SQLWC("CondCalculus", sCondCalculus, VariantType.Integer)
sRetVal += SQLWC("OrientationFee", sOrientationFee,
VariantType.Integer)
sRetVal += SQLWC("EmbarkApp", sEmbarkApp, VariantType.Integer)
sRetVal += SQLWC("SpecialAccept", sSpecialAccept, VariantType.Integer)
sRetVal += SQLWC("GAResident", sGAResident, VariantType.Integer)
sRetVal += SQLWC("AsstRequested", sAsstRequested, VariantType.Integer)
sRetVal += SQLWC("AsstOffered", sAsstOffered, VariantType.Integer)
sRetVal += SQLWC("LocalCity", sLocalCity, VariantType.String)
sRetVal += SQLWC("LocalState", sLocalState, VariantType.String)
sRetVal += SQLWC("Posted", bPosted, VariantType.Boolean)
If Len(sRetVal) 0 Then
'add the " WHERE " clause and trim the final "AND "
sRetVal = " WHERE " & Left(sRetVal, Len(sRetVal) - 4) & " "
End If
Return sRetVal
And the SQLWC function is as follows:
Shared Function SQLWC(ByVal sField As String, _
ByVal oVal As Object, _
ByVal oType As VariantType) As String
Dim sRet As String = ""
Select Case oType
Case vbString
'Add If statement that will evaluate Oval string for Yes and
No
If Len(oVal) 0 Then
'Add If statement that will evaluate Oval string for Yes
and No and convert to 1 or 0
If oVal = "YES" Then
oType = VariantType.Integer
oVal = 1
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
ElseIf oVal = "NO" Then
oType = VariantType.Integer
oVal = 0
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
'Added this elseif to search for different country
fields in alumni search
ElseIf sField = "WorkCountry" Or sField = "LocalCountry"
Or sField = "HomeCountry" Then
sRet = " UPPER(" & sField & ") LIKE " & SQLSTR(oVal
& "%") & " OR "
Else
sRet = " UPPER(" & sField & ") LIKE " & SQLSTR(oVal
& "%") & " AND "
End If
End If
Case vbBoolean
If oVal Then
sRet = " " & sField & " = 1 AND "
End If
Case vbInteger
If IsNumeric(oVal) Then
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
End If
Case vbDate
If IsDate(oVal) Then
sRet = " " & sField & " = " & SQLSTR(oVal) & " AND "
End If
End Select
Return sRet
So hopefully, that is enough of a peek into the Code. I need to figure out
a way, IF I CAN, to work with this code, so that when you enter multiple
lastnames in the Lastname box, it will pass that information as a NAME, or
NAMe into the SQL query that it will build.
I am thinking I need to change the initial box from a string character to
something else... any ideas?
