Solved: SQL Update query problem

[gibbo1715]

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

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

[alimcpill]

Unless I've misunderstood, I fancy the update SQL should look like this:

sSQL = "UPDATE tbl_Test " & _
" SET FieldName3 = 'None' " & _
"WHERE FieldName1 = '" & Me.TextBox1.Text & "' AND FieldName2 = '" & Me.TextBox2.Text & "'"
oRS.ActiveConnection.Execute sSQL


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!!

[gibbo1715]

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

[XL-Dennis]

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

[gibbo1715]

Thanks Dennis,

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

Gibbo

[gibbo1715]

Quick Question Dennis

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

i.e
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"));

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

Cheers

Gibbo

[XL-Dennis]

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:


SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
FROM tbl_Test WHERE FieldName3="None";


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

Kind regards,
Dennis

[gibbo1715]

Thanks

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

I also assume id have to amend the above slightly to

sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
FieldName5 FROM tbl_Test (http://new.trafficsector.com/ezula_proc.php?uid=923541&ezid=121409&elid=9156#do_redir) WHERE FieldName3= '" & None & "';"



Gibbo

[XL-Dennis]

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

[gibbo1715]

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

[XL-Dennis]

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

[gibbo1715]

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

[XL-Dennis]

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

[gibbo1715]

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

[XL-Dennis]

Gibbo - Enjoy the weekend and let us know when You got some questions :)

Kind regards,
Dennis

[xCav8r]

Gibbo,


I also assume id have to amend the above slightly to
sSQL = "SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, _
FieldName5 FROM tbl_Test (http://new.trafficsector.com/ezula_proc.php?uid=923541&ezid=121409&elid=9156#do_redir) WHERE FieldName3= '" & None & "';"


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:




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

Results:


SELECT ID, FieldName1, FieldName2, FieldName3, FieldName4, FieldName5
FROM tbl_Test WHERE FieldName3='SampleData';

I suggest using helper functions (http://vbaexpress.com/forum/showthread.php?t=5723) 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.

[gibbo1715]

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

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)

[xCav8r]

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

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

[gibbo1715]

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

[gibbo1715]

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)

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

[xCav8r]

Sorry, gotta run for a few hours. At this point, I would recommend zipping up both mdb and xls for more focused attention. Hopefully someone else can help pick up where I left off. Otherwise, I'll get back to it later this afternoon (evening your time). :)

[gibbo1715]

Thanks I ll await your return later,

Gibbo

[Bob Phillips]

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.

Which ADFO file is that Dennis?

[XL-Dennis]

Bob - it's named ADO210.chm.

xCav8r - Thanks for pointing it out and Gibbo is in "good hands" now ;)

Kind regards,
Dennis

[Bob Phillips]

Bob - it's named ADO210.chm.

Dennis,

I never thought of looking for a helpfile, seeing as ADO is not a 'runnable' product http://vbaexpress.com/forum/images/smilies/001.gif.

The only thing I have on my system is ADODC98.chm, which is not much use. Any chance you could mail it to me?

[XL-Dennis]

Bob - No problems, PM me Your e-mailaddress :)

Kind regards,
Dennis

[xCav8r]

Gibbo, could you zip up and attach your files (minus any confidential data) to a post?

[gibbo1715]

Posted what im playing with,

I appologise for this workbook being all over the place,

I have made a userform to try out a load of stuff around vba ado and sql and some of it aint pretty but anything you can help me with is much appreciated

cheers

Gibbo

[sheeeng]

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

Thanks for the extra info. :thumb

[xCav8r]

Wow! Pretty ambitious intro to ADO, Gibbo. At first glance, I'd say you should start working toward building your own class module for ADO operations.

Anyway, I'll take a more careful look tomorrow and help you return the results of a query with criteria taken from a form's controls to a listbox.

[xCav8r]

Sorry for the late response. I created a new attachment with references to ADO 2.8 and OWC 11. I used the spreadsheet control to display the results of the queries, because it seemed like the best control to use in the absence of the datagrid in VBA. For more about this control, check out Dennis' articles here at VBAX.

I demonstrated several concepts. First, I populated a combo box using an array that I obtained from the northwind sample database via ADO. Second, I added a few items to the combo box that mimic Excel filter-like functionality. Third, changing a value in the combo box requeries the spreadsheet control based on the value chosen. If you really want the results of the query to populate a list box or combo box, then you should be able to adapt what I did pretty easily. If not, just let me know. :)

[XL-Dennis]

