November 12, 2010 at 1:37 pm
Hi ,
I am building Access application that has a SQL database in the background (linked tables). I am now working on Search form which I want to do as dynamic as possible. I've had an idea and it works fine until I came to the obstacle, and then by searching for solution I started to doubt on my solution. I have attached an image of Part of the Search form.
Idea is that user would be entering criteria in continious form bound to temp table on user computer that has fields such as: LogicalOperator, FieldName, MathOperator, Criteria . I wanted user to be able to combine his criteria by adding AND or OR. Up to now, I have built "Where clause" from this table and I send it to my SP on server that would execute sql statement with Where clause that I send.
But then I wanted to do the search such that it would returne to me all contacts that have city of Toronto and city of Vancouver in their address (that info is in join table, and contact can have more then 1 address). Then I started to question myself if this is a good approach after all.
Does anyone know any better apporach for the search as this one?
I was thinking of passing this temp_table with criteria to sql server (if possible) and then deal with it on server or something similar.
Thanks for any input.
November 12, 2010 at 1:43 pm
That's pretty much the exact scenario dynamic SQL is meant to handle. You'll either want to build the query dynamically in the front end, or on the database server, but you'll want to build it dynamically. Just make sure it's injection-proof.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 8:11 am
Thank you for response.
Would you mind giving me a SQL stored procedure example that would handle this?
Right now I am building my SQL statement on the front end, and I send WHERE clause as a parameter among other parameters. Everything is fine while I do search for example:
SELECt * FROM (set of join tables...) WHERE City = 'Toronto' AND Company = 'xyz'
But I don't know how to handle example where I want to find all contacts that have address in Toronto and in Vancouver (contacts can have multiple addresses)
SELECt * FROM (set of join tables...) WHERE City = 'Toronto' AND City = 'Vancouver'
If I try like this it gives me zero records. I guess I should use something like INTERSECT, or I am totally wrong.
Thanks
November 16, 2010 at 4:30 am
SELECt * FROM (set of join tables...) WHERE City = 'Toronto' AND City = 'Vancouver'
The WHERE criteria will never be fulfilled. If you want to get result for both Cities use the OR operator or the IN clause.
SELECt * FROM (set of join tables...) WHERE City = 'Toronto' OR City = 'Vancouver'
SELECt * FROM (set of join tables...) WHERE City IN ('Toronto','Vancouver')
Here some code to execute a stored procedure in VBA:
Option Compare Database
Option Explicit
'Declare variables
Private prm As ADODB.Parameter
Private cmd As ADODB.Command
Private rst As ADODB.Recordset
Private par_name As Variant
Private par_value As Variant
Public Function ExecuteSP(par_area As Variant, par_attribute As Variant, returnMessage As String, sp_name As Variant)
'Connection object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
'Pass on name of stored procedure
cmd.CommandText = sp_name
cmd.CommandType = adCmdStoredProc
'Define the parameters for the stored procedure
'@DBName: Name of parameter in the stored procedure containing the DB Name on the server
'Set the parameter for the DBName
'Get the server name
par_name = par_area(0)
par_value = par_area(0)
Set prm = cmd.CreateParameter(par_name, adVarChar, adParamInput, Len(par_value))
prm.Value = par_value
cmd.Parameters.Append prm
'Define the parameters for the stored procedure
'@DBName: Name of parameter in the stored procedure containing the DB Name on the server
'Get the database name
If par_attribute <> "" Then
par_name = par_attribute
Else
par_name = "@" + par_area(1)
End If
par_value = par_area(1)
Set prm = cmd.CreateParameter(par_name, adVarChar, adParamInput, Len(par_value))
prm.Value = par_value
cmd.Parameters.Append prm
'Set the parameter for the message
'@returnMessage: Name of parameter in the stored procedure containing the return message
Set prm = cmd.CreateParameter("@returnMessage", adVarChar, adParamReturnValue, Len(returnMessage))
prm.Value = returnMessage
cmd.Parameters.Append prm
'Execute the stored procedure
Set rst = New ADODB.Recordset
rst.Open cmd
returnMessage = cmd.Parameters("@returnMessage")
End Function
This is the event procedure triggering the execution of the stored procedure.
Private Sub cmdDropView_Click()
On Error GoTo Err_cmdDropView_Click
spname = "ap_drop_views_legacy_reporting" 'Stored procedure name
Select Case frmServerName 'Value comes from an option box with 3 possible values
Case 1
' par_area = Array("WhateverServerName", Me!cboxDatabase, " ", " ")
Case 2
par_area = Array(server_name, Me!cboxDatabase, " ", " ")
Case 3
par_area = Array("[local]", Me!cboxDatabase, " ", " ")
End Select
par_attribute = String(50, " ")
Call ExecuteSP(par_area, par_attribute, returnMessage, spname)
boxMessage = returnMessage
Exit_cmdDropView_Click:
Exit Sub
Err_cmdDropView_Click:
MsgBox Err.Description
Resume Exit_cmdDropView_Click
End Sub
Here an example I found which also should work using VBA: http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6
Hope this gives you an idea.
November 16, 2010 at 7:00 am
Hi,
Thanks for your answer. I can see your point.
How would you manage this situation.
ID ContactID City
1 1 Toronto
2 1 Vancouver
3 2 Toronto
4 3 Toronto
5 4 Toronto
I want any contact that has Toronto and Vancouver as a city. In this case I want Contact 1 as a result not other ones that would come up as a result if I would be using OR.
Thanks
November 16, 2010 at 9:00 am
Not sure if I understand what you are looking for exactly. Hence the following is based on a lot of guessing:
This one gives you the Contact Id for every contact who is in Toronto AND Vancouver.
SELECT DISTINCT ([ContactID]) AS myContact
FROM tblContact
WHERE ((([City]) In ("Toronto","Vancouver"))
AND (([ContactID]) In (SELECT [ContactID] FROM [tblContact] As Tmp GROUP BY [ContactID] HAVING Count(*)>1 )))
ORDER BY [ContactID];
The basis is a query which checks for duplicates (HAVING Count(*)>1). As you are only interested in the contact id the distinct is applied.
What do you think? :unsure:
November 16, 2010 at 12:11 pm
just as an aside, I would use combo boxes on your search form. Populate them with a sql query that pulls values that are in your DB and limit them to the list. This takes a big bite out of having to validate the SQL before sending it off.
November 16, 2010 at 12:30 pm
Liebesiech:
Yes this would give desired result.
Thanks for advice!
Uripedes Pants:
Yes I have combo boxes. I also allow user to build his query that I store in a temp table. Then I build WHERE clause on Front-end and I send it to SQL server.
Now I should make sure that user can have all that flexibility they want (i.e. combine with OR , AND etc.)
Thanks for input!
November 22, 2010 at 8:13 am
Hi ,
one more question.
Let's say we have:
ID ContactID City
1 1 Toronto
2 1 Vancouver
3 2 Toronto
4 3 Toronto
5 4 Toronto
6 6 Montreal
And I want my result to be
SELECT DISTINCT ([ContactID]) AS myContact
FROM tblContact
WHERE ((([City]) In ("Toronto","Vancouver"))
AND (([ContactID]) In (SELECT [ContactID] FROM [tblContact] As Tmp GROUP BY [ContactID] HAVING Count(*)>1 )))
OR City = Montreal
So result would be:
ContactID
1
6
Any idea?
November 22, 2010 at 8:46 am
Try this:
SELECT DISTINCT ([ContactID]) AS myContact
FROM tblContact
WHERE ((([City]) In ("Toronto","Vancouver"))
AND (([ContactID]) In (SELECT [ContactID] FROM [tblContact] As Tmp GROUP BY [ContactID] HAVING Count(*)>1 )))
OR [City] IN ("Montreal");
The original query is enhanced with an OR statement. If you have to included more cities, simply add them to the IN statement.
No guarantee regarding performance on large amount of data.
Cheers
November 22, 2010 at 10:28 am
Hi,
Yes this works with this statement.
But I modified mine alittle bit so it looks like something like:
SELECT DISTINCT ([ContactID]) AS myContact
FROM tblContact
WHERE ([City]) In ("Toronto","Vancouver")
GROUP BY [ContactID] HAVING Count(*)=2
So adding "OR City = Montreal" doesn't work, it gives me an error.
November 22, 2010 at 11:33 pm
Try this:
SELECT ContactID
FROM tblContact
GROUP by ContactID
HAVING SUM(CASE WHEN City='Toronto' THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN City='Vancouver' THEN 1 ELSE 0 END) > 0
It will give you the ContactID where both the city exists.
Ashutosh Karn
November 23, 2010 at 3:55 pm
There are a couple ways to do this. here is one. I created a function that takes 2 parameters. one is the name of the datalist where the information is going to be attached the second parameter is the filter I am applying. On your case you want to pass both city and state
Public Sub bindInfo(ByVal grid As DataList, ByVal UserID As Int32)
Dim strcon As String = ConfigurationManager.AppSettings("ApplicationServices")
Dim Connection As New SqlClient.SqlConnection(strcon)
Dim SQLstatement As New SqlClient.SqlDataAdapter("select fname, lname, UID from Customers where UID = " & UserID & "", Connection)
Dim info As New DataSet
Try
SQLstatement.Fill(info)
grid.DataSource = info
grid.DataBind()
Catch ex As Exception
End Try
End Sub
here is how I call it.
bindInfo(grdName, UID)
here is how I display the info
<asp:DataList ID="grdName" runat="server">
<ItemTemplate>
Hello, <asp:Label ID="lname" runat="server" Text='<%# Eval("fname") %>'></asp:Label>
<asp:Label ID="fname" runat="server" Text='<%# Eval("lname") %>'></asp:Label>.
<a href="../Account/Logout.aspx">Log out</a>
</ItemTemplate>
</asp:DataList>
You can also do this by calling a stored procedure, but I could not find a good example for you.
let me know if you have any questions
November 4, 2014 at 3:52 am
From the previous post(copied below), are DataList, Try and Catch accepted elements in VBA? Or is this not VBA?
Public Sub bindInfo(ByVal grid As DataList, ByVal UserID As Int32)
Dim strcon As String = ConfigurationManager.AppSettings("ApplicationServices")
Dim Connection As New SqlClient.SqlConnection(strcon)
Dim SQLstatement As New SqlClient.SqlDataAdapter("select fname, lname, UID from Customers where UID = " & UserID & "", Connection)
Dim info As New DataSet
Try
SQLstatement.Fill(info)
grid.DataSource = info
grid.DataBind()
Catch ex As Exception
End Try
End Sub
November 4, 2014 at 8:43 am
The code above is VB.Net.
You can see some examples on error handling in VBA here: http://www.cpearson.com/excel/ErrorHandling.htm
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply