Error Connecting to SQL Server 7


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: Error Connecting to SQL Server 7

Hybrid View

  1. #1
    darcey Guest

    Error Connecting to SQL Server 7


    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

  2. #2
    jy Guest

    Re: Error Connecting to SQL Server 7


    "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.

  3. #3
    darcey Guest

    Re: Error Connecting to SQL Server 7


    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.



  4. #4
    dbishop Guest

    Re: Error Connecting to SQL Server 7


    "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

  5. #5
    dbishop Guest

    Re: Error Connecting to SQL Server 7


    "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

  6. #6
    darcey Guest

    Re: Error Connecting to SQL Server 7


    Hi dbishop,

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

    Darcey

  7. #7
    darcey Guest

    Re: Error Connecting to SQL Server 7


    Hi dbishop,

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

    Darcey

  8. #8
    darcey Guest

    Re: Error Connecting to SQL Server 7


    "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

  9. #9
    dbishop Guest

    Re: Error Connecting to SQL Server 7


    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



  10. #10
    dbishop Guest

    Re: Error Connecting to SQL Server 7


    "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


  11. #11
    darcey Guest

    Re: Error Connecting to SQL Server 7



    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.

  12. #12
    dbishop Guest

    Re: Error Connecting to SQL Server 7


    "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.




  13. #13
    darcey Guest

    Re: Error Connecting to SQL Server 7


    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






Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center