We help IT Professionals succeed at work.
Get Started
I have an error that states, "Length cannot be less then zero. Parameter name: length:"
I do not understand what the problem is in my Visual Basic code but I am trying to make a StreamWriter work and for some reason it is not. I get this error and no text gets written to the text file. See code below:
Sub WriteText()
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
'Dim dr As New SqlDataReader()
Dim dr As SqlDataReader '= myCommand.ExecuteReader()
Dim sb As New System.Text.StringBuilder
Dim output As String = "C:\Users\BRSmith\Desktop\workfolder\testrun\testfiles\outputfile.txt"
Dim writer As New System.IO.StreamWriter(output)
'declaring the objects
'dr.
myConnection = New SqlConnection("server=BRSMITH-PC\SQL2005;uid=brs0903;pwd=UltimaGTR08;database=automationdb")
'establishing connection. you need to provide password for sql server
Try
myConnection.Open()
'opening the connection
myCommand = New SqlCommand("Select * from outputquery", myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
'reading from the datareader
Dim fullname As String
Dim fname As String
Dim lname As String
Dim address1 As String
Dim address2 As String
Dim city1 As String
Dim state1 As String
Dim zip1 As String
Dim phone1 As String
Dim empphone1 As String
Dim birthdatemonth As String
Dim birthdateday As String
Dim birthdateyear As String
Dim birthdatestring As String
Dim birthdate1 As String
Dim patientno As Integer
Dim statusinfo As String
Dim socialsecno As String
Dim guarname As String
Dim guarfname As String
Dim guarlastname As String
Dim guaraddress As String
Dim guaraddress2 As String
Dim guarcity As String
Dim guarstate As String
Dim guarzip As String
Dim guarphone As String
Dim admitdate As String
Dim admitdatestring As String
Dim admitdateday As String
Dim admitdatemonth As String
Dim admitdateyear As String
Dim dischargedate As String
Dim dischargedatestring As String
Dim dischargedateday As String
Dim dischargedatemonth As String
Dim dischargedateyear As String
Dim insurcovplan As String
Dim subscriberaddress As String
Dim subscribercity As String
Dim subscriberstate As String
Dim subscriberzip As String
Dim insurpolno As String
Dim insurgrpno As String
Dim insurcoplancode As String
Dim insurcoplandesc As String
Dim insursubscriberaddress As String
Dim insursubscribercity As String
Dim insursubscriberstate As String
Dim insursubscriberzip As String
Dim insurpolno2 As String
Dim insurgrpno2 As String
Dim insurplancode2 As String
Dim insurcovplandesc3 As String
Dim insursubscriberaddress3 As String
Dim insursubscribercity3 As String
Dim insursubscriberstate3 As String
Dim insursubscriberzip3 As String
Dim insurpolno4 As String
Dim insurgrpno4 As String
Dim insurcovplancode4 As String
Dim insurocplandesc4 As String
Dim insursubscriberaddress4 As String
Dim insursubscribercity4 As String
Dim insursubscriberstate4 As String
Dim insursubscriberzip4 As String
Dim insurpolno5 As String
Dim insurgrpno5 As String
Dim insurcovplancode5 As String
Dim atndrname As String
Dim medrecno As String
Dim cmstreatauthno As String
Dim finclass As String
Dim patsex As String
Dim patssno As String
Dim atndrno As String
Dim patdobstring As String
Dim patdobmonth As String
Dim patdobday As String
Dim patdobyear As String
Dim patdob As String
Dim guarsex As String
Dim dischargedatestringfinal As String
Dim dischargedatemonthfinal As String
Dim dischargedatedayfinal As String
Dim dischargedateyearfinal As String
Dim dischargedatefinal As String
sb.Length = 0 ' clear the stringbuilder
fullname = Trim(dr(0).ToString()).PadRight(25, " ")
fname = fullname.Substring(fullname.IndexOf(",") + 1, Len(fullname) - (fullname.IndexOf(",") + 1)).Trim
lname = fullname.Substring(0, fullname.IndexOf(",")).Trim
address1 = Trim(dr(1).ToString()).PadRight(22, " ")
address2 = Mid(Trim(dr(2).ToString()).PadRight(15, " "), 1, 15)
city1 = Trim(dr(3).ToString()).PadRight(15, " ")
state1 = Trim(dr(4).ToString()).PadRight(2, " ")
zip1 = Trim(dr(5).ToString()).PadRight(10, " ")
phone1 = Trim(dr(6).ToString()).PadRight(10, " ")
empphone1 = Trim(dr(7).ToString()).PadRight(10, " ")
If Not (IsDBNull(dr(8))) Then 'birthdate - test to see if there are any null values
birthdatestring = (Trim(dr(8).ToString()).PadRight(6, " "))
birthdatemonth = Convert.ToDateTime(birthdatestring).ToString("MM")
birthdateday = Convert.ToDateTime(birthdatestring).ToString("dd")
birthdateyear = Convert.ToDateTime(birthdatestring).ToString("yy")
birthdate1 = birthdatemonth + birthdateday + birthdateyear
End If
patientno = Trim(dr(9).ToString()).PadRight(11, " ")
statusinfo = Trim(dr(10).ToString()).PadRight(28, " ")
socialsecno = Trim(dr(11).ToString()).PadRight(11, " ")
guarname = Trim(dr(12).ToString()).PadRight(25, " ")
guarfname = guarname.Substring(guarname.IndexOf(",") + 1, Len(guarname) - (guarname.IndexOf(",") + 1)).Trim
guarlastname = guarname.Substring(0, guarname.IndexOf(",")).Trim
guaraddress = Trim(dr(13).ToString()).PadRight(22, " ")
guaraddress2 = Trim(dr(14).ToString()).PadRight(15, " ")
guarcity = Trim(dr(15).ToString()).PadRight(15, " ")
guarstate = Trim(dr(16).ToString()).PadRight(2, " ")
guarzip = Trim(dr(17).ToString()).PadRight(10, " ")
guarphone = Trim(dr(18).ToString()).PadRight(10, " ")
If Not (IsDBNull(dr(19))) Then 'birthdate - test to see if there are any null values
admitdatestring = (Trim(dr(19).ToString()).PadRight(6, " "))
admitdatemonth = Convert.ToDateTime(admitdatestring).ToString("MM")
admitdateday = Convert.ToDateTime(admitdatestring).ToString("dd")
admitdateyear = Convert.ToDateTime(admitdatestring).ToString("yy")
admitdate = admitdatemonth + admitdateday + admitdateyear
End If
If Not (IsDBNull(dr(20))) Then 'birthdate - test to see if there are any null values
dischargedatestring = (Trim(dr(20).ToString()).PadRight(6, " "))
dischargedatemonth = Convert.ToDateTime(dischargedatestring).ToString("MM")
dischargedateday = Convert.ToDateTime(dischargedatestring).ToString("dd")
dischargedateyear = Convert.ToDateTime(dischargedatestring).ToString("yy")
dischargedate = dischargedatemonth + dischargedateday + dischargedateyear
End If
insurcovplan = Trim(dr(21).ToString()).PadRight(20, " ")
subscriberaddress = Trim(dr(22).ToString()).PadRight(20, " ")
subscribercity = Trim(dr(23).ToString()).PadRight(15, " ")
subscriberstate = Trim(dr(24).ToString()).PadRight(2, " ")
subscriberzip = Trim(dr(25).ToString()).PadRight(10, " ")
insurpolno = Trim(dr(26).ToString()).PadRight(14, " ")
insurgrpno = Trim(dr(27).ToString()).PadRight(14, " ")
insurcoplancode = Trim(dr(28).ToString()).PadRight(5, " ")
insurcoplandesc = Trim(dr(29).ToString()).PadRight(20, " ")
insursubscriberaddress = Trim(dr(30).ToString()).PadRight(20, " ")
insursubscribercity = Trim(dr(31).ToString()).PadRight(15, " ")
insursubscriberstate = Trim(dr(32).ToString()).PadRight(2, " ")
insursubscriberzip = Trim(dr(33).ToString()).PadRight(10, " ")
insurpolno2 = Trim(dr(34).ToString()).PadRight(14, " ")
insurgrpno2 = Trim(dr(35).ToString()).PadRight(14, " ")
insurplancode2 = Trim(dr(36).ToString()).PadRight(5, " ")
insurcovplandesc3 = Trim(dr(37).ToString()).PadRight(20, " ")
insursubscriberaddress3 = Trim(dr(38).ToString()).PadRight(20, " ")
insursubscribercity3 = Trim(dr(39).ToString()).PadRight(15, " ")
insursubscriberstate3 = Trim(dr(40).ToString()).PadRight(2, " ")
insursubscriberzip3 = Trim(dr(41).ToString()).PadRight(10, " ")
insurpolno4 = Trim(dr(42).ToString()).PadRight(14, " ")
insurgrpno4 = Trim(dr(43).ToString()).PadRight(14, " ")
insurcovplancode4 = Trim(dr(44).ToString()).PadRight(5, " ")
insurocplandesc4 = Trim(dr(45).ToString()).PadRight(20, " ")
insursubscriberaddress4 = Trim(dr(46).ToString()).PadRight(20, " ")
insursubscribercity4 = Trim(dr(47).ToString()).PadRight(15, " ")
insursubscriberstate4 = Trim(dr(48).ToString()).PadRight(2, " ")
insursubscriberzip4 = Trim(dr(49).ToString()).PadRight(10, " ")
insurpolno5 = Trim(dr(50).ToString()).PadRight(14, " ")
insurgrpno5 = Trim(dr(51).ToString()).PadRight(14, " ")
insurcovplancode5 = Trim(dr(52).ToString()).PadRight(5, " ")
atndrname = Trim(dr(53).ToString()).PadRight(25, " ")
medrecno = Trim(dr(54).ToString()).PadRight(12, " ")
cmstreatauthno = Trim(dr(55).ToString()).PadRight(15, " ")
finclass = Trim(dr(56).ToString()).PadRight(1, " ")
patsex = Trim(dr(57).ToString()).PadRight(1, " ")
patssno = Trim(dr(58).ToString()).PadRight(9, " ")
atndrno = Trim(dr(59).ToString()).PadRight(6, " ")
If Not (IsDBNull(dr(60))) Then 'birthdate - test to see if there are any null values
patdobstring = (Trim(dr(60).ToString()).PadRight(6, " "))
patdobmonth = Convert.ToDateTime(patdobstring).ToString("MM")
patdobday = Convert.ToDateTime(patdobstring).ToString("dd")
patdobyear = Convert.ToDateTime(patdobstring).ToString("yy")
patdob = patdobmonth + patdobday + patdobyear
End If
guarsex = Trim(dr(61).ToString()).PadRight(1, " ")
If Not (IsDBNull(dr(62))) Then 'birthdate - test to see if there are any null values
dischargedatestringfinal = (Trim(dr(62).ToString()).PadRight(6, " "))
patdobmonth = Convert.ToDateTime(dischargedatestringfinal).ToString("MM")
patdobday = Convert.ToDateTime(dischargedatestringfinal).ToString("dd")
patdobyear = Convert.ToDateTime(dischargedatestringfinal).ToString("yy")
dischargedatefinal = dischargedatemonthfinal + dischargedatedayfinal + dischargedateyearfinal
End If
writer.Write("{0,25}", (fname & " " & lname).ToString().PadRight(25, " "))
' writer.Write("{0,22}", (address1).ToString().PadRight(22, " "))
' writer.Write("{0,15}", (address2).ToString().PadRight(15, " "))
' writer.Write("{0,15}", (city1).ToString().PadRight(15, " "))
' writer.Write("{0,2}", (state1).ToString().PadRight(2, " "))
' writer.Write("{0,10}", (zip1).ToString().PadRight(10, " "))
' writer.Write("{0,10}", (phone1).ToString().PadRight(10, " "))
' writer.Write("{0,10}", (empphone1).ToString().PadRight(10, " "))
'writer.Write("{0,6}", (birthdate1).ToString().PadRight(6, " "))
'writer.Write("{0,11}", (patientno).ToString().PadRight(11, " "))
'writer.Write("{0,28}", (statusinfo).ToString().PadRight(28, " "))
'writer.Write("{0,11}", (socialsecno).ToString().PadRight(11, " "))
'writer.Write("{0,24}", (guarfname & " " & guarlastname).ToString().PadRight(25, " "))
'writer.Write("{0,22}", (guaraddress).ToString().PadRight(22, " "))
'writer.Write("{0,15}", (guaraddress2).ToString().PadRight(15, " "))
'writer.Write("{0,15}", (guarcity).ToString().PadRight(15, " "))
'writer.Write("{0,2}", (guarstate).ToString().PadRight(2, " "))
'writer.Write("{0,10}", (guarzip).ToString().PadRight(10, " "))
'writer.Write("{0,10}", (guarphone).ToString().PadRight(10, " "))
' writer.Write("{0,6}", (admitdate).ToString().PadRight(6, " "))
' writer.Write("{0,6}", (dischargedate).ToString().PadRight(6, " "))
' writer.Write("{0,20}", (insurcovplan).ToString().PadRight(20, " "))
' writer.Write("{0,20}", (subscriberaddress).ToString().PadRight(20, " "))
' writer.Write("{0,15}", (subscribercity).ToString().PadRight(15, " "))
' writer.Write("{0,2}", (subscriberstate).ToString().PadRight(2, " "))
' writer.Write("{0,10}", (subscriberzip).ToString().PadRight(10, " "))
' writer.Write("{0,14}", (insurpolno).ToString().PadRight(14, " "))
' writer.Write("{0,14}", (insurgrpno).ToString().PadRight(14, " "))
' writer.Write("{0,5}", (insurcoplancode).ToString().PadRight(5, " "))
' writer.Write("{0,20}", (insurcoplandesc).ToString().PadRight(20, " "))
' writer.Write("{0,20}", (insursubscriberaddress).ToString().PadRight(20, " "))
'writer.Write("{0,15}", (insursubscribercity).ToString().PadRight(15, " "))
' writer.Write("{0,2}", (insursubscriberstate).ToString().PadRight(2, " "))
'writer.Write("{0,10}", (insursubscriberzip).ToString().PadRight(10, " "))
' writer.Write("{0,14}", (insurpolno2).ToString().PadRight(14, " "))
'writer.Write("{0,14}", (insurgrpno2).ToString().PadRight(14, " "))
' writer.Write("{0,5}", (insurplancode2).ToString().PadRight(5, " "))
' writer.Write("{0,20}", (insurcovplandesc3).ToString().PadRight(20, " "))
' writer.Write("{0,20}", (insursubscriberaddress3).ToString().PadRight(20, " "))
'writer.Write("{0,15}", (insursubscribercity3).ToString().PadRight(15, " "))
' writer.Write("{0,2}", (insursubscriberstate3).ToString().PadRight(2, " "))
' writer.Write("{0,10}", (insursubscriberzip).ToString().PadRight(10, " "))
'writer.Write("{0,14}", (insurpolno4).ToString().PadRight(14, " "))
' writer.Write("{0,14}", (insurgrpno4).ToString().PadRight(14, " "))
'writer.Write("{0,5}", (insurcovplancode4).ToString().PadRight(5, " "))
'writer.Write("{0,20}", (insurocplandesc4).ToString().PadRight(20, " "))
'writer.Write("{0,20}", (insursubscriberaddress4).ToString().PadRight(20, " "))
'writer.Write("{0,15}", (insursubscribercity4).ToString().PadRight(15, " "))
' writer.Write("{0,2}", (insursubscriberstate4).ToString().PadRight(2, " "))
' writer.Write("{0,10}", (insursubscriberzip4).ToString().PadRight(10, " "))
' writer.Write("[0,14}", (insurpolno5).ToString().PadRight(14, " "))
' writer.Write("[0,14}", (insurgrpno5).ToString().PadRight(14, " "))
' writer.Write("[0,5}", (insurcovplancode5).ToString().PadRight(5, " "))
'writer.Write("[0,25}", (atndrname).ToString().PadRight(25, " "))
'writer.Write("[0,12}", (medrecno).ToString().PadRight(12, " "))
'writer.Write("[0,15}", (cmstreatauthno).ToString().PadRight(15, " "))
'writer.Write("[0,1}", (finclass).ToString().PadRight(1, " "))
'writer.Write("[0,1}", (patsex).ToString().PadRight(1, " "))
'writer.Write("[0,9}", (patssno).ToString().PadRight(9, " "))
'writer.Write("[0,6}", (atndrno).ToString().PadRight(6, " "))
'writer.Write("[0,6}", (patdob).ToString().PadRight(6, " "))
'writer.Write("[0,1}", (guarsex).ToString().PadRight(1, " "))
'writer.Write("[0,6}", (dischargedatefinal).ToString().PadRight(6, " "))
writer.WriteLine(sb.ToString)
End While
dr.Close()
myConnection.Close()
writer.Close()
Catch e As Exception
End Try
End Sub
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE