Error Connecting to SQL Server 7

[darcey]

Hi guys, hope you can help me out on this. Sorry if this is the wrong group
to post to. I was not sure if I should post to here or the SQL group. I
did post to the SQL group as well but i have not had a response in a couple
of days so I thought maybe it was the wrong group. But my problem is that
when I am trying to connect to SQL Server I am getting the error:

-2147467259 - [DBMSSOCN]General network error. Check your network documentation.


Now, I do not get the error on the same line of code all the time. It can
be on a different line, in a different function, and I do not get it when
I step through the code, and sometimes it works perfectly. It generally
gives the error in one of the functions *after* I open a different form and
execute a stored procedure that returns records. I have several functions
that return values retrieved from SQL Server. These functions are located
at the module level. Here is a sample of what one of the functions looks
like:

Function Get_Product_FBMCONV(ByVal pID As Long) As Double
On Error GoTo errH
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
Trusted_Connection=No;UID=sa;PWD;", adOpenStatic, adLockReadOnly,
adCmdText
Set rs.ActiveConnection = Nothing

If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV = rs!FBMCONV
rs.Close
Set rs = Nothing
errH:
If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
Next Else Resume
End Function

I am a little baffled by the error because I am pretty sure there is not
a problem with the network because I can open any other form and establish
a connection with no problem.

Any help/suggestions appreciated.

Thanks,

Darcey

[jy]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi guys, hope you can help me out on this. Sorry if this is the wrong group
>to post to. I was not sure if I should post to here or the SQL group.
I
>did post to the SQL group as well but i have not had a response in a couple
>of days so I thought maybe it was the wrong group. But my problem is that
>when I am trying to connect to SQL Server I am getting the error:
>
>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>
>
>Now, I do not get the error on the same line of code all the time. It can
>be on a different line, in a different function, and I do not get it when
>I step through the code, and sometimes it works perfectly. It generally
>gives the error in one of the functions *after* I open a different form
and
>execute a stored procedure that returns records. I have several functions
>that return values retrieved from SQL Server. These functions are located
>at the module level. Here is a sample of what one of the functions looks
>like:
>
>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
> On Error GoTo errH
> Dim rs As ADODB.Recordset
>
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseClient
> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic, adLockReadOnly,
>adCmdText
> Set rs.ActiveConnection = Nothing
>
> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV = rs!FBMCONV
> rs.Close
> Set rs = Nothing
>errH:
> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>Next Else Resume
>End Function
>
>I am a little baffled by the error because I am pretty sure there is not
>a problem with the network because I can open any other form and establish
>a connection with no problem.
>
>Any help/suggestions appreciated.
>
>Thanks,
>
>Darcey
check your password declaration in the connection string; it should read
pwd=<password>

you need the '=' symbol even if the password is blank.

hope this helps. j-y.

[darcey]

Hi j-y,

thanks a lot for the suggestion, but i am still receiving that error. Just
so you know a little more i am using VB 6 SP4 , mdac 2.5, TCP/IP. I only
get the error *after* i open another form and execute a stored procedure
that returns two recordsets *and* if i leave the form open. Here is the code
that executes the stored procedure:

Private Sub New_inq_2()

On Error GoTo errH

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

If Update_Combo_Boxes(Me) = False Then Exit Sub

Screen.MousePointer = vbHourglass

DoEvents

Dim Cmd As ADODB.Command

Set Cmd = New ADODB.Command

Cmd.CommandType = adCmdStoredProc

If chkNonZero.Value = 0 Then
Cmd.CommandText = "sp_INVENTORY_INQUIRY_NO_ZERO"
Else
Cmd.CommandText = "sp_INVENTORY_INQUIRY_INCLUDE_ZERO"
End If


With Cmd
.Parameters.Append .CreateParameter("@AppComp", adTinyInt, adParamInput,
, AppComp)
.Parameters.Append .CreateParameter("@CustID", adVarChar, adParamInput,
15, CustomerID)
.Parameters.Append .CreateParameter("@OrderNo", adVarChar, adParamInput,
8, OrderNum)
.Parameters.Append .CreateParameter("@InvGrp", adVarChar, adParamInput,
15, InvGroup)
.Parameters.Append .CreateParameter("@Height", adVarChar, adParamInput,
15, Hght)
.Parameters.Append .CreateParameter("@Width", adVarChar, adParamInput,
15, Wdth)
.Parameters.Append .CreateParameter("@Length", adVarChar, adParamInput,
15, Length)
.Parameters.Append .CreateParameter("@Grade", adVarChar, adParamInput,
15, Grade)
.Parameters.Append .CreateParameter("@Species", adVarChar, adParamInput,
15, Species)
.Parameters.Append .CreateParameter("@State", adVarChar, adParamInput,
15, InvState)
.Parameters.Append .CreateParameter("@Milling", adVarChar, adParamInput,
15, Milling)
.Parameters.Append .CreateParameter("@Infeed", adVarChar, adParamInput,
15, Infeed)
.Parameters.Append .CreateParameter("@FBM", adBoolean, adParamInput,
, FBM)
.Parameters.Append .CreateParameter("@Print", adBoolean, adParamInput,
, 0)
.Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput,
15, Trim(UCase(UserName)))
End With

Set cn = Open_Connection

Set Cmd.ActiveConnection = cn

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open Cmd, , adOpenStatic, adLockReadOnly, adCmdStoredProc

Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs2 = rs.NextRecordset

Set rs.ActiveConnection = Nothing
Set rs2.ActiveConnection = Nothing
Set Cmd.ActiveConnection = Nothing

Close_Connection cn

Set Adodc1.Recordset = rs
Set DbGrid.DataSource = Adodc1

With DbGrid
.ClearFields
.ReBind
.RowHeight = 250
.Columns(0).Visible = False 'ID
.Columns(0).AllowSizing = False
.Columns(1).AllowSizing = False
.Columns(2).AllowSizing = False
.Columns(4).Visible = True
.Columns(1).Width = 3000
.Columns(2).Width = 850
For lp = 3 To 7
.Columns(lp).Width = 1160
.Columns(lp).AllowSizing = False
.Columns(lp).Alignment = 1
.Columns(lp).NumberFormat = "#########0"
Next lp
If SecLev = 5 Then
.Columns(8).Visible = False
Else
.Columns(8).Width = 1420
End If
.Columns(8).Alignment = 1
.Columns(8).NumberFormat = "Currency"
.Columns(8).AllowSizing = False
If AppComp = 2 Then .Columns(8).NumberFormat = " "
.Columns(3).Alignment = 1
End With

DoEvents

lblTot(0) = Format(rs2!AVAIL, "########0") ' available
lblTot(1) = Format(rs2!YARD, "########0") 'yard
lblTot(2) = Format(rs2![COMMITTED], "########0") 'sales
lblTot(4) = Format(rs2!ONORDER, "########0") 'Purchase orders
lblTot(5) = Format(rs2!REMAN, "########0") ' reman
Set rs = Nothing
Set rs2 = Nothing
Set Cmd = Nothing
Set cn = Nothing
Screen.MousePointer = vbDefault

errH:
If Err <> 0 Then If ErrP("INVInquiry.btnView.Click", Err) = 0 Then Resume
Next Else Resume
End Sub

incidently the form that returns that error after i execute the above code
opens and closes connections as needed (approx. 50 times). Is this a good
idea or should I leave a connection open while the form is displayed ? The
users frequently will leave this form open for long periods of time with
no activity (couple of hours).

Thanks a lot for any help/suggestions.

Darcey




"jy" <jy@directdialog.com> wrote:
>
>"darcey" <dbrown@bayridgelbr.com> wrote:
>>
>>Hi guys, hope you can help me out on this. Sorry if this is the wrong
group
>>to post to. I was not sure if I should post to here or the SQL group.

>I
>>did post to the SQL group as well but i have not had a response in a couple
>>of days so I thought maybe it was the wrong group. But my problem is that
>>when I am trying to connect to SQL Server I am getting the error:
>>
>>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>>
>>
>>Now, I do not get the error on the same line of code all the time. It
can
>>be on a different line, in a different function, and I do not get it when
>>I step through the code, and sometimes it works perfectly. It generally
>>gives the error in one of the functions *after* I open a different form
>and
>>execute a stored procedure that returns records. I have several functions
>>that return values retrieved from SQL Server. These functions are located
>>at the module level. Here is a sample of what one of the functions looks
>>like:
>>
>>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
>> On Error GoTo errH
>> Dim rs As ADODB.Recordset
>>
>> Set rs = New ADODB.Recordset
>> rs.CursorLocation = adUseClient
>> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
>> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic,
adLockReadOnly,
>>adCmdText
>> Set rs.ActiveConnection = Nothing
>>
>> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV = rs!FBMCONV
>> rs.Close
>> Set rs = Nothing
>>errH:
>> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>>Next Else Resume
>>End Function
>>
>>I am a little baffled by the error because I am pretty sure there is not
>>a problem with the network because I can open any other form and establish
>>a connection with no problem.
>>
>>Any help/suggestions appreciated.
>>
>>Thanks,
>>
>>Darcey
>check your password declaration in the connection string; it should read
>pwd=<password>
>
>you need the '=' symbol even if the password is blank.
>
>hope this helps. j-y.

