Ask the debugging method when connecting SQL errors, thank you

173127891

I use vb to connect to sqlserver. The connection function is as follows:

Public Function ExecuteSQL (ByVal sql As String) As ADODB.Recordset
Dim mycon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set mycon = New ADODB.Connection
'On Error GoTo err
mycon.ConnectionString = connstring
mycon.CursorLocation = adUseClient
mycon.CommandTimeout = 5
mycon.Open

'Exit Function
'err.Clear
Dim stokens () As String
'On Error GoTo exectuesql_error
stokens = Split (sql)
If InStr ("INSER, DELETE, UPDATE", UCase (stokens (0))) Then
      mycon.Execute sql
Else
  Set rst = New ADODB.Recordset
  rst.Open Trim (sql), mycon, adOpenKeyset, adLockOptimistic
 Set ExecuteSQL = rst
End If

End Function

Public Function connstring (serverip) As String
connstring = "Provider = SQLOLEDB.1; Persist Security Info = False; User ID = sa; Password = e; Initial Catalog = tt; Data Source = (local)"
End Function

Used when calling
.........
txtsql1 = "select * from person where username = 'ixcw'"
    Set mrc = ExecuteSQL (txtsql1, serverip)

    If mrc.EOF = False Then
        sss = MsgBox ("Exists!")
    Else
        sss = MsgBox ("Does not exist!")
    End If
..............
When the provided sqlserver information is correct, there is no problem, but when the sql permissions are wrong, an error will appear, prompting that sql does not exist or the connection is wrong. After clicking OK, the program is closed. I want to know how to check if sql is connected successfully Code and abort query event correctly, thanks!

drbabyhai

To be honest, I think your code above is very redundant

ado and ado.net are not the same. In ado, it is generally used to open continuously, that is, it is OK to open it once, try not to close the connection, and keep switching the connection. The actual consumption is greater.

A lot of your code is useless. Modify a part, see the comment section
Option Explicit

'Define Connection and recordset as global, use local unless necessary for a small area
Public mycon As New ADODB.Connection
Public rst As New ADODB.Recordset

Public Function ExecuteSQL (ByVal sql As String) As ADODB.Recordset

    Dim strConn As String 'Defines connection parameters

    'Connetcion is assignment
    strConn = "Provider = SQLOLEDB.1; Persist Security Info = False; User ID = sa; Password = e; Initial Catalog = tt; Data Source = (local)"

    'Added the function to determine whether the Connection is normally connected

   'Set mycon = New ADODB.Connection' is completely unnecessary, because you have already instantiated Connection above
  'Use function to determine whether sql has been opened normally
  If LoadConn = False Then Exit Function
  
   
    Dim stokens () As String

    stokens = Split (sql)
    If InStr ("INSER, DELETE, UPDATE", UCase (stokens (0))) Then
        Debug.Print sql 'First debug.print your sql to see if it is correct, and it is recommended to use a statement such as strsql instead of sql. The above judgment code does not know what conditions you wrote, it feels very messy
        mycon.Execute sql
    Else
       'Set rst = New ADODB.Recordset is still redundant code
        rst.Open Trim (sql), mycon, adOpenKeyset, adLockOptimistic
        Set ExecuteSQL = rst
    End If

End Function

'Set boolen to determine whether the connection is normal
Public Function LoadConn (strConn As String) As Boolean
  On Error GoTo err:
  mycon.CursorLocation = adUseClient
  mycon.Open strConn
  mycon.CommandTimeout = 5
  
  mycon.Open
  LoadConn = True
  Debug.Print "Open normally"
err:
  LoadConn = False
  Debug.Print "Cannot open sql"
  err.Clear
  
End Function

drbabyhai

I've seen a lot of people write code in a variety of ways, using words to be self-righteous.

Actually, I think you do n’t even know what a function is and a sub, it ’s a mess.
Including your Public Function ExecuteSQL (ByVal sql As String) As ADODB.Recordset. I don't know what it is used for, so there is no way to modify it completely.

You'd better read the help of ado, carefully look at a few clear-cut code, your code above is a mess. How to use Connection and recordset, when to use it, how to declare it, and the scope of the declaration are completely unclear.

Of course the simplest variable and err judge you won't either. . .

chengyu876

The concepts of declarations and definitions are not clear
Dim mycon As New ADODB.Connection declaration + definition
Set mycon = New ADODB.Connection definition
Although it will not go wrong, it will be confusing

'Reasonable writing

Dim mycon As ADODB.Connection statement
...
on error goto errHander
Set mycon = New ADODB.Connection definition
...
errHandler:
'Error handling

chengyu876

The concepts of declarations and definitions are not clear
Dim mycon As New ADODB.Connection declaration + definition
Set mycon = New ADODB.Connection definition
Although it will not go wrong, it will be confusing

'Reasonable writing

Dim mycon As ADODB.Connection statement
...
on error goto errHander
Set mycon = New ADODB.Connection definition
...
errHandler:
'Error handling

chengyu876

The concept is a bit confusing

drbabyhai

mycon.Open
   LoadConn = True
   Debug.Print "Opens normally"
'Plus
exit function

Adding the above, I just forgot to write, it seems that you yourself have not even found the wrong err -_-

woainipy99

After the connection is opened, it is better not to close it. If all programs use this connection repeatedly, it may crash, I have encountered