vb to sql server

[JimMuglia]

Folks,

I need to know why my code below locks up on the line

rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic

and the program says "Not Responding" in the task bar. Please help!

My boss is in another state and needs me to send him afix withinan hour. Heellp!!!!

Thank you,
Jim

Dim rst As New ADODB.Recordset
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Driver={SQL Server};Server=SQL;Database=" & Trim(lblDatabase.Caption) & ";Uid=sa;Pwd=;"

cnn.Open

sourcesqlstatement = "Select PartName from Part where PartName <> ''"

rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic

If rst.RecordCount > 0 Then
rst.MoveFirst
While Not rst.EOF
Value = rst.Fields("PartName")
If Value = partname Then
dbAdd = False
End If
rst.MoveNext
Wend
End If
rst.Close

[jhermiz]

sourcesqlstatement = "Select PartName from Part where PartName IS NOT NULL"

Jon

[radum]

first of all, i hope it's not your boss trying to use this program from wherever he is... logging in over the internet with username=sa and no password is a huge security breach.

but anyways, sounds like your connection works and it's just the recordset that doesnt get populated. question: how many records do you have in the database? it may be that there are too many, and the server times out...

[radum]

Originally posted by radum
question: how many records do you have in the database? it may be that there are too many, and the server times out...

disregard this... you would get a timeout error eventually...

[jhermiz]

If you're using SQL Server..why are you using dynamic sql right in the front end. This should be processed on the server via a stored procedure. Your front end code should be:

Dim objCmd As ADODB.Command
 
Call EstablishConnection
Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient
 
    With objCmd
        .ActiveConnection = objConn
        .CommandText = "your_sproc_name"       'our stored procedure
        .CommandType = adCmdStoredProc         'its a stored procedure
        Set objRS = .Execute
        Set objRS.ActiveConnection = Nothing
        Call ReleaseConnection
    End With
    
    If objRS.BOF Then
        'no records?
       
        MsgBox "Your search returned no results!",   vbExclamation, "Record Not Found!"
 
    Else
        If objRS.EOF Then
            MsgBox "Your search returned no results!", vbExclamation, "Record Not Found!"
            Unload frmMain
        Else
              While Not objRS.EOF 
                         Value = objRS.Fields("PartName") 
                                   If Value = partname Then 
                                             dbAdd = False 
                                   End If 
                         objRS.movenext
        End If
    End If
 
Done:
Set objCmd = Nothing
Exit Sub
 
Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done

Your sproc should look like this:

CREATE PROCEDURE sproc_name_here
AS
BEGIN
SET NOCOUNT ON
Select PartName from Part where PartName IS NOT NULL
Set NOCOUNT OFF
End
GO

Later,Jon

[radum]

try this piece of code (it only attempts to select 10 records and skips past the test)

Dim rst As New ADODB.Recordset 
Set cnn = CreateObject("ADODB.Connection") 
cnn.ConnectionString = "Driver={SQL Server};Server=SQL;Database=" & Trim(lblDatabase.Caption) & ";Uid=sa;Pwd=;" 
 
cnn.Open 
 
sourcesqlstatement = "Select TOP 10 PartName from Part where PartName <> ''" 
 
On Error Resume Next
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic 
If Err Then
  Debug.Print "Error " & CStr(Err.Number) & ": " & Err.Description
Else
  rst.MoveFirst 
  While Not rst.EOF 
    Value = rst.Fields("PartName") 
    If Value = partname Then 
      dbAdd = False 
    End If 
    rst.MoveNext 
  Wend
  rst.Close
End If

[radum]

oh jeez... just figured... what youre trying to do here is to set dbAdd = False if you already have a given partname in the database... it's actually a lot easier than this, you dont have to go thru all the table sequentially to accomplish that:

Dim rst As New ADODB.Recordset 
Set cnn = CreateObject("ADODB.Connection") 
cnn.ConnectionString = "Driver={SQL Server};Server=SQL;Database=" & Trim(lblDatabase.Caption) & ";Uid=sa;Pwd=;" 
cnn.Open 
 
sourcesqlstatement = "SELECT COUNT(PartName) AS isPartThere FROM Part WHERE PartName = '" & partname & "'" 
rst.Open sourcesqlstatement, cnn, adOpenStatic, adLockOptimistic 
dbAdd = rst("isPartThere") = 0
rst.Close

and no, you dont *have to* use a stored procedure for this... it's an option, but it wont make things faster.

[JimMuglia]

There's 31 records in the Part database. When I do a MsgBox to indicate the record count, the MsgBox is blank. Its like the table is not even being read.

Why would there not be 31 parts counted. And then it stays in this loop indifinitely and stops responding.


If rst.RecordCount > 0 Then
rst.MoveFirst
While Not rst.EOF
Value = rst.Fields("PartName")
If Value = partname Then
dbAdd = False
End If
rst.MoveNext
Wend
End If
rst.Close

What is so strange is that I have no problems at all with this code when putting the data into Access.

Please help. My reputation, what's left of it, is on the line!

Jim

[radum]

the .RecordCount property is not set unless you go to the last record in the recordset. i'm affraid that nothing gets actually executed...

[JimMuglia]

Originally posted by radum
the .RecordCount property is not set unless you go to the last record in the recordset. i'm affraid that nothing gets actually executed...

It does seem like nothing is getting executed. What do you think I should do about it, based on your looking at my code? Anybody?
Jim

[radum]

run it step by step in debug mode. put a breakpoint on the rst.Open statement and see if that thing ever returns the control to your application. check the Err object after that. and so on... or simply try to replace your code with the code in my 4th or 5th post, the really tiny one (you wont regret it afterwards, especially when the database will grow bigger).

the infinite loop effect that youre getting may be caused by another part of your code, because what you posted doesnt seem to have a problem. worst thing that can happen if the server doesnt return a result set (which is something that i doubt) is a timeout error.

[jhermiz]

what's wrong with the solution I posted?

Jon