[dbishop]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi j-y,
>
>thanks a lot for the suggestion, but i am still receiving that error. Just
>so you know a little more i am using VB 6 SP4 , mdac 2.5, TCP/IP. I only
>get the error *after* i open another form and execute a stored procedure
>that returns two recordsets *and* if i leave the form open. Here is the
code
>that executes the stored procedure:
>
>Private Sub New_inq_2()
>
> On Error GoTo errH
>
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
>
> If Update_Combo_Boxes(Me) = False Then Exit Sub
>
> Screen.MousePointer = vbHourglass
>
> DoEvents
>
> Dim Cmd As ADODB.Command
>
> Set Cmd = New ADODB.Command
>
> Cmd.CommandType = adCmdStoredProc
>
> If chkNonZero.Value = 0 Then
> Cmd.CommandText = "sp_INVENTORY_INQUIRY_NO_ZERO"
> Else
> Cmd.CommandText = "sp_INVENTORY_INQUIRY_INCLUDE_ZERO"
> End If
>
>
> With Cmd
> .Parameters.Append .CreateParameter("@AppComp", adTinyInt, adParamInput,
>, AppComp)
> .Parameters.Append .CreateParameter("@CustID", adVarChar, adParamInput,
>15, CustomerID)
> .Parameters.Append .CreateParameter("@OrderNo", adVarChar, adParamInput,
>8, OrderNum)
> .Parameters.Append .CreateParameter("@InvGrp", adVarChar, adParamInput,
>15, InvGroup)
> .Parameters.Append .CreateParameter("@Height", adVarChar, adParamInput,
>15, Hght)
> .Parameters.Append .CreateParameter("@Width", adVarChar, adParamInput,
>15, Wdth)
> .Parameters.Append .CreateParameter("@Length", adVarChar, adParamInput,
>15, Length)
> .Parameters.Append .CreateParameter("@Grade", adVarChar, adParamInput,
>15, Grade)
> .Parameters.Append .CreateParameter("@Species", adVarChar, adParamInput,
>15, Species)
> .Parameters.Append .CreateParameter("@State", adVarChar, adParamInput,
>15, InvState)
> .Parameters.Append .CreateParameter("@Milling", adVarChar, adParamInput,
>15, Milling)
> .Parameters.Append .CreateParameter("@Infeed", adVarChar, adParamInput,
>15, Infeed)
> .Parameters.Append .CreateParameter("@FBM", adBoolean, adParamInput,
>, FBM)
> .Parameters.Append .CreateParameter("@Print", adBoolean, adParamInput,
>, 0)
> .Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput,
>15, Trim(UCase(UserName)))
> End With
>
> Set cn = Open_Connection
>
> Set Cmd.ActiveConnection = cn
>
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseClient
> rs.Open Cmd, , adOpenStatic, adLockReadOnly, adCmdStoredProc
>
> Dim rs2 As ADODB.Recordset
> Set rs2 = New ADODB.Recordset
> Set rs2 = rs.NextRecordset
>
> Set rs.ActiveConnection = Nothing
> Set rs2.ActiveConnection = Nothing
> Set Cmd.ActiveConnection = Nothing
>
> Close_Connection cn
>
> Set Adodc1.Recordset = rs
> Set DbGrid.DataSource = Adodc1
>
> With DbGrid
> .ClearFields
> .ReBind
> .RowHeight = 250
> .Columns(0).Visible = False 'ID
> .Columns(0).AllowSizing = False
> .Columns(1).AllowSizing = False
> .Columns(2).AllowSizing = False
> .Columns(4).Visible = True
> .Columns(1).Width = 3000
> .Columns(2).Width = 850
> For lp = 3 To 7
> .Columns(lp).Width = 1160
> .Columns(lp).AllowSizing = False
> .Columns(lp).Alignment = 1
> .Columns(lp).NumberFormat = "#########0"
> Next lp
> If SecLev = 5 Then
> .Columns(8).Visible = False
> Else
> .Columns(8).Width = 1420
> End If
> .Columns(8).Alignment = 1
> .Columns(8).NumberFormat = "Currency"
> .Columns(8).AllowSizing = False
> If AppComp = 2 Then .Columns(8).NumberFormat = " "
> .Columns(3).Alignment = 1
> End With
>
> DoEvents
>
> lblTot(0) = Format(rs2!AVAIL, "########0") ' available
> lblTot(1) = Format(rs2!YARD, "########0") 'yard
> lblTot(2) = Format(rs2![COMMITTED], "########0") 'sales
> lblTot(4) = Format(rs2!ONORDER, "########0") 'Purchase orders
> lblTot(5) = Format(rs2!REMAN, "########0") ' reman
> Set rs = Nothing
> Set rs2 = Nothing
> Set Cmd = Nothing
> Set cn = Nothing
> Screen.MousePointer = vbDefault
>
>errH:
> If Err <> 0 Then If ErrP("INVInquiry.btnView.Click", Err) = 0 Then Resume
>Next Else Resume
>End Sub
>
>incidently the form that returns that error after i execute the above code
>opens and closes connections as needed (approx. 50 times). Is this a good
>idea or should I leave a connection open while the form is displayed ?
The
>users frequently will leave this form open for long periods of time with
>no activity (couple of hours).
>
>Thanks a lot for any help/suggestions.
>
>Darcey
>
>
>
>
>"jy" <jy@directdialog.com> wrote:
>>
>>"darcey" <dbrown@bayridgelbr.com> wrote:
>>>
>>>Hi guys, hope you can help me out on this. Sorry if this is the wrong
>group
>>>to post to. I was not sure if I should post to here or the SQL group.
>
>>I
>>>did post to the SQL group as well but i have not had a response in a couple
>>>of days so I thought maybe it was the wrong group. But my problem is
that
>>>when I am trying to connect to SQL Server I am getting the error:
>>>
>>>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>>>
>>>
>>>Now, I do not get the error on the same line of code all the time. It
>can
>>>be on a different line, in a different function, and I do not get it when
>>>I step through the code, and sometimes it works perfectly. It generally
>>>gives the error in one of the functions *after* I open a different form
>>and
>>>execute a stored procedure that returns records. I have several functions
>>>that return values retrieved from SQL Server. These functions are located
>>>at the module level. Here is a sample of what one of the functions looks
>>>like:
>>>
>>>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
>>> On Error GoTo errH
>>> Dim rs As ADODB.Recordset
>>>
>>> Set rs = New ADODB.Recordset
>>> rs.CursorLocation = adUseClient
>>> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>>>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
>>> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic,

