使用VB6 连接SQL Server，出现很多TIME_WAIT，然后软件会没有响应了。

Question

Private Sub Command3_Click()
    Timer.Enabled = False
    Start = False
    Set Conn = New ADODB.Connection
    'Set Rs = New ADODB.Recordset
        Conn.ConnectionString = "Driver={sql server};server=" + strSQLServerName + ";uid=" + strSQLDBUserName + ";pwd=" + strSQLDBPassword + ";database=" + strSQLDBName
    Conn.ConnectionTimeout = 10
    Conn.Open
    'On Error Resume Next
    Conn.Execute "Insert Into List_tohtml (ASPAddress,HtmlAddress) VALUES ('Default.asp','Default.Htm')"
    Set Rs = Conn.Execute("SELECT * FROM List_MainType Where Not DocumentName Is Null")
    Do While Not Rs.EOF And Not Rs.BOF
        Conn.Execute "Insert Into List_tohtml (ASPAddress,HtmlAddress) VALUES ('" + Rs("DocumentName") + "/Default.asp','" + Rs("DocumentName") + "/Default.Htm')"
    Rs.MoveNext
    Loop
    Set Rs = Nothing    Set Rs = Conn.Execute("SELECT Film.VideoID, List_MainType.DocumentName FROM Film INNER JOIN List_MainType ON Film.MainType = List_MainType.MainTypeID")
    Do While Not Rs.EOF And Not Rs.BOF
        Conn.Execute "Insert Into List_tohtml (ASPAddress,HtmlAddress) VALUES ('" + Rs("DocumentName") + "/showdata/show.asp?videoid=" + CStr(Rs("VideoID")) + "','" + Rs("DocumentName") + "/showdata/" + CStr(Rs("VideoID")) + ".html')"
        Rs.MoveNext
    Loop
    Set Rs = Nothing
    Conn.Close
    Set Conn = Nothing
End Sub这个代码，每循环一次，就会留下一次TIME_WAIT，循环多了以后，软件就挂了。请教各位大虾，如何可以解决？是代码问题吗？

Answer 1

用Netstat -N 查出现故障入下:
  TCP    192.168.13.50:2008     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2009     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2010     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2011     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2012     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2013     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2014     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2015     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2016     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2017     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2019     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2020     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2021     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2022     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2023     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2024     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2025     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2026     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2027     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2028     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2029     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2030     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2031     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2032     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2033     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2034     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2035     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2036     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2037     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2038     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2039     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2040     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2041     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2042     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2043     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2044     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2045     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2046     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2047     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2048     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2049     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2050     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2051     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2052     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2053     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2056     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2057     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2058     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2059     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2060     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2061     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2062     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2063     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2064     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2065     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2066     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2067     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2068     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2069     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2070     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2071     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2072     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2073     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2074     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2075     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2076     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2077     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2078     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2079     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2080     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2081     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2082     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2083     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2084     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2085     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2086     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2087     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2088     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2089     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2090     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2091     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2092     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2093     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2094     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2095     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2096     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2097     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2098     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2099     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2100     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2101     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2102     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2103     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2104     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2105     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2106     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2107     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2108     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2109     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2110     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2111     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2112     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2113     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2114     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2115     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2116     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2119     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2120     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2121     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2122     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2123     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2124     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2125     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2126     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2127     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2128     218.218.218.218:1433     TIME_WAIT
  TCP    192.168.13.50:2129     218.218.218.218:1433     TIME_WAIT

Answer 2

你在do之前就把连接关掉，然后在do里面再重新conn.open试试吧,偶前些日子好象刚弄过这样的问题

Answer 3

在释放记录集对象前，先关闭记录集试试
......
If Rs.State=adStateOpen Then Rs.Close
Set Rs = Nothing
............
If Rs.State=adStateOpen Then Rs.Close
Set Rs = Nothing
......

Answer 4

还是不行，用了rs.close还是不行。

Answer 5

SELECT * FROM 
把*号换成所有要选的字段名，不使用星号

Answer 6

你用Rs.GetRows把rs的内容全转存到数组,然后用数组循环rst=rs.getrows
  for i =0 to ubound(rst,2)
    Conn.Open ...
        Conn.Execute "Insert Into List_tohtml (ASPAddress,HtmlAddress) VALUES ('" + Rs("DocumentName") + "/Default.asp','" + Rs("DocumentName") + "/Default.Htm')"
conn.close
    ...
    next

Answer 7

supergreenbean正解。但是没写对具体用法。rst=rs.getrows
  for i =0 to ubound(rst,2)
    Conn.Open ...
        Conn.Execute "Insert Into List_tohtml (ASPAddress,HtmlAddress) VALUES ('" + Cstr(Rst(1,i)) + "/Default.asp','" + Cstr(Rst(1,i)) + "/Default.Htm')"
conn.close
    ...
    next