Do Loop Until Pulling Blank Entry 2

[dlongnecker]

Hello!

I'm trying to build a helper application for me and work on some ASP.NET skills at the same time. I frequently build forms, surveys, etc. in ASP and am trying to avoid the repetition of creating the variable, cleaning up the data, and creating the SQL statement for each project.

I decided to create a ASP.NET application that would upload a plain text file of the form values I used. For example:

Name
Address
City
State
ZIPCode
Phone
Email

From here, it would open a StreamReader and ReadLine() each line into a Do Loop to build my information. However, my problem lies in how the loop works. There's always an empty record at the bottom. While it's not hard to clean up (much faster than what it takes to build all of this), I'd still like to see if I can perfect the program. Here's the code to build the first part of the application.

' Open the Stream and Text File
objStreamReader = File.OpenText(strFileName)
		
' Begin the Do Loop
Do

' Read line of text file.
strFormName = objStreamReader.ReadLine()

' Create Dim line
  Response.Write("Dim str" & strFormName & "<br>")

' Create Request.Form Line (pull data line)
  Response.Write("str" & strFormName & " = Request.Form(&quot;" & strFormName & "&quot;)<br>")

' Create Trim whitespace line
  Response.Write("str" & strFormName & " = Trim(str" & strFormName & ")<br>")

' Create Replace quote line
  Response.Write("str" & strFormName & " = Replace(str" & strFormName & ", &quot;'&quot;, &quot;''&quot;)<br>")

' Line break
  Response.Write("<br>")

' Loop until nothing else shows up.
Loop Until strFormName = ""

' Close stream and clean out variable
objStreamReader.Close()
objStreamReader = Nothing

This works great! It spits out information like:

Dim strName
strName = Request.Form("Name")
strName = Trim(strName)
strName = Replace(strName, "'", "''")

strSQL = "INSERT INTO <TABLENAME> (strName,strAddress,strCity,strState,strZIPCode,strPhone,strFax,strAge,strGender,strIncome,strEthnic,strEducation,str,) VALUES ("
strSQL = strSQL & "'" & strName & "'"
strSQL = strSQL & ", "
strSQL = strSQL & ");"

However, the final entry, rather, the "", shows up.

Dim str
str = Request.Form("")
str = Trim(str)
str = Replace(str, "'", "''")
strSQL = strSQL & "'" & str & "'"
strSQL = strSQL & ", "
strSQL = strSQL & ");"

Is there another way to write the loop statement? Perhaps a better way than ReadLine()'ing the text file. I wanted to keep it simple so I could put this out to some of the other users in our organization...

Ideas, suggestions?

Thanks in advance!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[jzelhart]

Try to check for "" before executing the loop.

Do Until strFormName = ""

Loop

Hope everyone is having a great day!

Thanks - Jennifer

 

[dlongnecker]

Jennifer-

I tried that and for some reason, it just dies at the beginning and doesn't output anything. I'll give it another shot (it's been a long day... I could just be missing something), and post what I come up with.

Thanks!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[jzelhart]

Put the strFormName = objStreamReader.ReadLine() prior to the Do as well as inside the do.

At the time you run the do it is not set.


Hope everyone is having a great day!

Thanks - Jennifer

 

[dlongnecker]

Jennifer-

I modified it to read:

objStreamReader = File.OpenText(strFileName)
strFormName = objStreamReader.ReadLine()
Do Until strFormName = ""
strFormName = objStreamReader.ReadLine()
  Response.Write("Dim str" & strFormName & "<br>")
  Response.Write("str" & strFormName & " = Request.Form(&quot;" & strFormName & "&quot;)<br>")
  Response.Write("str" & strFormName & " = Trim(str" & strFormName & ")<br>")
  Response.Write("str" & strFormName & " = Replace(str" & strFormName & ", &quot;'&quot;, &quot;''&quot;)<br>")
  Response.Write("<br>")
Loop
objStreamReader.Close()
objStreamReader = Nothing

It does run; however, still tosses in the blank record.

I changed it from 'Do Until strFormName = ""' to "@" and added a "@" at the end of the document... then it just outputs:

Dim str@
str@ = Request.Form("@")
str@ = Trim(str@)
str@ = Replace(str@, "'", "''")

So, I thought I might "outsmart" it... *laugh*... I ended the last line with, in this case, Address@. Well, that really upset ASP.NET. Looking at the error log on my web server:

aspnet_wp.exe (PID: 4072) was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM).

For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

Yeah, guessing that was an overflow. Oops!

-David

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[jzelhart]

Try this to cheat it...

Do Until (strFormName & "~") = "~"

It might work?


Hope everyone is having a great day!

Thanks - Jennifer

 

[treyball3]

using your original code, try putting

if strFormName = "" then exit do

right after

strFormName = objStreamReader.ReadLine()

and at the end change

Loop Until strFormName = ""

to just say Loop

 

[cappmgr]

another approach

strFormName = objStreamReader.ReadLine()
While Not strFormName Is Nothing
  Response.Write("Dim str" & strFormName & "<br>")
  Response.Write("str" & strFormName & " = Request.Form(&quot;" & strFormName & "&quot;)<br>")
  Response.Write("str" & strFormName & " = Trim(str" & strFormName & ")<br>")
  Response.Write("str" & strFormName & " = Replace(str" & strFormName & ", &quot;'&quot;, &quot;''&quot;)<br>")
  Response.Write("<br>")
  strFormName = objStreamReader.ReadLine()
End While
objStreamReader.Close()

Marty

 

[dlongnecker]

Okay! Success... sorta!

Marty's example seemed to be the one that fit the bill. It removed the blank entry that was appearing. Here's the code for the entire program:

<%@ Import Namespace="System.IO" %>
<html>
	<head>
<script language="vb" runat="server">
Sub btnUploadFile_OnClick(sender as Object, e as EventArgs)
		' ~~~~~ OPENER INFORMATION ~~~~~

		Dim strDir As String
		Dim strFileName as String
		Dim strFormName As String
		Dim objStreamReader as StreamReader
		
		strDir = "c:\inetpub\[URL unfurl="true"]wwwroot\dotnet\"[/URL]
		strFileName = Path.GetFileName(txtFile.PostedFile.FileName)
		txtFile.PostedFile.SaveAs(strDir & strFileName)
		strFileName = strDir & strFileName


		response.write("<code>")
		response.write("Information<br><br>")
    	
		' ~~~~~ BUILD SECTION 1 INFORMATION ~~~~~
		
		objStreamReader = File.OpenText(strFileName)
		
		strFormName = objStreamReader.ReadLine()
		While Not strFormName Is Nothing
			Response.Write("Dim str" & strFormName & "<br>")
			Response.Write("str" & strFormName & " = Request.Form(&quot;" & strFormName & "&quot;)<br>")
			Response.Write("str" & strFormName & " = Trim(str" & strFormName & ")<br>")
			Response.Write("str" & strFormName & " = Replace(str" & strFormName & ", &quot;'&quot;, &quot;''&quot;)<br>")
			Response.Write("<br>")
			strFormName = objStreamReader.ReadLine()
		End While
		objStreamReader.Close()

		objStreamReader = Nothing

		' ~~~~~ BUILD SECTION 2 INFORMATION ~~~~~
		
		dim strBuildSQL as String
		dim strBuildValues as String
		strBuildSQL = "strSQL = &quot;INSERT INTO &lt;TABLENAME&gt; ("
		strBuildValues = ""
		Dim strFormName1 as string

	    	objStreamReader = File.OpenText(strFileName)
	
		strFormName1 = objStreamReader.ReadLine()
		While Not strFormName1 Is Nothing
			strBuildValues = strBuildValues & "str" & strFormName1
			strFormName1 = objStreamReader.ReadLine()
			strBuildValues = strBuildValues & ","
		End While
		objStreamReader.Close()
	
		objStreamReader = Nothing
		
		strBuildSQL = strBuildSQL & strBuildValues & ") VALUES (&quot;"

		' ~~~~~ BUILD SECTION 3 INFORMATION ~~~~~

		objStreamReader = File.OpenText(strFileName)

		dim strFormName2 as string

		strFormName2 = objStreamReader.ReadLine()
		While Not strFormName2 Is Nothing
			strBuildSQL = strBuildSQL & "<br>strSQL = strSQL & &quot;'&quot; & str" & strFormName2 & " & &quot;'&quot;"
			strFormName2 = objStreamReader.ReadLine()
			strBuildSQL = strBuildSQL & "<br>strSQL = strSQL & &quot;, &quot;"
		End While

		objStreamReader.Close()

	    	strBuildSQL = strBuildSQL & "<br>strSQL = strSQL & &quot;);&quot;"
	
		response.write (strBuildSQL)
	
		response.write ("</code>")