>adLockReadOnly,
>>>adCmdText
>>> Set rs.ActiveConnection = Nothing
>>>
>>> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV =
rs!FBMCONV
>>> rs.Close
>>> Set rs = Nothing
>>>errH:
>>> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>>>Next Else Resume
>>>End Function
>>>
>>>I am a little baffled by the error because I am pretty sure there is not
>>>a problem with the network because I can open any other form and establish
>>>a connection with no problem.
>>>
>>>Any help/suggestions appreciated.
>>>
>>>Thanks,
>>>
>>>Darcey
>>check your password declaration in the connection string; it should read
>>pwd=<password>
>>
>>you need the '=' symbol even if the password is blank.
>>
>>hope this helps. j-y.
>
When words and phrases like "not every time" and "intermittent" and "sometimes"
are used to describe and issue, it's often hard to pinpoint the source.

I've read this thread and it sounds to me like a timing issue. I think SQL
Server is probably trying to process each of your requests as soon as it
can as quickly as you ask for them but it's failing at some point.

You said something like, "if I step through the code, it works just fine."
That tells me that the code is fine. It's beyond that. It's got to have something
to do with the speed of the requests, the order of the requests, when SQL
Server can process them and what happens if it doesn't process them fast
enough.

By stalling the processing, even for a moment or two, when stepping the code
in debug mode, you're probably giving the SQL Server just enough time to
accomplish that which you've asked and therefore don't experience the error.


I know this flies in the face of everything you know and hold near and dear
to your heart as a professional VB programmer... but... I would suggest trying
a few DoEvents statements strategically placed in your code to give the system
time to process your requests. _IF_ that resolves the problem, at least you
know what you're up against and you may be able to approach the delivery
of your requests in a different sequence or... whatever, just to give the
system time to complete your requests. Then again, if it doesn't help, this
is a tough problem being "intermittent" and "sometimes", etc.

Good luck and I hope this helps you find a solution at least.

dbishop

