Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 46

Thread: Solved: SQL Update query problem

  1. #1
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location

    Solved: SQL Update query problem

    Can anyone tell me why the code below update query doesnt update my record?

    I have a table called tbl_Test with field names

    FieldName1
    FieldName2
    FieldName3


    Thanks Gibbo

    [VBA] Const adOpenForwardOnly As Long = 0
    Const adLockReadOnly As Long = 1
    Const adCmdText As Long = 1
    Dim oConn As Object
    Dim oRS As Object
    Dim sConnect As String
    Dim sSQL As String
    Dim ary

    sConnect = (stCon)

    sSQL = "SELECT * From tbl_Test"
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
    adLockReadOnly, adCmdText
    ' Check to make sure we received data.
    If oRS.EOF Then
    MsgBox "No records returned.", vbCritical
    Else
    sSQL = "UPDATE tbl_Test " & _
    " SET FieldName3 = 'None' " & _
    "WHERE FieldName1 = 'Me.TextBox1.Text' AND FieldName2 = 'Me.TextBox2.Text'"
    oRS.ActiveConnection.Execute sSQL

    sSQL = "SELECT * From tbl_Test"
    oRS.ActiveConnection.Execute sSQL
    ary = oRS.GetRows
    MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0) & ", " & ary(3, 0)
    End If
    oRS.Close
    Set oRS = Nothing [/VBA]

  2. #2
    VBAX Regular
    Joined
    Aug 2004
    Location
    London, England
    Posts
    52
    Location
    Unless I've misunderstood, I fancy the update SQL should look like this:
    [vba]
    sSQL = "UPDATE tbl_Test " & _
    " SET FieldName3 = 'None' " & _
    "WHERE FieldName1 = '" & Me.TextBox1.Text & "' AND FieldName2 = '" & Me.TextBox2.Text & "'"
    oRS.ActiveConnection.Execute sSQL
    [/vba]

    In other words, at the moment you're only updating the table where the actual value of FieldName1 is literally "Me.TextBox1.Text", rather than the value of Me.TextBox1. You missed some of the concatenation, easy mistake to make!!

  3. #3
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    excellent thanks

    I ve only just started looking at SQL so appreciate all the help i can get at the moment

    Can anyone suggest a good place to look to learn more please

    cheers

    Gibbo

  4. #4
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    Here is a small SQL-guide that may get handy for You:

    http://techonthenet.com/sql/

    I agree, SQL is in the long run more difficult then the ADO-stuff. One approach You may benefit from is create queries in MS Access and view the SQL code.

    Edit: My post here may also be of interest in general terms for DB & ADO & SQL:
    http://www.ozgrid.com/forum/showthread.php?t=32246

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #5
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Dennis,

    Loads to learn so appreciate all the help I can get with this

    Gibbo

  6. #6
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Quick Question Dennis

    Is there an easy way to use the sql as generated by access

    i.e
    [VBA] sSQL = SELECT [tbl_Test].[ID], [tbl_Test].[FieldName1], [tbl_Test].[FieldName2], _
    [tbl_Test].[FieldName3], [tbl_Test].[FieldName4], [tbl_Test].[FieldName5]"
    FROM tbl_Test _
    WHERE ((([tbl_Test].[FieldName3])="None")); [/VBA]

    Or do i have to convert it all to the syntax you ve already shown me?

    Cheers

    Gibbo
    Last edited by geekgirlau; 10-22-2005 at 06:30 PM. Reason: Put line breaks in code

  7. #7
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    As a beginner You can accept the generated syntax especially if You work with Access DB. However, as You notice it generate some overflow (in my opinion) You can later use:

    [vba]
    SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
    FROM tbl_Test WHERE FieldName3="None";
    [/vba]

    But I'm convinced that other members here who have better knowledge of SQL can guide You better

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  8. #8
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks

    Having generated my query then how do i then view the results?

    I also assume id have to amend the above slightly to

    [VBA]sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
    FieldName5 FROM tbl_Test WHERE FieldName3= '" & None & "';"
    [/VBA]


    Gibbo

  9. #9
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    Yes, that's correct.

    Having generated my query then how do i then view the results?
    I'm not sure I understand Your question but if I'm correct then I assume You're in the Query -window and then You can right click and choose SQL from the popup menu.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  10. #10
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Sorry Dennis

    Im accessing my database from a userform within excel so I need to return the results somehow to a userform within excel

    A listbox on a userform within excel for example

    Cheers

    Gibbo

  11. #11
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    OK, now I'm with You

    And yet another link to another good friend's (aka Mark007) site where another good friend Will (aka WillR) have made an example available about populating a Userform ListBox with ADO:

    http://www.thecodenet.com/articles.php?id=24

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  12. #12
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks Dennis

    That answers a lot of questions for me, Im had wondered about populating an array so I can use the indiviual columns to populate text boxes etc

    I assume the following will work but i ll check it later

    Me.TextBox1.Value = vaData(0, 0)

    Me.TextBox2.Value = vaData(1, 0)

    Me.TextBox3.Value = vaData(2, 0)

    and so on


    thanks again

    If you have other places I can research around ADO and SQL please let me know

    gibbo

  13. #13
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    Instead of answer I suggest that You try it out and study the outcome. If You got any questions You're most welcome

    How serious are You when it comes to ADO / SQL? There exist some good books but they are usually expensive. Except for that they target different DB - platforms (SQL Server, Oracle, DB2 etc) and Access is not usually covered so well.

    BTW, I sent You a PM the other day about the ADO helpfile which I believe is the best resource available unless You want buy books.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  14. #14
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    sorry dennis did mean to reply and that is very useful yes, this is definately a way forward for me at my work as it resolves a lot of issues for me

    I will continue to play and learn

    Thanks for your time

    Gibbo

  15. #15
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo - Enjoy the weekend and let us know when You got some questions

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  16. #16
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Gibbo,

    Quote Originally Posted by gibbo1715
    I also assume id have to amend the above slightly to
    [VBA]sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
    FieldName5 FROM tbl_Test WHERE FieldName3= '" & None & "';"
    [/VBA]
    Actually, I think Dennis overlooked or misunderstood what you were asking. What he wrote in the post to which you're responding in the above quotation is SQL and not VBA--despite his having had enclosed the SQL with VBA tags for the forum. To clear things up, the following is SQL:

     SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
    FROM tbl_Test WHERE FieldName3='None';
    The following is an example of an SQL statement incorporated into VBA:

    [VBA]
    Sub DemoSQL()

    Dim strSQL As String
    strSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, " _
    & "FieldName5" & vbNewLine _
    & "FROM tbl_Test WHERE FieldName3='" & Me.TextBox1.Value & "';"
    Debug.Print strSQL
    End Sub
    [/VBA]
    Results:

    SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
    FROM tbl_Test WHERE FieldName3='SampleData';
    I suggest using helper functions to enclose criteria with quotation marks and pound signs (#) instead of writing them manually. It's not that they're difficult to write, but they are easy to overlook, so helper functions, no matter how simple, reduce errors.

  17. #17
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks for clarifying, Im still a bit confused though, I ve added your amended SQL to my code but whenever i click the button I always only get the first record in the dataset

    Any ideas what im doing wrong please

    Cheers

    Gibbo

    [VBA] Const adOpenForwardOnly As Long = 0
    Const adLockReadOnly As Long = 1
    Const adCmdText As Long = 1
    Dim sConnect As String
    Dim sSQL As String
    Dim vaData As Variant

    On Error GoTo Err:
    sConnect = (stCon)
    sSQL = "SELECT * From tbl_Test"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sSQL, sConnect, adOpenForwardOnly, _
    adLockReadOnly, adCmdText
    ' Check to make sure received data.
    If rs.EOF Then
    MsgBox "No records returned.", vbCritical
    Else
    strSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, " _
    & "FieldName5" & vbNewLine _
    & "FROM tbl_Test WHERE FieldName3='" & Me.TextBox1.Value & "';"
    rs.ActiveConnection.Execute sSQL

    With rs
    vaData = .GetRows
    End With

    'Populate the TextBoxes.

    Me.TextBox1.Value = vaData(1, 0)
    Me.TextBox2.Value = vaData(2, 0)
    Me.TextBox3.Value = vaData(3, 0)
    Me.TextBox4.Value = vaData(4, 0)
    Me.TextBox5.Value = vaData(5, 0)
    End If
    rs.Close
    Set rs = Nothing
    Exit Sub
    Err:
    Call ErrorLog(Err.Description, Err.Number, Me.Name)[/VBA]

  18. #18
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    The sample query returns only those records where the field named "FieldName3" has a value of whatever is in the textbox called "TextBox1". In other words, it selects those rows that meet that criterion, returning the columns specified.

    Is it possible that you only have one row where the value in FieldName3 is the same as the value in TextBox1? (Edit: just noticed that you're executing with sSQL instead of the strSQL that you copied from my post. You must only have one row in the table. Or you probably need to set the rows to return in the GetRows method. I usually do a workspace & Jet instead of ADO, so I don't remember and would need to mock it up on my end to tell you something more definitive.)

    This may be more along the lines of what you're looking for...

    [VBA] Sub PopulateTextBoxes()
    Const adOpenForwardOnly As Long = 0
    Const adLockReadOnly As Long = 1
    Const adCmdText As Long = 1

    Dim sConnect As String
    Dim sSQL As String
    Dim vaData As Variant

    On Error GoTo PopulateTextBoxes_Error

    sConnect = (stCon)
    sSQL = "SELECT * From tbl_Test"

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
    If rs.EOF = False Then
    vaData = rs.GetRows
    Me.TextBox1.Value = vaData(1, 0)
    Me.TextBox2.Value = vaData(2, 0)
    Me.TextBox3.Value = vaData(3, 0)
    Me.TextBox4.Value = vaData(4, 0)
    Me.TextBox5.Value = vaData(5, 0)
    End If
    PopulateTextBoxes_Exit:
    rs.Close
    Set rs = Nothing
    Exit Sub
    PopulateTextBoxes_Error:
    Select Case Err.Number
    'Case
    Case Else
    Call ErrorLog(Err.Description, Err.Number, Me.Name)
    End Select
    Resume PopulateTextBoxes_Exit
    End Sub
    [/VBA]

  19. #19
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks

    Sorry but I am actually trying to find a unique record and fill the textboxes with the values for that record

    How do I do that

    Thanks

    Gibbo

  20. #20
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Think I ll post the others I ve been working on incase they are of use to others, these populate a listbox or a combobox on a userform (Mine is in Excel)

    [VBA] Sub Populate_Listbox()
    Dim cnADO As ADODB.Connection
    Dim rstADO As ADODB.Recordset
    Dim strCon As String, strSQL As String
    Dim vaData As Variant
    Dim lCols As Long

    'Set the ADO connection
    Set cnADO = New ADODB.Connection

    'Specify the connection string.
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
    & "C:\Gibbos.mdb;Persist Security Info=False"
    'Specify your SQL statement.

    strSQL = "SELECT * FROM tbl_Test"

    With cnADO
    .CursorLocation = adUseClient
    'Client-side cursor location (as opposed to server-side)
    'required as we are going to disconnect the recordset
    'in order to populate the listbox.
    .Open strCon 'Open the connection.
    'execute the SQL statement.
    Set rstADO = .Execute(strSQL)
    End With

    With rstADO
    Set .ActiveConnection = Nothing
    'Disconnect recordset.
    lCols = .Fields.Count
    'Populate a variant array with the recordset.
    vaData = .GetRows
    End With
    'Close the connection.
    cnADO.Close

    'Populate the Listbox.
    With UserForm1
    With .ListBox1
    .Clear
    .ColumnCount = lCols
    .BoundColumn = lCols
    .List = Application.Transpose(vaData)
    .ListIndex = -1
    End With
    End With

    'Release objects from memory.
    Set rstADO = Nothing
    Set cnADO = Nothing

    End Sub
    Sub Populate_Combobox()
    Dim cnADO As ADODB.Connection
    Dim rstADO As ADODB.Recordset
    Dim strCon As String, strSQL As String
    Dim vaData As Variant
    Dim lCols As Long
    Dim x As Long

    'Set the ADO connection
    Set cnADO = New ADODB.Connection

    'Specify the connection string.
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
    & "C:\Gibbos.mdb;Persist Security Info=False"

    'Specify your SQL statement.
    strSQL = "SELECT * FROM tbl_Test"

    With cnADO
    .CursorLocation = adUseClient
    'Client-side cursor location (as opposed to server-side)
    'required as we are going to disconnect the recordset
    'in order to populate the listbox.
    .Open strCon 'Open the connection.
    'execute the SQL statement.
    Set rstADO = .Execute(strSQL)
    End With

    With rstADO
    Set .ActiveConnection = Nothing
    'Disconnect recordset.
    lCols = .Fields.Count
    'Populate a variant array with the recordset.
    vaData = .GetRows
    End With

    'Close the connection.
    cnADO.Close
    'Populate the Listbox.
    With UserForm1
    With .ComboBox1
    .Clear
    .ColumnCount = 2
    .List = Application.Transpose(vaData)
    .ListIndex = 0
    End With
    End With

    'Release objects from memory.
    Set rstADO = Nothing
    Set cnADO = Nothing

    End Sub [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •