Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Help with GetPrivateProfileString & MySQL Connection

Hi, I'm having problem using GetPrivateProfileString and I'm using MySQL ODBC

Dim fName As String
    fName = App.Path & "\db.ini"
   
    If Len(Dir(fName)) = 0 Then
        WritePrivateProfileString "MySQL", "Server", "192.168.0.8", fName
        WritePrivateProfileString "MySQL", "Port", "3306", fName
        WritePrivateProfileString "MySQL", "User", "root", fName
        WritePrivateProfileString "MySQL", "Password", "", fName
    End If
   
    Dim Server As String
    Dim Port As String
    Dim User As String
    Dim Password As String
   
    Server = String(20, " ")
    Port = String(20, " ")
    User = String(20, " ")
    Password = String(20, " ")
   
    GetPrivateProfileString "MySQL", "Server", "192.168.0.8", Server, Len(Server), fName
    GetPrivateProfileString "MySQL", "Port", "3306", Port, Len(Port), fName
    GetPrivateProfileString "MySQL", "User", "root", User, Len(User), fName
    GetPrivateProfileString "MySQL", "Password", "", Password, Len(Password), fName

    Server = Trim$(Server)
    Port = Trim$(Port)
    User = Trim$(User)
    Password = Trim$(Password)

Public Sub Connect(ByVal Server As String, ByVal Port As Integer, ByVal User As String, ByVal Password As String)
    With adoCon
        On Error GoTo ErrHld
       
        .Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=server;PORT=" & Port & ";DATABASE=toms;USER=" & User & ";PASS=" & Password & ";OPTION=3;"
        .CursorLocation = adUseClient
       
    End With

ErrHld:
    adoError
End Sub


I got problem connecting using these string, because the string here are trailing with empty space, even I trim it.
0
ahpaul
Asked:
ahpaul
1 Solution
 
Ryan ChongCommented:
try add a module of this:

Private Declare Function GetPrivateProfileString Lib "kernel32" _
   Alias "GetPrivateProfileStringA" _
  (ByVal lpSectionName As String, _
   ByVal lpKeyName As Any, _
   ByVal lpDefault As String, _
   ByVal lpReturnedString As String, _
   ByVal nSize As Long, _
   ByVal lpFileName As String) As Long
   
Private Declare Function WritePrivateProfileString Lib "kernel32" _
   Alias "WritePrivateProfileStringA" _
  (ByVal lpSectionName As String, _
   ByVal lpKeyName As Any, _
   ByVal lpString As Any, _
   ByVal lpFileName As String) As Long


Public Sub ProfileSaveItem(inifile As String, lpSectionName As String, lpKeyName As String, lpValue As String)

'This function saves the passed value to the file,
'under the section and key names specified.
'If the ini file does not exist, it is created.
'If the section does not exist, it is created.
'If the key name does not exist, it is created.
'If the key name exists, its value is replaced.
   
   Call WritePrivateProfileString(lpSectionName, _
                                  lpKeyName, _
                                  lpValue, _
                                  inifile)

End Sub


Public Function ProfileGetItem(inifile As String, lpSectionName As String, lpKeyName As String, Optional defaultValue As String = "") As String

'Retrieves a value from an ini file corresponding
'to the section and key name passed.
       
   Dim success As Long
   Dim nSize As Long
   Dim ret As String
 
  'call the API with the parameters passed.
  'The return value is the length of the string
  'in ret, including the terminating null. If a
  'default value was passed, and the section or
  'key name are not in the file, that value is
  'returned. If no default value was passed (""),
  'then success will = 0 if not found.

  'Pad a string large enough to hold the data.
   ret = Space$(2048)
   nSize = Len(ret)
   success = GetPrivateProfileString(lpSectionName, _
                                     lpKeyName, _
                                     defaultValue, _
                                     ret, _
                                     nSize, _
                                     inifile)
   
   If success Then
      ProfileGetItem = Left$(ret, success)
   End If
   
End Function


Public Sub ProfileDeleteItem(lpSectionName As String, _
                             lpKeyName As String, _
                             inifile As String)

'this call will remove the keyname and its
'corresponding value from the section specified
'in lpSectionName. This is accomplished by passing
'vbNullString as the lpValue parameter. For example,
'assuming that an ini file had:
'  [Colours]
'  Colour1=Red
'  Colour2=Blue
'  Colour3=Green
'
'and this sub was called passing "Colour2"
'as lpKeyName, the resulting ini file
'would contain:
'  [Colours]
'  Colour1=Red
'  Colour3=Green
   
   Call WritePrivateProfileString(lpSectionName, _
                                  lpKeyName, _
                                  vbNullString, _
                                  inifile)

End Sub


Public Sub ProfileDeleteSection(lpSectionName As String, _
                                inifile As String)

'this call will remove the entire section
'corresponding to lpSectionName. This is
'accomplished by passing vbNullString
'as both the lpKeyName and lpValue parameters.
'For example, assuming that an ini file had:
'  [Colours]
'  Colour1=Red
'  Colour2=Blue
'  Colour3=Green
'
'and this sub was called passing "Colours"
'as lpSectionName, the resulting Colours
'section in the ini file would be deleted.
   
   Call WritePrivateProfileString(lpSectionName, _
                                  vbNullString, _
                                  vbNullString, _
                                  inifile)

End Sub

then amend your script to as:


Dim fName As String
    fName = App.Path & "\db.ini"
   
    If Len(Dir(fName)) = 0 Then
        WritePrivateProfileString "MySQL", "Server", "192.168.0.8", fName
        WritePrivateProfileString "MySQL", "Port", "3306", fName
        WritePrivateProfileString "MySQL", "User", "root", fName
        WritePrivateProfileString "MySQL", "Password", "", fName
    End If
   
    Dim Server As String
    Dim Port As String
    Dim User As String
    Dim Password As String
   
'    Server = String(20, " ")
'    Port = String(20, " ")
'    User = String(20, " ")
'    Password = String(20, " ")
   
Server = ProfileGetItem(fName, "MySQL", "Server")
Port = ProfileGetItem(fName, "MySQL", "Port" )
User = ProfileGetItem(fName, "MySQL", "User" )
Password = ProfileGetItem(fName, "MySQL", "Password" )


    Server = Trim$(Server)
    Port = Trim$(Port)
    User = Trim$(User)
    Password = Trim$(Password)

Public Sub Connect(ByVal Server As String, ByVal Port As Integer, ByVal User As String, ByVal Password As String)
    With adoCon
        On Error GoTo ErrHld
       
        .Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=server;PORT=" & Port & ";DATABASE=toms;USER=" & User & ";PASS=" & Password & ";OPTION=3;"
        .CursorLocation = adUseClient
       
    End With

ErrHld:
    adoError
End Sub

Is this works for you? regards
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now