[dbishop]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi j-y,
>
>thanks a lot for the suggestion, but i am still receiving that error. Just
>so you know a little more i am using VB 6 SP4 , mdac 2.5, TCP/IP. I only
>get the error *after* i open another form and execute a stored procedure
>that returns two recordsets *and* if i leave the form open. Here is the
code
>that executes the stored procedure:
>
>Private Sub New_inq_2()
>
> On Error GoTo errH
>
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
>
> If Update_Combo_Boxes(Me) = False Then Exit Sub
>
> Screen.MousePointer = vbHourglass
>
> DoEvents
>
> Dim Cmd As ADODB.Command
>
> Set Cmd = New ADODB.Command
>
> Cmd.CommandType = adCmdStoredProc
>
> If chkNonZero.Value = 0 Then
> Cmd.CommandText = "sp_INVENTORY_INQUIRY_NO_ZERO"
> Else
> Cmd.CommandText = "sp_INVENTORY_INQUIRY_INCLUDE_ZERO"
> End If
>
>
> With Cmd
> .Parameters.Append .CreateParameter("@AppComp", adTinyInt, adParamInput,
>, AppComp)
> .Parameters.Append .CreateParameter("@CustID", adVarChar, adParamInput,
>15, CustomerID)
> .Parameters.Append .CreateParameter("@OrderNo", adVarChar, adParamInput,
>8, OrderNum)
> .Parameters.Append .CreateParameter("@InvGrp", adVarChar, adParamInput,
>15, InvGroup)
> .Parameters.Append .CreateParameter("@Height", adVarChar, adParamInput,
>15, Hght)
> .Parameters.Append .CreateParameter("@Width", adVarChar, adParamInput,
>15, Wdth)
> .Parameters.Append .CreateParameter("@Length", adVarChar, adParamInput,
>15, Length)
> .Parameters.Append .CreateParameter("@Grade", adVarChar, adParamInput,
>15, Grade)
> .Parameters.Append .CreateParameter("@Species", adVarChar, adParamInput,
>15, Species)
> .Parameters.Append .CreateParameter("@State", adVarChar, adParamInput,
>15, InvState)
> .Parameters.Append .CreateParameter("@Milling", adVarChar, adParamInput,
>15, Milling)
> .Parameters.Append .CreateParameter("@Infeed", adVarChar, adParamInput,
>15, Infeed)
> .Parameters.Append .CreateParameter("@FBM", adBoolean, adParamInput,
>, FBM)
> .Parameters.Append .CreateParameter("@Print", adBoolean, adParamInput,
>, 0)
> .Parameters.Append .CreateParameter("@UserID", adVarChar, adParamInput,
>15, Trim(UCase(UserName)))
> End With
>
> Set cn = Open_Connection
>
> Set Cmd.ActiveConnection = cn
>
> Set rs = New ADODB.Recordset
> rs.CursorLocation = adUseClient
> rs.Open Cmd, , adOpenStatic, adLockReadOnly, adCmdStoredProc
>
> Dim rs2 As ADODB.Recordset
> Set rs2 = New ADODB.Recordset
> Set rs2 = rs.NextRecordset
>
> Set rs.ActiveConnection = Nothing
> Set rs2.ActiveConnection = Nothing
> Set Cmd.ActiveConnection = Nothing
>
> Close_Connection cn
>
> Set Adodc1.Recordset = rs
> Set DbGrid.DataSource = Adodc1
>
> With DbGrid
> .ClearFields
> .ReBind
> .RowHeight = 250
> .Columns(0).Visible = False 'ID
> .Columns(0).AllowSizing = False
> .Columns(1).AllowSizing = False
> .Columns(2).AllowSizing = False
> .Columns(4).Visible = True
> .Columns(1).Width = 3000
> .Columns(2).Width = 850
> For lp = 3 To 7
> .Columns(lp).Width = 1160
> .Columns(lp).AllowSizing = False
> .Columns(lp).Alignment = 1
> .Columns(lp).NumberFormat = "#########0"
> Next lp
> If SecLev = 5 Then
> .Columns(8).Visible = False
> Else
> .Columns(8).Width = 1420
> End If
> .Columns(8).Alignment = 1
> .Columns(8).NumberFormat = "Currency"
> .Columns(8).AllowSizing = False
> If AppComp = 2 Then .Columns(8).NumberFormat = " "
> .Columns(3).Alignment = 1
> End With
>
> DoEvents
>
> lblTot(0) = Format(rs2!AVAIL, "########0") ' available
> lblTot(1) = Format(rs2!YARD, "########0") 'yard
> lblTot(2) = Format(rs2![COMMITTED], "########0") 'sales
> lblTot(4) = Format(rs2!ONORDER, "########0") 'Purchase orders
> lblTot(5) = Format(rs2!REMAN, "########0") ' reman
> Set rs = Nothing
> Set rs2 = Nothing
> Set Cmd = Nothing
> Set cn = Nothing
> Screen.MousePointer = vbDefault
>
>errH:
> If Err <> 0 Then If ErrP("INVInquiry.btnView.Click", Err) = 0 Then Resume
>Next Else Resume
>End Sub
>
>incidently the form that returns that error after i execute the above code
>opens and closes connections as needed (approx. 50 times). Is this a good
>idea or should I leave a connection open while the form is displayed ?
The
>users frequently will leave this form open for long periods of time with
>no activity (couple of hours).
>
>Thanks a lot for any help/suggestions.
>
>Darcey
>
>
>
>
>"jy" <jy@directdialog.com> wrote:
>>
>>"darcey" <dbrown@bayridgelbr.com> wrote:
>>>
>>>Hi guys, hope you can help me out on this. Sorry if this is the wrong
>group
>>>to post to. I was not sure if I should post to here or the SQL group.
>
>>I
>>>did post to the SQL group as well but i have not had a response in a couple
>>>of days so I thought maybe it was the wrong group. But my problem is
that
>>>when I am trying to connect to SQL Server I am getting the error:
>>>
>>>-2147467259 - [DBMSSOCN]General network error. Check your network documentation.
>>>
>>>
>>>Now, I do not get the error on the same line of code all the time. It
>can
>>>be on a different line, in a different function, and I do not get it when
>>>I step through the code, and sometimes it works perfectly. It generally
>>>gives the error in one of the functions *after* I open a different form
>>and
>>>execute a stored procedure that returns records. I have several functions
>>>that return values retrieved from SQL Server. These functions are located
>>>at the module level. Here is a sample of what one of the functions looks
>>>like:
>>>
>>>Function Get_Product_FBMCONV(ByVal pID As Long) As Double
>>> On Error GoTo errH
>>> Dim rs As ADODB.Recordset
>>>
>>> Set rs = New ADODB.Recordset
>>> rs.CursorLocation = adUseClient
>>> rs.Open "Select [FBMCONV] from INVENTORY_MASTER where [ID]
>>>= " & pID, "Provider=SQLOLEDB;Server=TestServer;Database=BayridgeTest;
>>> Trusted_Connection=No;UID=sa;PWD;", adOpenStatic,

>adLockReadOnly,
>>>adCmdText
>>> Set rs.ActiveConnection = Nothing
>>>
>>> If rs.EOF Then Get_Product_FBMCONV = 0 Else Get_Product_FBMCONV =
rs!FBMCONV
>>> rs.Close
>>> Set rs = Nothing
>>>errH:
>>> If Err <> 0 Then If ErrP("Get_Product_FBMCONV", Err) = 0 Then Resume
>>>Next Else Resume
>>>End Function
>>>
>>>I am a little baffled by the error because I am pretty sure there is not
>>>a problem with the network because I can open any other form and establish
>>>a connection with no problem.
>>>
>>>Any help/suggestions appreciated.
>>>
>>>Thanks,
>>>
>>>Darcey
>>check your password declaration in the connection string; it should read
>>pwd=<password>
>>
>>you need the '=' symbol even if the password is blank.
>>
>>hope this helps. j-y.
>
When words and phrases like "not every time" and "intermittent" and "sometimes"
are used to describe and issue, it's often hard to pinpoint the source.

I've read this thread and it sounds to me like a timing issue. I think SQL
Server is probably trying to process each of your requests as soon as it
can as quickly as you ask for them but it's failing at some point.

You said something like, "if I step through the code, it works just fine."
That tells me that the code is fine. It's beyond that. It's got to have something
to do with the speed of the requests, the order of the requests, when SQL
Server can process them and what happens if it doesn't process them fast
enough.

By stalling the processing, even for a moment or two, when stepping the code
in debug mode, you're probably giving the SQL Server just enough time to
accomplish that which you've asked and therefore don't experience the error.


I know this will fly in the face of everything you know and hold near and
dear to your heart as a professional VB programmer... but... I would suggest
trying a few DoEvents statements strategically placed in your code to give
the system time to process your requests. _IF_ that resolves the problem,
at least you know what you're up against and you may be able to approach
the delivery of your requests in a different sequence or... whatever, just
to give the system time to complete your requests. Then again, if it doesn't
help, this is a tough problem being "intermittent" and "sometimes", etc.

Good luck and I hope this helps you find a solution at least.

dbishop

[darcey]

Hi dbishop,

Thanks a lot for the suggestion. I will give that a try.

Darcey

[darcey]

Hi dbishop,

Thanks a lot for the suggestion. I will give it a try.

Darcey

[darcey]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi dbishop,
>
>Thanks a lot for the suggestion. I will give it a try.
>
>Darcey

Hi dbishop,

I tried your suggestion, but I am still receiving the error. The wierd thing
is that I have to open the one form and execute the stored procdure called
from the program and after that I get the error. If I close the form then
it works fine. If I open a different form that establishes a connection
it works fine. I will keep looking. Thanks for the suggestion.

Any other suggestions greatly appreciated.

Thanks,

Darcey

[dbishop]

Sounds like (and I think I remember reading something about this
somewhere...) the Command object doesn't allow changes to the commandtext
property once it's established.

My next suggestion would be to do something like:

Dim cnn As New ADODB.Connection
Dim cmd As ADODB.Command ' don't use the NEW keyword here
With cnn
.ConnectionString = MyConnectionString ' whatever yours is
.ConnectionTimeout = 30
.Open
End With
Set cmd = CreateObject("ADODB", "Connection")
With cmd
.CommandText = "usp_GetRecordsFromTable" ' sproc or SQL statement
.CommandType = adCmdStoredProc ' ADO constant, specifies the command type

.ActiveConnection = cnn ' specifies the connection
.Parameters.Append .CreateParameter("@Parm1", adVarChar, adParamInput,
10,
strValueString)
Set rs = .Execute ' return data
End With
Set cmd = Nothing ' right here, I think this might fix the issue you're
having.
Set cmd = CreateObject("ADODB", "Connection")
' and continue just as you did above with additional commands

<end of code>

I think if you release the object reference, then re-establish it, that may

get you the results you want. My reasoning is, you told me if you fire your

code from a DIFFERENT form, you're cool. That's probably because the command

object in the other form is local to that form and a completely different

object. What do you think? I'm hoping I'm helping and not hindering you.


By the way, I tried emailing you directly at your email address below and
I got a mail daemon. Is it correct? I tried because posting to this message
board takes FOREVER!!!! I click post and wait FOREVER!!!!

dbishop

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>"darcey" <dbrown@bayridgelbr.com> wrote:
>>
>>Hi dbishop,
>>
>>Thanks a lot for the suggestion. I will give it a try.
>>
>>Darcey
>
>Hi dbishop,
>
>I tried your suggestion, but I am still receiving the error. The wierd
thing
>is that I have to open the one form and execute the stored procdure called
>from the program and after that I get the error. If I close the form then
>it works fine. If I open a different form that establishes a connection
>it works fine. I will keep looking. Thanks for the suggestion.
>
>Any other suggestions greatly appreciated.
>
>Thanks,
>
>Darcey

[dbishop]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>Hi dbishop,
>
>Thanks a lot for the suggestion. I will give that a try.
>
>Darcey

I tried to respond to your direct email and I keep getting a mail daemon.
I sent it twice hoping that something would change between attempts but noooooo.

So, here's what I wrote to you:

This is a good one. I still think it has something to do with timing. I
think that was my first guess with the DoEvents suggestion and now I am
coming back to it.

Reason: if you step the code, it seems to work. If you run the code full

speed, it gives you the network error -- which could be a collision of
packet requests, etc. -- but that's not going to be fruitful trying to run

that one down. The end result of finding out that it IS network congestion

is -- oh, it's network congestion... but no real fix for the problem.

I think you said you tried placing some DoEvents statements behind your
Close method calls, didn't you? That is still my best suggestion.

Now, about the strategy for connection objects and at what scope you should

create them... Well, I can tell you at what scope I create mine and it seems

to work fine. If I am doing some DB work in a form's methods and events,
I
typically isolate a single connection to a series of Subs and Functions that

use the same connection between them. For example. Let's say I have a click

event on a button. The code in that click event is supposed to do some DB

work. What I do is, I tell the click event to call an independent Sub that

handles all the work. That keeps my form code very clean and allows me to

call that independent Sub from other forms if necessary. In the independent

Sub, I establish my connection object. From the Sub, I pass the live
connection to other Subs and Functions that do things and retrieve data for

me, all returning to the original independent Sub. When all the work is
finished for that Sub, I close the connection and set it to Nothing.

No matter how many Subs or Functions I pass my connection object to, I never

get an error or a timeout and I'm error trapping in each of those outside

Subs and Functions -- just to make sure AND to help me isolate my errors
to
the exact Sub or Function of the error's origin.

Nothing I've said here is exotic or earth-shattering. (And I'm sure you're

nodding your head right now.) But when you're doing client/server
interaction, there are so many factors that we VB programmers tend to take

for granted, we say to ourselves, "The resource will be there! Hey! It's

always there!" But sometimes there are conflicts.

My gut feeling about what you're describing is that it is a timing issue.

But hey, here's an idea: Put up your "Locals" window and watch it as you
get
into that second form -- the one that's choking -- and see if you have
multiple connections open at the same time.

I'd sure like to be the hero here and give you the exact answer but I guess

it's pretty late to go for that now, huh?

Let me know how it goes. I'm into this now!

dbishop

[darcey]

Hi dbishop,

