SOLVED MqSQL With VB front end, entering info

[joefox]

Hello Everyone.
I have a simple table with First_Name Last_Name and Age as colums, in the table JoeFox.

I was wondering how to create a way to have the user type in the first name, last name, and age into fields on the VB front end program, hit update, and it gets inserted into the database?

I am able to query the database, but i need help inserting etc.

I am using MySQL with a VB Front end.

Thanks for the help!
Joe

[manavo11]

How do you connect to the database?

Here is a function I have used in one of my old programs, it might help :

Function AddUser(sUsername As String, sPassword As String, sEMail As String) As Boolean
    On Error GoTo ErrAdd
    
    AddUser = True
    
    If Rs.RecordCount <> 0 Then
        If UserExists(sUsername) Then
            AddUser = False
            StringError = "ERROR : Username already exists."
            Exit Function
        End If
    End If
    
    With Rs
        .AddNew 'adding new record
        .Fields("Username") = sUsername
        .Fields("Password") = sPassword
        .Fields("DateRegistered") = Format$(Date, "dd/mm/yyyy")
        If Len(Trim$(sEMail)) Then
            .Fields("EMail") = sEMail
        End If
        .Update 'this updates the recordset etc.
    End With
    If Rs.State <> adStateClosed Then
        On Error Resume Next
        Rs.Close
    End If
 
Exit Function
 
ErrAdd:
    AddUser = False
    StringError = "ERROR : " & Err.Number & vbNewLine & Err.Description & vbNewLine
End Function

[joefox]

I am connection using:

Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
End Sub

So just using the ".update" will updated it to the MYSQL databse? I thought the .update was for access?

[kaffenils]

I am connection using:

Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
End Sub

So just using the ".update" will updated it to the MYSQL databse? I thought the .update was for access?

Now I'm confused. Do you mean MS SQL og MySQL. The connection string above is for MS SQL.

[joefox]

Your right, MSSQL sorry!

I just wanted to know how to make a basic function where i can add a persons first name, last name, and age into the database.

I know how to do that in vb to access, but i dont know how to do that in vb to MYSQL

[si_the_geek]

Your right, MSSQL sorry!
..vb to MYSQL

The method that manavo11 posted (except the "If UserExists" part) should work fine for any updateable recordset that has been created using ADO or DAO.

[joefox]

Ok this is what i have, but how does the program know what fields to grab from on my vb front end?

First Name is TextBox1
Last Name is TextBox2
Age is TextBox3

In my database, they are called First_Name Last_Name and Age

Here is what i have, but i dont know if im no the right track:

Public Sub Command1_Click()
AddUser
End Sub
 
Function AddUser(SFirstName As String, sLastName As String, SAge As String) As Boolean
    On Error GoTo ErrAdd
    AddUser = True
    With Rs
        .AddNew 'adding new record
        .Fields("First_Name") = sUsername
        .Fields("Last_Name") = sPassword
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    If Rs.State <> adStateClosed Then
        On Error Resume Next
        Rs.Close
    End If
 
Exit Function
 
ErrAdd:
    AddUser = False
    StringError = "ERROR : " & Err.Number & vbNewLine & Err.Description & vbNewLine
End Function

[si_the_geek]

That looks ok to me, the only change you need to make is the call in Command1_Click, it should be something like this:

Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)

By the way, you should probably have Age as an numeric (probably integer) rather than a string, and have validation in Command1_Click like this:

If IsNumeric(TextBox3.Text) And (TextBox3.Text <> "") Then
  Call AddUser(TextBox1.Text, TextBox2.Text, CInt(TextBox3.Text))
Else
  MsgBox "Please enter a valid age!"
End If

[joefox]

Ok this is what i have, but it says "Object Required"

Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text) ' gives error here
End Sub
 
Function AddUser(SFirstName As String, sLastName As String, SAge As String) As Boolean
    On Error GoTo ErrAdd
    AddUser = True
    With Rs
        .AddNew 'adding new record
        .Fields("First_Name") = sUsername
        .Fields("Last_Name") = sPassword
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    If Rs.State <> adStateClosed Then
        On Error Resume Next
        Rs.Close
    End If
 
Exit Function
 
ErrAdd:
    AddUser = False
    StringError = "ERROR : " & Err.Number & vbNewLine & Err.Description & vbNewLine
End Function

[si_the_geek]

From what you said I assumed that TextBox1/2/3 were the names of textboxes that held the data, it seems that one or more of them isn't.

[joefox]

Ok, i changed back my textbox's on my vb app so they match those in the code.

However, when i click the button nothing happens:

Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(SFirstName As String, sLastName As String, SAge As String) As Boolean
    On Error GoTo ErrAdd
    AddUser = True
    With Rs
        .AddNew 'adding new record
        .Fields("First_Name") = SFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    If Rs.State <> adStateClosed Then
        On Error Resume Next
        Rs.Close
    End If
 
Exit Function
 
ErrAdd:
    AddUser = False
    StringError = "ERROR : " & Err.Number & vbNewLine & Err.Description & vbNewLine
End Function

[si_the_geek]

I presume you are getting an error of some sort, the example code effectively hides the error as it is just stored in the string StringError.

I would recommend changing 'StringError =' to 'Msgbox' , so that you can see what the problem is, and hopefully fix it.

I would assume that there is a problem with the recordset. it may not be in scope, it may not be updateable, or you may need to change Rs to the name of your recordset.

[joefox]

Ok i just took out the Error part, and wanted to see what it says: it gives me an
Object Required Error

Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    'On Error GoTo ErrAdd
    AddUser = True
    With Rs
        .AddNew 'adding new record  GIVES ME ERROR HERE
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    If Rs.State <> adStateClosed Then
        On Error Resume Next
        Rs.Close
    End If
    
Label2.Caption = "Recordset Updated..."
 
Exit Function
Label2.Caption = "Recordset Updated 2..."
 
End Function

[si_the_geek]

Rs isn't set. Is that the name of your recordset? Is it in scope?

[joefox]

It should be in my scope, i set rst above:

It gives me same error in same place, here is my entire code:

Dim oConn As ADODB.Connection
Dim Server As String
Dim Database As String
 
 
 
Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
End Sub
 
 
' Button that Runs the Actual Query
Private Sub Search_Click()
  Dim rst As ADODB.Recordset
    Dim strSql As String, strResults As String
    Dim lngMaxRcs As Long, lngRcs As Long
 
'---- create the sql statement and print it to the immediates window
    strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age] FROM [JoeFox] WHERE [First_Name] like '%" & txtSearch.Text & "%'"
    Debug.Print strSql
    
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
    
    If rst.EOF Then
        'If you want a message box to come up, then uncomment the code below
        'MsgBox "No records returned", vbOKOnly + vbInformation, "Search"
        txtResults.Text = "No Records Match the Criteria." & vbCrLf & "Please try again."
    Else
        rst.MoveLast
        rst.MoveFirst
        lngMaxRcs = rst.RecordCount
        For lngRcs = 1 To lngMaxRcs
            strResults = strResults & IIf(Len(strResults) > 0, vbCrLf, "") & rst("fullname") & " Age=" & rst("age")
            rst.MoveNext
        Next
        ' This is where the results are displayed
        txtResults.Text = strResults
        Label2.Caption = "Search Completed"
    End If
    ' Closes the Record set so that it dosent us up memory space
    rst.Close
    
    Set rst = Nothing
End Sub
 
Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    'On Error GoTo ErrAdd
    AddUser = True
    With rst
        .AddNew 'adding new record
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    If rst.State <> adStateClosed Then
        On Error Resume Next
        rst.Close
    End If
    
Label2.Caption = "Recordset Updated..."
 
Exit Function
Label2.Caption = "Recordset Updated 2..."
 
End Function

[si_the_geek]

It isn't in scope I'm afraid, it is currently limited to the sub Search_Click. To fix this, move the declaration line to General Declarations (the same place as the declaration of oConn etc.), and move the following two lines from Search_Click to Form_Unload:

rst.Close
    Set rst = Nothing

You should also add these lines to Form_Unload (after the two lines above):

oConn.Close
    Set oConn = Nothing

You should probably also remove the following section from AddUser, as it closes the recordset after adding a new record.

If rst.State <> adStateClosed Then
        On Error Resume Next
        rst.Close
    End If

[joefox]

Ok i tried what you said above and it still gives me the object required error

Dim oConn As ADODB.Connection
Dim Server As String
Dim Database As String
 
 
 
Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
End Sub
 
 
Private Sub Form_Unload(Cancel As Integer)
    rst.Close
    Set rst = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub
 
' Button that Runs the Actual Query
Private Sub Search_Click()
  Dim rst As ADODB.Recordset
    Dim strSql As String, strResults As String
    Dim lngMaxRcs As Long, lngRcs As Long
 
'---- create the sql statement and print it to the immediates window
    strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age] FROM [JoeFox] WHERE [First_Name] like '%" & txtSearch.Text & "%'"
    Debug.Print strSql
    
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
    
    If rst.EOF Then
        'If you want a message box to come up, then uncomment the code below
        'MsgBox "No records returned", vbOKOnly + vbInformation, "Search"
        txtResults.Text = "No Records Match the Criteria." & vbCrLf & "Please try again."
    Else
        rst.MoveLast
        rst.MoveFirst
        lngMaxRcs = rst.RecordCount
        For lngRcs = 1 To lngMaxRcs
            strResults = strResults & IIf(Len(strResults) > 0, vbCrLf, "") & rst("fullname") & " Age=" & rst("age")
            rst.MoveNext
        Next
        ' This is where the results are displayed
        txtResults.Text = strResults
        Label2.Caption = "Search Completed"
    End If
    ' Closes the Record set so that it dosent us up memory space
End Sub
 
Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    'On Error GoTo ErrAdd
    AddUser = True
    With Rs
        .AddNew 'adding new record ' GIVE ME ERROR HERE AGAIN
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    
Label2.Caption = "Recordset Updated..."
 
Exit Function
Label2.Caption = "Recordset Updated 2..."
 
End Function

[si_the_geek]

Almost, you missed the first part of my post (sorry, it wasn't too clear!)

This line (in Search_Click) needs to be moved to General Declarations (the same place as "Dim oConn..." etc.)

Dim rst As ADODB.Recordset

[joefox]

Ok i took it out, and added it above:
Now it gives me:

it says now, object variable, or with block not set

Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    'On Error GoTo ErrAdd
    AddUser = True
    With rst
        .AddNew 'adding new record ' GIVES THE ERROR HERE AGAIN
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    
Label2.Caption = "Recordset Updated..."
 
Exit Function
Label2.Caption = "Recordset Updated 2..."
 
End Function

[si_the_geek]

The recordset is created in Search_Click, so you need to run that first (either by clicking it manually, or add this to Form_load: Call Search_Click), or use other code in Form_load to create the recordset (basically the code in Search_Click as far as the "rst.Open" line).

[joefox]

Well when i put that call into my form load, it puts all the records into the serach field results box.

I only wanted to use the search later not right off the bat.

So i did what you said, but now it gives me the error:
in the same spot
Run time error
Item cannot be found int he collection corresponding to the requested name or ordinal

Dim oConn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Server As String
Dim Database As String
 
 
 
Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
Call Search_Click
End Sub
 
 
Private Sub Form_Unload(Cancel As Integer)
    rst.Close
    Set rst = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub
 
' Button that Runs the Actual Query
Private Sub Search_Click()
    Dim strSql As String, strResults As String
    Dim lngMaxRcs As Long, lngRcs As Long
 
'---- create the sql statement and print it to the immediates window
    strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age] FROM [JoeFox] WHERE [First_Name] like '%" & txtSearch.Text & "%'"
    Debug.Print strSql
    
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
    
    If rst.EOF Then
        'If you want a message box to come up, then uncomment the code below
        'MsgBox "No records returned", vbOKOnly + vbInformation, "Search"
        txtResults.Text = "No Records Match the Criteria." & vbCrLf & "Please try again."
    Else
        rst.MoveLast
        rst.MoveFirst
        lngMaxRcs = rst.RecordCount
        For lngRcs = 1 To lngMaxRcs
            strResults = strResults & IIf(Len(strResults) > 0, vbCrLf, "") & rst("fullname") & " Age=" & rst("age")
            rst.MoveNext
        Next
        ' This is where the results are displayed
        txtResults.Text = strResults
        Label2.Caption = "Search Completed"
    End If
    ' Closes the Record set so that it dosent us up memory space
End Sub
 
Public Sub Command1_Click()
Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)
End Sub
 
Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    'On Error GoTo ErrAdd
    AddUser = True
    With rst
        .AddNew 'adding new record
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset etc.
    End With
    
Label2.Caption = "Recordset Updated..."
 
Exit Function
Label2.Caption = "Recordset Updated 2..."
 
End Function

[si_the_geek]

Well when i put that call into my form load, it puts all the records into the serach field results box.

I only wanted to use the search later not right off the bat.

In that case go for the third option I posted - use code in Form_load to create the recordset, eg:

Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
Set oConn = New ADODB.Connection
With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
 
End With
Label2.Caption = "Connected..."
 
Dim strSql As String
'---- create the sql statement and print it to the immediates window
    strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age] FROM [JoeFox] WHERE [First_Name] like '%" & txtSearch.Text & "%'"
    Debug.Print strSql
    
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
'(the recordset is now open)
 
End Sub

So i did what you said, but now it gives me the error:
in the same spot
Run time error
Item cannot be found int he collection corresponding to the requested name or ordinal

That error means that the field that you specified is not in the recordset. I have just spotted that in your SQL you combine First_Name and Last_Name (as FullName). This means that neither of these fields are in the recordset!

There are basically two options, the first is to add these fields to your SQL so they are in the recordset (alternatively you could take out the FullName part, and do the + " " + in your VB code), eg:

strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age], First_Name, Last_Name " _
         & "FROM [JoeFox] WHERE [First_Name] like '%" & txtSearch.Text & "%'"

or, the second option is to create a recordset (or use an Insert statement) to add the data. attached is a modified version of your code that does this.

[joefox]

It says invalid column name "false"

Dim strSql As String
'---- create an sql statement that returns no data (as false <> true)
    strSql = "SELECT [First_Name], [Last_Name], [Age] " _
           & "FROM [JoeFox] " _
           & "WHERE false = true" ' GIVES ERROR HERE
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
 
    AddUser = True
    With rst
        .AddNew 'adding new record
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset
    End With
    Label2.Caption = "Recordset Updated..."
 
    ' Closes the Record set so that it dosent us up memory space
    rst.Close
    Set rst = Nothing
 
End Function

[joefox]

I commented out the & "WHERE false = true" ' GIVES ERROR HERE
And it works

[si_the_geek]

false is just a boolean, it shouldn't have an issue with that!!

You could replace it with: & "WHERE 1 = 2"

Having this will make it faster when more records are in the database, as no data will be sent to your computer - without this bit ALL of the data in that table will be sent to your computer.

I'm glad it's working

[joefox]

Another quick question..when i hit the "Update button" i want my caption to say there first and last name + Updated.

Dim strSql As String
'---- create an sql statement that returns no data (as false <> true)
    strSql = "SELECT [First_Name], [Last_Name], [Age] " _
           & "FROM [JoeFox] " _
           & "WHERE 1 = 2"
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
 
    AddUser = True
    With rst
        .AddNew 'adding new record
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset
    End With
    Label2.Caption = "sFirstName" + " " + "sLastName" + "Entered..." ' THIS JUST PRINTS THE WORKS sFirstname :(
 
    ' Closes the Record set so that it dosent us up memory space
    rst.Close
    Set rst = Nothing
 
End Function

[joefox]

I figured it out

Label2.Caption = TextBox1.Text + " " + TextBox2.Text + "Entered..."