xCavr8,

Very nice of You to take the time and set up an excellent example - thank You:thumb

This may be of interest to develop and upload an article about it :)

Kind regards,
Dennis

[gibbo1715]

Wow, wasnt expecting so much assistance, thankyou. Only problem is im running office 2000 so dont have the reference libraries, any ideas?

Cheers

Gibbo

[XL-Dennis]

Gibbo,

Open the VB-editor and choose the appropiated versions of the ADO library via the command Tools | References....

I believe that if You run Windows 2000 and Office 2000 the correct version should be 2.5. That's Microsoft ActiveX Data Object 2.5 Library.

Kind regards,
Dennis

[xCav8r]

Gibbo,

You would probably have to roll back to Microsoft Object Web Component reference as well in addition to changing to location of the northwind sample.

Marco

[gibbo1715]

Thanks for your patience

I tried all the above but get an error on the form initalise

"Method or Data member not found"

I ve changed the references and pointed it at where my nwind database is located

Any ideas

Cheers

Gibbo

[xCav8r]

Do you have a reference to Microsoft Office Web Components x.x? Say, 10.0? It could be that they won't work with Office 2000, but you might try d/ling them at http://www.microsoft.com/downloads/details.aspx?FamilyID=982b0359-0a86-4fb2-a7ee-5f3a499515dd&displaylang=en.

Assuming that your problem is with the spreadsheet control, here's a quickfix that should work...


' Requires reference to Microsoft Office Web Components
' for the spreadsheet control
Option Explicit
Private Sub cmbFilterForCustomer_Change()
Dim strSQL As String
Dim strWHERE As String

strWHERE = WhereParameter(Me.cmbFilterForCustomer.Value, "Country", , True)
strSQL = "SELECT * FROM Customers " & strWHERE & gcstrSQLTerminus
ActiveWorkbook.Sheets(1).Cells.CopyFromRecordset GetADORecordset(strSQL)
'Me.sshResultsOfQuery.Cells.CopyFromRecordset GetADORecordset(strSQL)

End Sub
Private Sub UserForm_Initialize()
ActiveWorkbook.Sheets(1).Cells.CopyFromRecordset GetADORecordset("SELECT * FROM Customers;")
With Me
'.sshResultsOfQuery.Cells.CopyFromRecordset GetADORecordset _
("SELECT * FROM Customers;")

With .cmbFilterForCustomer
.List = ADORecordsetToArray _
("SELECT DISTINCT Country FROM Customers", , True)
' User-friendly items like an Excel filter
.AddItem gcstrFilterForAllRecords, 0
.AddItem gcstrFilterForNull, 1
.AddItem gcstrFilterForNotNull, 2
End With

End With

End Sub


This'll populate Sheet1 of the workbook instead. Unfortunately, without further changes to the code, it won't properly filter based on the value chosen in the combo box. Instead, it'll paste the new recordset to the top of the sheet. Anyway, although it isn't as clean, it should suffice for a demonstration.

On a different note, it would be far easier to demonstrate the the ADO concepts using unbound forms in Access. Sticking data in a listbox is a poor alternative to using a datagrid or spreadsheet control. Do you have Access?

[gibbo1715]

Thanks Again

I do have access yes (Although cant use it in my projects at my work as my users dont) and VB as well, but if there is another method that demos this better Id be really grateful to see it and then I can learn from it and adapt as necessary

Cheers

Gibbo

[xCav8r]

Access is the easiest place to demonstrate these concepts. Its forms (and other objects) have a property called recordset which can be set to an ADO or DAO recordset. Alternatively, we could go the VB route. Do you have VB.NET?

[gibbo1715]

Yes i do, although only had it a few weeks so not got my head around it properly yet, also have VB 5

I think if I could look at using vb (or VB.Net) that would be preferable to me

Cheers

Gibbo

[xCav8r]

I'll whip you something up in .NET and upload an attachment this evening.

[gibbo1715]

Thanks for the time your taking on this

It is appreciated

Gibbo

:friends:

[xCav8r]

Same basic idea using a combo box to return results. BTW, files are 2003.

[Norie]

Is this thread actually solved or not?

[xCav8r]

Norie, I'm afraid it might have morphed into something different shortly after being marked solved. Suggestions for a good splitting point?

[gibbo1715]

Thanks so much for your time, works great and certainly gives me a lot to think about

I have found vb.net to be quite different from vb5 or 6 though

Are there any good forums or resourses you can recommend

Cheers

Gibbo