Need To Convert This Sql Query For Access To Identical Sql Query For Sql 2005..
My code retrieves a username and a password from a form. Then this information is compared to some usernames and passwords that are stored in a database. The important thing here is that the comparison must be case sensitive meaning that "passWord" is not the same thing as "password"
I have this code, working fine in access 2003
SQL = "SELECT * FROM users WHERE StrComp(username_column,'" & entered_username_in_form & "',0) = 0 AND StrComp(password_column,'" & entered_password_in_form & "',0) = 0"
but get the following error when I run it against my sql 2005 database.
[Microsoft][SQL Native Client][SQL Server]'StrComp' is not a recognized built-in function name.
I don't know the corresponding t-sql for the query.
View Replies
ADVERTISEMENT
I am using ASP/MS ACCESS to see how I can query the same database, via 2 formfields.
{name: - search}Textfield 1: - Search by Category
AND/OR By
{name: - searchT}Textfield 2: - Location
Currently,
strSearchwords = Trim(Request.QueryString("search")); where "search" is the name of Textfield1
Which is fine, but how can I set it so that on Submit, the string from search, and searchT are somehow joined together into one string?
View Replies
View Related
I'm trying to run an access query with one parameter that is a string. I can call a query that has no parameters. I can also call a query that has one parameter that is an integer. But the one that has a string parameter isn't working.
Here is the code:
[VBS]set rs = conn.execute("getArtists '" & ltr & "'")[/VBS]
I've tried using a command object and it did the same thing. Is there any other way I can get my query to return the records. Running it in access works.
View Replies
View Related
I have a Acces query which creates a table using manipulated data from another table. I want my asp page to run this query after doing an update to the database.
View Replies
View Related
I've got a query that works fine when used in mySQL but completely fails
when I try to apply it to MS Access, weird thing is that it is just a copy
of a stored procedure that works fine in the same copy (with the
Request.QueryString replaced by a fixed value) ! Any explaination would be
great, ID is a number BTW :
SELECT page_title,ID,page_content FROM content WHERE ID='" &
Request.QueryString("ID") & "' '
Had a look at aspfaq.com but couldn't find anything that applys.
View Replies
View Related
Is it possible to write an SQL statement that reads from an Access Query instead of an Access Table. If so is the syntax the same as for reading a Table.
View Replies
View Related
How can you use ASP code to execute an Access query. I want to run the query to then display updated data on the webpage.
View Replies
View Related
Where I found this?
Like this:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS
( http://msdn.microsoft.com/library/e.../access2sql.asp )
But with explanations (usage) and samples...
View Replies
View Related
I have a field called store, I have more than 1 row with the same store name in the store field.
I need to coolect all the stores from the store field, remove any duplicates, like I might have buy.com twice and only need it to show once in the drop down. And sort ascending (by store name).
Summary.
Database is called: tracker
The table (and the only table) is called merchants
I only want to pull data from the field store, remove duplicate store names, sort ascending.
I can figure out how to plug the required htm around the asp code, to auto populate the drop down.
View Replies
View Related
I'm using odbc and Classic ASP and I want to read the sql content of a query in an access database. ANyone have any clues?
View Replies
View Related
I am trying to query an Access database using the like operation; however, when I test the query in the browser all records from the database are displayed. Here is my code:
View Replies
View Related
I want to do a running total query , here is what i want :
I want the running total like this:
View Replies
View Related
I'm attempting to limit a query which is being ordered by a date field to 10 records. The date field contains duplicate values, which are sorted from most recent, back.
However their suggestion of sorting by a secondary field is not providing acceptable results (it returns all the rows in the database). Does anyone have any suggestions of another way around this?
View Replies
View Related
What is the best way to create new queries and/or modify existing tables within an online access database. At the moment, I am periodically downloading the db and making alterations. But, I have heard others on the list say they use a query analayzer built on
a <form> where they execute SQL statements. Is this the correct way to go?
View Replies
View Related
I have the following query:
Update properties set Last_Change=#8-Mar-2006# Where properties.fdref='h3c000';
which works fine when I run it on my desktop under MS Access, but when I run it as an ASP page, I get the following error message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
I am using the same database bot online and offline so field names etc are exactly the same.
View Replies
View Related
I have an Access database that I created a query in. The query brings fields from related tables and prompts users to perform a search based on 3 criterias.
NOTE: the prompts can be bypassed by just leaving it blank and selecting OK. So in other words, the user does not need to fill in each prompt for the search to work, it just give them a more narrowed down search if they fill more in...
Now for the ASP part... I want to create an ASP page where the use selects from 3 drop downs. These drop downs are named the same as the field names in my query. How do I get this to work??
View Replies
View Related
Currently, I do the following in my ASP pages when making use of an MS
Access database:
Dim adoCon, rsSet, strSQL
Set AdoCon = server.CreateObject("ADODB.Connection")
Set RsSet = Server.CreateObject("ADODB.RecordSet")
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("MyDB.mdb")
strSQL="SELECT * FROM MyTable;"
rsSet.Open strSQL, adoCon
...do some stuff...
rsSet.Close
adoCon.Close
Set rsSet=Nothing
Set adoCon=Nothing
I have a couple queries stored in the database that I use when in MS Access.
Can I call these queries instead of using a string to specify the SQL
command?
The above seems kind of verbose for making use of a database. Can it be
simplified at all?
View Replies
View Related
here's what i want to do. from an asp page, perform a search on a
table in access. i have used sql code in the asp page itself, but i'd
rather execute a query in access. i have success in running any query
(basic SELECT, SELECT with conditions _other_ than LIKE, etc..) for
some reason, when i execute the query below from the asp page, i get
no results.
the search.asp page just has a text box in a form that submits the
srchBOX field to the results.asp page.
here's the asp code from the results.asp page: Code:
View Replies
View Related
I am trying to search through an access database for tickets createded with in a certin date. I am using the below code.
strdate = DateAdd("d", -2, Now())
sqlstr="SELECT * FROM Incidents WHERE TicketDate BETWEEN #"& Now() &"# AND #"& strdate &"# ;"
I verify the query with response.write and get the following.
SELECT * FROM Incidents WHERE TicketDate BETWEEN #15/07/04 19:19:32# AND #13/07/04 19:19:32# ;
I know there are 100% tickets within this date. Yet my query returns nuthing. Please help i've searched this forum for other peoples code looking for examples but end up with the same result.
View Replies
View Related
What is this all about? Generally I run against SQL Server but now I have to use access. The query I want to run is a simple double join
SELECT tblArtists.*,tblGenres.name AS genreName,tblPages.pageId
FROM (tblArtists
INNER JOIN tblGenres ON tblArtists.genreId = tblGenres.genreId)
LEFT JOIN tblPages ON tblArtists.artistId = tblPages.artistId
ORDER BY surName ASC, firstName ASC
Now If I run this question in access against the database it accurateley returns two posts. BUT When I paste the same query into an asp document and runs it with a fileDSN against the same database it returns nothing!? Is this some kind of joke from Microsoft?
View Replies
View Related
I am running a querry on an access database and have set the number of records/page displayed at 20. if there are more than 20 records returned, then 1st page will show the first 20, the next page will show next 20 and so on....
The trouble:
the count of total records displayed is correct and the first page is displayed correctly. But when i click on *Next* to go to the next page, all the records of the database get displayed (not the 2nd page of records from the query).
View Replies
View Related
In Access you use "*" + [passed variable] + "*", + can be replaced with &
Calling a parameterized query in Access requires % be used in place of *,
however, all that I have read show dynamic SQL passed to Access:
WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
However, my call is similar to:
conn.qMyLookup strVar, rs
If I modify the query in Access to:
"%" & [passed variable] & "%"
I get all records. If I only put it at the end, as suggested, I only get
matches at the end, not throughout the column. Code:
View Replies
View Related
I am about to migrate my asp web pages from a previous well functioning connection with Access 2003 to SQL Server 2005 Express Edition instead.
I have created a database in SQL Server called "test_db". As far as I know I have not set any particular user id or password for the database.
In this microscopic database I have onnly a single table called "name" and in this table I have two columns called "fname" and "lname". I have two records stored in the name table. What I want to achieve is simply to display these two records in my asp-page.
How can I achieve this and what am I missing here?
View Replies
View Related
Getting an error using ASP with an Access database on a remote host (I don't control perms).
It's giving this error:
Microsoft JET Database Engineerror '80004005'Operation must use an updateable query. /admin/projects.asp, line 180
Isn't this normally just a permissins error?
The host says they've just given full permissions so it shouldn't a problem, but I want to confirm that this is a perms issue before I get too uppity.
It happens on every INSERT/UPDATE/DELETE that is attempted to be run, and the exact same SQL statements work perfectly on the local machine and network, so my gut instinct is perms.
View Replies
View Related
The setup:
Access database of vendors. A vendor can offer one or more services.
ASP page provides user a checkbox of services. User clicks one or more options and submits. The database should return all vendors that match the services selected.
No dice. I can of course easily pull up vendors that match one service. But the multiple options are treated as an AND search. I don't want to find only vendors that provide all the services checked, but all the vendors that provide one or more of the services selected by the user.
I have tried stored procedures in Access and SQL statements. I either get data type errors or nothing found.
View Replies
View Related
Not that much experience working with .asp, but I have managed to create a project.
This project currently has a DB in Access.
I'm moving it over to MySql.
Was able to ODBC import it and all data seems gtg.
Uploaded a test folder, and modified my connection so I can test the MySQL database before I change over.
I try running the pages, and have been making changes to the code as I see errors come up.
All was moving forward until now.. where I just keep getting 500 errors, and can't get it to tell me what's wrong.
I use Option Explicit, and all my variables are posted.
Is there any other code I can put into my pages so that it will not just generate a 500 message when there is something wrong?
Or any Software programs I can get which will help me troubleshoot my code for MySQL?
View Replies
View Related
how to convert excel contain japanese text to access format, and allow the japanese text converted to access allow to display in the web browser? Because when i try to display the text inside my browser, all the japanese text become ?
View Replies
View Related
how do you do this. 2 tables. one called "Bookings" one called "Rooms"
what kinda query would i use to make it LIST the contents of the ROOM column in the BOOKINGS table, then get remove identical matches in the ROOM column in the ROOMS table?
View Replies
View Related
Is it possible to write sql query to fetch records from
specific record number?
lets say i have one variable and it stores values such as
1,2,3 or so...
if variable contains, i want to fetch records from 1 to
20.
if it contains 2, then records should be from 21 to 40.
for 3, records would be 41 to 60...
what could be the sql for that?
i dont wannna fetch whole table and then filtering
through code..
View Replies
View Related
i have a table called checkrenewal
it has a field called date_renewal
data is stored like this
03/20/2007
05/17/2008
10/21/2006
09/18/2006
09/04/2006
so what i want to do is display only records that are in month of sept for 2006
so there will be 2 records
so next month is Oct 2006 so only display one record
so depending on which month and which year it is display only those records.
View Replies
View Related
I can't write the sum of a certain colum in a table of a db.
<%
sql_Sum_Tax="SELECT SUM(vtax) AS sql_Sum_Tax_RS_Var FROM orderstats
WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";"
Set sql_Sum_Tax_RS = Server.CreateObject("ADODB.Recordset")
sql_Sum_Tax_RS.Open sql_Sum_Tax, conn1
%>
<%=sql_Orders_Placed_RS("sql_Orders_Placed_RS_Var")%>
That last line generates this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
View Replies
View Related
i have a table and the fields are
gender
weight
points
the records for example are
Male 55 2.0
Male 60 3.12
Male 65 3.87
Male 70 3.99
so i need to find out the points when the user selects gender and enters the weight
people are allowed to enter weights between 55 and 70
say for example someone enters weight as 55 his points will be 2.0
if someone enters weight as 64 i want the points to be 3.12
View Replies
View Related
I have an Access database of literature that I want to search using an ASP page using multiple search criteria (author, title, year, discipline,etc).
I'm using a form to collect the criteria from the user and then sending it to an ASP page. I'm creating the SQL statement using variables that pick up values passed from the form.
Code:
strSQL = "SELECT * FROM Literature WHERE " & _
"(Author Like '%" & mauthor & "%') AND " & _
"(Title Like '%" & mtitle & "%') AND " & _
"(Journal Like '%" & mjournal & "%') AND "& _
"(ArticleBook Like '%" & mBookOption & "%') AND " & _
"(Year Like '%" & myear & "%') " & _
"ORDER BY Title ASC;"
This would probably work if I used "OR" in my SQL, but I want to be able to use "AND." My problem is that I'm not getting records that have null values in some of these fields.
View Replies
View Related