I agree with you that it must be a timing issue. I did watch my locals window
and I do have only one connection open at a time according to my app. I
also have been passing an open connection to some of the functions and subs,
which works great. I remember starting to set it up that way everywhere,
but I can not remember why I discarded that idea :-) whoops, maybe I should
have stuck with it. I do have about 400 subs and functions to set up then
though (I am migrating from Access 97 to SQL Server 7 - So I am modifying
a lot of code. Wish I had time to re-architect it, but my boss would never
allow that sort of time - "Just make it work" - Uuugh, I hate doing that
- they do not realize that just creates more problems in the long run, not
to mention they will not gain nearly all the performance benefits from SQL
Server) ;(

Anyway...

What I also noticed was that in SQL Server after running the sp_who stored
procedure was that SQL Server must not release connections right away even
though they are closed in the app. After opening that form and clicking
the button that executs these functions and subs, I would run that sp_who
procedure and there would be up tp 90 'sleeping' connections at one time.
Run the sp_who 30 sec later and there would be the normal few connections
that SQL Server has open for its own use. Now I have been reading a few
threads in the SQL Server group and they were talking about idle connections
and how they do not really take up resources on the server (that was the
general concensus anyway).

So i created a connections class, declared the class object global, created
an instance in my sub main procedure and leave the connection open during
the life of the app and dispose of the object when they exit the app. This
means one permanent connection per app during the life of the app. Guess
what, no error (as you probably know).

Now I was concerned that leaving a connection open all day during the life
of the app would degrade the performance of the server too much, and that
was why i was only going to open the connections as needed. But connecting
takes the most time and there are a lot of buttons and other controls that
the users can click often where the app needs to connect, so I do not think
that this design is very efficient in my case. I also thought about having
one connection per form type of design, but they can easily have 5 - 10 forms
open at once and that would be too many connecitions per workstation. But
there are only 25- 30 users in total using the app (And that will not grow
very quickly - probably will not add 10 in the next 2 - 3 years), so I am
sort of leaning towards one permanant connection per app design - this has
been influenced by our little discussion and the other thread I read in the
SQL Server group. I know passing the connection as you do would solve my
problem, but i still have to deal with the issue of always connecting when
an event occurs that needs data from the server (which is very often - they
do a lot of processing with the app, continually updating and retrieving
data). Some users though will not use the app for several hours, but will
have it open, which is why i was concerned about the permanent connection.

So, to sum it up, I do think the problem was a timing issue that was causes
collisions on the network. And with not very many users connecting to the
server at once I was going to change it to have one permanant connection
per app for the life of the app, since idle connections do not really impact
performace.

What do you think ?

Thanks a lot for all your help/suggestions/ideas. I am the only programmer/developer
here, so it is nice to discuss this with someone else.

Darcey

P.S. It is always nice to be the hero. Sorry about the e-mail and for being
so long winded (almost blue in the face). Our ISP was having problems.

[dbishop]

"darcey" <dbrown@bayridgelbr.com> wrote:
>
>
>Hi dbishop,
>
>I agree with you that it must be a timing issue. I did watch my locals
window
>and I do have only one connection open at a time according to my app. I
>also have been passing an open connection to some of the functions and subs,
>which works great. I remember starting to set it up that way everywhere,
>but I can not remember why I discarded that idea :-) whoops, maybe I should
>have stuck with it. I do have about 400 subs and functions to set up then
>though (I am migrating from Access 97 to SQL Server 7 - So I am modifying
>a lot of code. Wish I had time to re-architect it, but my boss would never
>allow that sort of time - "Just make it work" - Uuugh, I hate doing that
>- they do not realize that just creates more problems in the long run, not
>to mention they will not gain nearly all the performance benefits from SQL
>Server) ;(
>
>Anyway...
>
>What I also noticed was that in SQL Server after running the sp_who stored
>procedure was that SQL Server must not release connections right away even
>though they are closed in the app. After opening that form and clicking
>the button that executs these functions and subs, I would run that sp_who
>procedure and there would be up tp 90 'sleeping' connections at one time.
> Run the sp_who 30 sec later and there would be the normal few connections
>that SQL Server has open for its own use. Now I have been reading a few
>threads in the SQL Server group and they were talking about idle connections
>and how they do not really take up resources on the server (that was the
>general concensus anyway).
>
>So i created a connections class, declared the class object global, created
>an instance in my sub main procedure and leave the connection open during
>the life of the app and dispose of the object when they exit the app. This
>means one permanent connection per app during the life of the app. Guess
>what, no error (as you probably know).
>
>Now I was concerned that leaving a connection open all day during the life
>of the app would degrade the performance of the server too much, and that
>was why i was only going to open the connections as needed. But connecting
>takes the most time and there are a lot of buttons and other controls that
>the users can click often where the app needs to connect, so I do not think
>that this design is very efficient in my case. I also thought about having
>one connection per form type of design, but they can easily have 5 - 10
forms
>open at once and that would be too many connecitions per workstation. But
>there are only 25- 30 users in total using the app (And that will not grow
>very quickly - probably will not add 10 in the next 2 - 3 years), so I am
>sort of leaning towards one permanant connection per app design - this has
>been influenced by our little discussion and the other thread I read in
the
>SQL Server group. I know passing the connection as you do would solve my
>problem, but i still have to deal with the issue of always connecting when
>an event occurs that needs data from the server (which is very often - they
>do a lot of processing with the app, continually updating and retrieving
>data). Some users though will not use the app for several hours, but will
>have it open, which is why i was concerned about the permanent connection.
>
>So, to sum it up, I do think the problem was a timing issue that was causes
>collisions on the network. And with not very many users connecting to the
>server at once I was going to change it to have one permanant connection
>per app for the life of the app, since idle connections do not really impact
>performace.
>
>What do you think ?
>
>Thanks a lot for all your help/suggestions/ideas. I am the only programmer/developer
>here, so it is nice to discuss this with someone else.
>
>Darcey
>
>P.S. It is always nice to be the hero. Sorry about the e-mail and for being
>so long winded (almost blue in the face). Our ISP was having problems.

Hey, Darcey. Good to hear back from you. I haven't been on the board for

about 5 or 6 days -- been whacked here.

Anyway, IF you can allow people to remain connected (no timeout problems)

AND you don't have too many users, AND it's a closed system (not like Taco

Bell or JC Penny's or some huge-o-company which a go-jillion
users/connections) you're probably going to be okay with the one connection

per application lifespan design. If you run into timeout issues or lack of

resources due to open connections or anything like that, you're going to

have to change your strategy, I think.

On the other hand, if your environment (read "boss") demands that you "just

get it running", well then, shoot! Get it running! However possible! And

however you can without it crashing or erroring out on you! I mean, keep

that paycheck coming! But for sure keep what you've learned (me too!) from

this experience/discussion/trial and error series in your back pocket for

future pitfall avoidance, right?

Best of luck to you and I wish you great success!

dave.

[darcey]

Hi Dave,

>
>Hey, Darcey. Good to hear back from you. I haven't been on the board for
>
>about 5 or 6 days -- been whacked here.
>
>Anyway, IF you can allow people to remain connected (no timeout problems)
>
>AND you don't have too many users, AND it's a closed system (not like Taco
>
>Bell or JC Penny's or some huge-o-company which a go-jillion
>users/connections) you're probably going to be okay with the one connection
>
>per application lifespan design. If you run into timeout issues or lack
of
>
>resources due to open connections or anything like that, you're going to
>
>have to change your strategy, I think.
>
>On the other hand, if your environment (read "boss") demands that you "just
>
>get it running", well then, shoot! Get it running! However possible! And
>
>however you can without it crashing or erroring out on you! I mean, keep
>
>that paycheck coming! But for sure keep what you've learned (me too!) from
>
>this experience/discussion/trial and error series in your back pocket for
>
>future pitfall avoidance, right?



You got it ! It definitely was a good learning experience and I definitely
will keep it in mind for future reference !


>
>Best of luck to you and I wish you great success!

Dido !!

>dave.

And thanks a lot for your help !! I really appreciated it !

Have a good one,

Darcey