End Sub

</script>
		<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
	</head>
	<body>
		<form id="FileUploadSample" enctype="multipart/form-data" method="post" runat="server">
			<p>Select the file to upload:</p>
			<p><input type="file" id="txtFile" runat="server"></p>
			<p><asp:button id="btnUploadFile" runat="server" text="Upload The File" onclick="btnUploadFile_OnClick" /></p>
		</form>
	</body>
</html>

Here's the output. My "stuff.txt" file contained:

Name
Address
City
State
ZIPCode
Phone

Output:

Information

Dim strName
strName = Request.Form("Name")
strName = Trim(strName)
strName = Replace(strName, "'", "''")

Dim strAddress
strAddress = Request.Form("Address")
strAddress = Trim(strAddress)
strAddress = Replace(strAddress, "'", "''")

Dim strCity
strCity = Request.Form("City")
strCity = Trim(strCity)
strCity = Replace(strCity, "'", "''")

Dim strState
strState = Request.Form("State")
strState = Trim(strState)
strState = Replace(strState, "'", "''")

Dim strZIPCode
strZIPCode = Request.Form("ZIPCode")
strZIPCode = Trim(strZIPCode)
strZIPCode = Replace(strZIPCode, "'", "''")

Dim strPhone
strPhone = Request.Form("Phone")
strPhone = Trim(strPhone)
strPhone = Replace(strPhone, "'", "''")

strSQL = "INSERT INTO <TABLENAME> (strName,strAddress,strCity,strState,strZIPCode,strPhone,) VALUES ("
strSQL = strSQL & "'" & strName & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strAddress & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strCity & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strState & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strZIPCode & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strPhone & "'"
strSQL = strSQL & ", "
strSQL = strSQL & ");"

The only thing that it has a problem with now is building the SQL statement. As you can see, there's the final ", " statement for each section... while a comma and a line is VERY easy to remove... still something I'll keep plugging away at. For the most part, it's turned out to make form ASP code generation a snap... and saves a lot of typing time!

Thanks to everyone for your help! It's greatly appreciated!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[cappmgr]

dlongnecker,
If the extra comma is coming from strBuildValues you can remove it with the remove metod of string. You might want to explore using a StringBuilder at sometime.

strBuildValues = strBuildValues & ","
End While
strBuildValues.Remove(strBuildValues - 1, 1)
objStreamReader.Close()

Marty

 

[cappmgr]

sorry that should be
strBuildValues.Remove(strBuildValues.Length - 1, 1)

Marty

 

[dlongnecker]

Ahhh! Wow.. that's nifty... Yeah, in my research, I've come across the StringBuilder, but nothing more... I'll look it up and try somethings out. Thanks for the advice!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[dlongnecker]

Hmmm... possible that it's just me and my lack of understanding, but to get it to work, I had to write it:

strBuildValues = strBuildValues.Remove(strBuildValues.Length - 1, 1)

Thanks!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259