The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Comparing a combo box to field in a table
Posted: 30 Apr 07 3:00 PM
|
fiogf49gjkf0d I am attempting to compare a selection in a combo box to previously made selections already in the db. I tried something simple, If cstr(cboKeytag.text)=rs.Fields("KEYTAG") Then msgbox "That keytag has already been selected, please choose another". This works, but just for the first entry in the table. I would assume this would mean that I would have to make it go through each record in the table. So I then tried to open a new recordset and then select only that column for the comparison. For each x in rs1 If cstr(cboKeytag.tect)=rs1.Fields("KEYTAG") Then msgbox... Next. I don't know if I was in the right direction there, but had a small error or two. I would have posted the actual script, but there have been numerous version from attempting different things.
|
|
|
|
Re: Comparing a combo box to field in a table
Posted: 30 Apr 07 6:38 PM
|
fiogf49gjkf0d Rather than iterating through the entire resultset, why not create another resultset that contains an additional WHERE clause? This way, your code will run faster, as it's using SQL Server to do the selection for you:
strSQL = "... existing SQL ... where table.keytag = '" & cstr(cboKeytag.tect) & "'"
Build a resultset using the above SQL and it will contain only those rows satisfying your search criteria. Be careful with the combination of single and double quotes.
Or, if you are checking only whether 1 or more records satisfying the criteria exists, consider using the GetField() function for simplicity.
PP |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 01 May 07 8:21 AM
|
fiogf49gjkf0d I think that's basically what I was attempting to do by creating a secondary recordset for just the keytag column. Upon trying it the way you suggested, I ended up with this:
'Checks for duplicate keytag selection
strSQL = "SELECT KEYTAG FROM Q10_CAMPAIGN WHERE CONTACTID = '" & Application.BasicFunctions.CurrentContactID & "'"
ElseIf cstr(cboKeytag.Text)=strSQL Then
msgbox "That keytag has already been selected, please choose another.", 48, "Duplicate selection"
However, I am missing something with this statement. I suppose I still don't understand how this will go through each result for the strSQL statement.
|
|
|
|
Re: Comparing a combo box to field in a table
Posted: 01 May 07 1:28 PM
|
fiogf49gjkf0d I also tried:
rs.MoveFirst
Do While Not rs.EOF
If rs.Fields("KEYTAG") = cboKeytag.Text Then
msgbox "That keytag has already been selected, please choose another.", 48, "Duplicate selection"
End If
rs.MoveNext
Loop
Which goes through and compares the combo box against every record in the set. I'd like to break the procedure and start from the beginning instead of continuing on through the recordset. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 01 May 07 6:42 PM
|
fiogf49gjkf0d Aha! Once you have your SQL, you need to submit it to SQL Server for processing and return the results to a recordset.
You then iterate through the items in the recordset.
But maybe there's a simpler way in this case, as I alluded in my previous message.
First of all, make sure you have included the script: System:SLX Database Support.
Then:
If GetField("TOP 1 KEYTAG", "Q10_CAMPAIGN", "KEYTAG = '" & cboKeytag.Text & "' AND CONTACTID = '" & Application.BasicFunctions.CurrentContactID & "'") <> "" then
MsgBox "That keytag has already been selected, please choose another.", 48, "Duplicate selection"
End If
|
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 8:50 AM
|
fiogf49gjkf0d Thank you Phil, that was exactly what I was looking for. Now once I figure out why my datagrid will not refresh after making the first selection I should be good to go. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 11:06 AM
|
fiogf49gjkf0d | Thank you Phil, that was exactly what I was looking for. Now once I figure out why my datagrid will not refresh after making the first selection I should be good to go. |
|
Make sure the grid.Activie property is set to True. Also you can force a refresh with grid.Refresh, although updating it's grid.SQL.Text proptery should refresh the grid as well. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 11:16 AM
|
fiogf49gjkf0d I had the grid.refresh set, the refresh works when the user hits the Save button, but not if the grid is empty prior to the selection. But if the user exits the form and then reenters, the selection is in the grid. Any thoughts as to why this would happen when inserting into an empty datagrid? |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 3:07 PM
|
fiogf49gjkf0d That is most likely BindID related. Your grid does something like "Select * from table where parentid = :BindID". If BindID is blank or a previous record then your grid isn't going to return the correct values. Typically this problem occurs frequently for run-time build datagrids as you have to programmatically fill in the BindId and it can easily be wrong. Design-time grids typically don't have this problem since your bindid and other properties have to be set before you'll ever see any data.
Another problem area is the DataGrid.RecordSet. Say you have a datagrid on a Data Form (not a Tab under Accounts, etc). If you have a child table underneath that and perform updates on the grid, the recordset will only store the changes. Most likely what happens is the grid has some internal structure that has all of the values and the recordset property just keeps the diff of the last change. Setting sortable to true (and loading the entire recordset into memory) gives you access to the complete recordset, basically mirroring it's internal structure. You might not be running into a problem with this now, but you may in the future. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 3:46 PM
|
fiogf49gjkf0d I see what you are saying, in my case, I have the bindID set to Q10Campaign.ContactID (since this form is linked to Contact) in design view. Would it make sense to reiterate the bindID in code with a reference to If rs.BOF Then BindID = Q10Campaign.ContactID? |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 02 May 07 4:04 PM
|
fiogf49gjkf0d If the bindid is set at design-time, then there's no reason to reiterate it in code. You may wish to do something like MsgBox(Grid.BindID) just to make sure it's correctly passing a value.
Since this is a Contact "tab". I assume you have a grid on your Contact Form. Does this grid use an underlying Data Form set to the Q10Campaign table? When you use Add/Edit in the grid does it open this "child form"? Where specifically in the heirarchy of forms is your grid and combobox? If your grid is on the Contact Form (tab), then as you add a record and click OK on the child form, it'll then refresh your grid with the appropriate values. If this grid is in your Contact Form but instead of a child form all of your editing is inline, then you will have to do some bindid manipulation and refreshing to get everything to display correctly.
Just for giggles (but not a permanent solution), have you tried Form.Refresh? This is the equivalent of clicking OK, navigating to the record in the grid and clicking Edit. It refreshes everything on the form though so there is a potential to screw things up. The reason I say it's not permanent is it's not a good idea to sprinkle it around where problems occur like this, considering I've only used this after a Form.Post to reopen a form with the values I just posted to the database. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 04 May 07 8:14 AM
|
fiogf49gjkf0d I have three edit boxes linked to fields on the contact table through the design view. Now nothing happens when I click the save button, but I have no idea why given this is a very simple operation.
'Saves contact data in edit boxes to SLXdb
Sub btnSaveClick(Sender)
Dim rs
Dim cn
'Open the connection
Set cn = Application.GetNewConnection
'Create the Recordset object
Set rs = CreateObject("ADODB.Recordset")
'Set the cursor location (Client),
'Locktype and cursor type
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenStatic
'Open the Recordset
rs.Open "SELECT FIRSTNAME, LASTNAME, EMAIL FROM CONTACT WHERE CONTACTID= '" & Application.BasicFunctions.CurrentContactID & "'", cn
'Set the field values
rs.Fields("FIRSTNAME") = edFirstName.Text
rs.Fields("LASTNAME") = edLastName.Text
rs.Fields("EMAIL") = edEmail.Text
'Call the update method
rs.Update
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 04 May 07 11:25 AM
|
fiogf49gjkf0d Try hooking up SLXProfiler.exe and then click the Save button. You should see it perform the SELECT command, followed by an update statement when you call rs.Update. If you get nothing out of the ordinary then you may want to set script debugging to Always for the Admin user. I agree that your operation is very simple but most likely there's some error that is not being caught, or it's not behaving in the manner it should. |
|
|
| |
|
Re: Comparing a combo box to field in a table
Posted: 07 May 07 8:06 AM
|
fiogf49gjkf0d I tried that earlier, but the edit boxes still would not save to the table.
Also, where might I be able to find the SLXprofiler? I have looked through the installs that I have, but cannot not find anything for that utility. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 07 May 07 8:35 AM
|
fiogf49gjkf0d Run C:\Program Files\SalesLogix\SLXProfiler.exe.
There are three files that compile the SLXProfiler.
-SLXProfiler.chm
-SLXProfiler.exe
-SLXProfiler.dll
|
|
|
|
Re: Comparing a combo box to field in a table
Posted: 10 May 07 3:23 PM
|
fiogf49gjkf0d my result from the slxprofiler:
---------- Client SQL ---------
SELECT A1.EMAIL, A1.LASTNAME, A2.CONTACTID A2_CONTACTID, A1.FIRSTNAME, A1.SECCODEID, fx_RowAccess() fx_RowAccess FROM CONTACT A1 LEFT OUTER JOIN Q10_CAMPAIGN A2 ON (A1.CONTACTID=A2.CONTACTID) WHERE A1.CONTACTID = [DBTYPE_STR | DBTYPE_BYREF,"C6UJ9ADXI0I4"]
---------- Executed SQL ----------
SELECT A1.EMAIL, A1.LASTNAME, A2.CONTACTID A2_CONTACTID, A1.FIRSTNAME, A1.SECCODEID, NULL fx_RowAccess,A1.CONTACTID,A2.Q10_CAMPAIGNID FROM CONTACT A1 LEFT OUTER JOIN Q10_CAMPAIGN A2 ON (A1.CONTACTID=A2.CONTACTID) WHERE A1.CONTACTID = ?
I don't quite understand why the executed WHERE clause compares A1.CONTACTID = ? as opposed to A1.CONTACTID = A2.CONTACTID. |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 15 May 07 10:38 AM
|
fiogf49gjkf0d To clarify, I have a form, form A, linked to the contact form. Form A has three text boxes bound to Contact:FirstName, COntact:LastName, and Contact:Email respectively. The purpose is to update contact information if need be. I cannot seem to get the data to update by declaring that rs.Fields("FIRSTNAME") = edFirstName.text. I have tried rs.Fields("FIRSTNAME").value = edFirstName.text, but that does not work either. Am I using the wrong process to update? |
|
|
|
Re: Comparing a combo box to field in a table
Posted: 15 May 07 1:42 PM
|
fiogf49gjkf0d In the profiler, the Client SQL holds the actual information. Executed SQL shows just the SQL statement SLX tried to perform. The ? means it was a parameterized query (which is resistant to SQL injection attacks) and what you're seeing is actually typical. The last part of the Client SQL statement shows the recordID it pasted.
Now to get to the issue. When you set a Data Form, you tie it to a table. To modify fields in that table, bind controls and the grid automatically performs the insert/update/delete statements necessary. Here's the caveat I think you're hitting: If you are binding a top level field, like say Contact in a form that is a table or even 2 tables deep, it will not automatically update. Essentially it's a readonly field but populated by the database value. To modify these fields I've ALWAYS had to use SQL to modify the field, the form has never taken care of it for me. I don't believe this has changed in v7 either. |
|
|
|