|
Good day,
I have some problem with my SQL Commands in access, really hope that you can help me out.
Category
CatID Category Name Sub- Category Description
1 Beverages 123
2 Condiments 456
3 Juice 1 help
4 Coffee 1 pls
I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.
Really hope if any kind soul could help me with this
Regards
Veon
|
|
|
|
|
Could you please show your SQL.
|
|
|
|
|
SELECT Product.[Electronic Product Code], Product.[Item Name], IIf([parent - category] Is Null,[name],[Category].[name]=[category].[Category ID].[name]) AS Category , IIf([Parent - Category] Is Not Null,[name],"") AS SubCategory, Location.Country, Location.Building, Location.[Street Address], [Tracking Record].Timestamp, [RFID Reader].[Reader ID]
FROM Location RIGHT JOIN ((([RFID Reader] RIGHT JOIN (Product LEFT JOIN [Tracking Record] ON Product.[Electronic Product Code] = [Tracking Record].[Electronic Product Code]) ON [RFID Reader].[Reader ID] = [Tracking Record].[RFID Reader ID]) LEFT JOIN Has ON Product.[Electronic Product Code] = Has.[Electronic Product Code]) LEFT JOIN Category ON Has.[Category ID] = Category.[Category ID]) ON (Location.Latitude = [Tracking Record].Latitude) AND (Location.Longtitude = [Tracking Record].Longtitude)
WHERE (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND ((Category.[Category ID])=[forms]![Search History]![cmbCat])) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Null)) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Not Null) AND ((Category.[Parent - Category])=[forms]![Search History]![cmbCat]));</pre>
|
|
|
|
|
veon cheng wrote: I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.
SELECT a.CategoryName
FROM Category a INNER JOIN Category b On a.CatId = b.SubId
WHERE b.SubCategory = 'Juice';
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Hi all,
I am trying to connect db file from a pen drive. it is fine when using on my local machine. But giving following error when trying it on my client machine. i am using vs2008 and mssql express 2008. Mssql express 2008 also installed on my client's machine.
Type of application: Window application.
Error:Failed to generate a user instance of sql server due to a failure in startng the process for the user instance. the connection will be closed.
i have deleted mssqlexpress folder form the location:
C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\
and restarted machine, but still getting the same error.
Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
if i remove the "User Instance=True" from the above connection string it gives me following error:
"Create database permission denied in dataase 'master' an attempt to attach an auto named database for file failed. A database with the same name exists, or specified file cannot be opened, or it is located on usc share."
Kindly help me.
Thanks
rmshah
Developer
|
|
|
|
|
r_mohd wrote: if i remove the "User Instance=True" from the above connection string it gives me following error:
Do you need a user-instance, or not?
Microsoft SQL Server 2005 Express Edition (SQL Server Express) supports a new feature, the user instance, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. Each instance runs under the security context of the individual user, on a one-instance-per-user basis.
I assume that Sql Express or Sql Server is installed on the machine? Can you post the stacktrace of the exception?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
r_mohd wrote: I am trying to connect db file from a pen drive.
That needs more explanation.
You have at least the following parts
1. MS SQL Server
2. The location of the MS SQL Server files.
3. The Visual Studio application.
Is the above list a COMPLETE list of everything that you are attempting to run?
From the above list what EXACTLY is on the pen drive?
From the above list what EXACTLY is NOT on the pen drive?
r_mohd wrote: i have deleted mssqlexpress folder form the location:
As stated that is just flat out wrong. You can't just delete that directory.
If SQL Server can't see the database then nothing you do in Visual Studio is going to fix that.
r_mohd wrote: Connection string: " Data Source='" & (SqlServerName) & "';AttachDbFilename=" & driveName & "dbfolder\dbname2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Certainly looks like a back slash is missing to me.
|
|
|
|
|
Thanks jschell,
MS SQL Server is installed on client's machine.
The location of the MS SQL Server files. it on client's machine(C drive) and data file and log file lying on pen drive.
The Visual Studio application is lying on client's machine(c drive).
The connection string is fine as it works on my machine.
i am using a pen drive for data file and log file.
Thanks again.
rmshah
Developer
|
|
|
|
|
r_mohd wrote: and data file and log file lying on pen drive.
And you can go into SQL Server Management Studio and create a database using that drive? On your box? Notice that has nothing to do with your application. It is just a test to insure that SQL server accepts that.
|
|
|
|
|
Hi friends. I have a small software that uses an access database. I use it at home but i wonder if i can use it from anywhere by reaching the db over internet. Should i change any code to do so. By the way if Access doesnt support this function, i can replace my db with sql server.
|
|
|
|
|
Bad idea; Microsoft Access is not meant to be used as a server, and it ain't as secure as Sql Server. I'd even recommend against using Sql Server over the internet, would be safer to have IIS on the same server and provide access to the DB using webservices.
I can imagine you don't want to "waste time" with writing a useless layer. If my memory serves me correct, you'd open port 1433 on the firewall. Updating from Access to Sql Server should be easy; if you open the DB in Access, you'll have a button in that lint that says "upgrade to Sql Server" - that launches a wizard that recreates all your tables in Sql Server.
Hope this helps 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
leone wrote: I use it at home but i wonder if i can use it from anywhere by reaching the db over internet.
As stated no.
MS Access is a file based server. Thus if you have software that uses MS Access then the file must be visible in file system of the client machine.
Thus if you want to run the software on machine A then on A you MUST be able to open explorer and see the database file.
That means that you would need to enable file sharing over the internet. You might be able to do that depending on the machines at either end and how they are connected.
leone wrote: i can replace my db with sql server.
MS SQL Server is accessible via the internet as long as the server machine is accessible. That is not guaranteed (machine is visible) but that is a different discussion.
It is possible to secure MS SQL Server so it can be exposed on the internet however either you must presume that your data is not secure and not important or you are going to need to learn a lot more about internet security and SQL Server before using it. It probably isn't worth learning that since it isn't a skill that businesses want.
|
|
|
|
|
Thank you friends for your answers. I decided to hire a server machine and put everyhing on it.
|
|
|
|
|
Don't understand why this was downvoted, (over)compensating.
|
|
|
|
|
Access can only be used remotely on MS Sharepoint websites, but you could uses Access as a data store behind a hosted web application (though I would suggest the use of SQL server).
Alternatively, you can use Access as the front end to a hosted SQL server database, so your user interface is local, but links to the remote database.
Many web service providers offer MSSQL and/or MySQL databases
====================================
Transvestites - Roberts in Disguise!
====================================
|
|
|
|
|
Hey.. I am developing an windows form application using ms sql server 2008. and i want to connect my sql server to cloud at internet to use it as backup. if there is any changes made bu application in local server then it should be reflected on cloud also. and if there is any loss of data at local server then it should get backup automatically from cloud.
Can someone help me in this please.. ??
|
|
|
|
|
You seem to be describing replication. See if your cloud provider will support that type of configuration. Sounds like overkill to me.
You can build a very robust database server configuration locally and maybe use the cloud to store your database backups. Before shipping the databackups to the cloud you might want to investigate data encryption. I don't know the type of data your are working with, but I wouldn't want my personal information stored in the cloud without being encrypted.
|
|
|
|
|
KUNWAR999 wrote: Can someone help me in this please.. ??
You already received help in the C# forum. Pete posted the best answer, as far as I can judge.
Did you try his answer, what where the results, and why are you repeating the question? It'd be appreciated if you at least included a link to the prior question, otherwise I'll simply start copy/pasting the old answers to your question. Same question -> same answer.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
I'm trying to send an email using a SQL procedure within SQL Anywhere 11. I have the following SQL statements to do this:
ALTER PROCEDURE "DBName"."WebFormNotify"()
begin
call xp_startsmtp('*A*@***.org', 'SMTP_Relay_server_name');
call xp_sendmail(recipient = '*B*@***.org',subject = 'SMTP SYBASE TEST',"message" = 'SMTP SYBASE TEST');
call xp_stopsmtp()
end
This is being ran from the server that has SMTP Relay setup on it. I am able to use telnet and send an email successfully. The procedure runs with no errors but the email is never recieved by the recipient. I have also replaced the second parameter passed into xp_startsmtp with the name of the actual SMTP server itself, but still no email was recieved by the recipient.
Any help on this would be really appreciated. Pleas elet me know if additional information is needed.
Thank you,
Mel
|
|
|
|
|
I'd start by capturing the return code from the calls. The MAPI errors will likely tell you what is failing. At a guess, it is possible that some form of authentication may be necessary to get this to work.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
xp_startsmtp is a system procedure that starts a mail session for a specified email address by connecting to an SMTP server. This connection can time out. Therefore, it is recommended that you call xp_startsmtp just before executing xp_sendmail.
If you specify smtp_auth_username and smtp_auth_password, and the server does not support the SMTP authentication capability, error code 104 is returned.
Virus scanners can affect xp_startsmtp, causing it to return error code 100. For McAfee VirusScan version 8.0.0 and later, settings for preventing mass mailing of email worms also prevent xp_sendmail from executing properly. If your virus scanning software allows you to specify processes that can bypass the mass mailing protections, specify dbeng12.exe and dbsrv12.exe. For example, with McAfee VirusScan you can prevent mass mailing by adding these two processes to the list of Excluded Processes in the Properties area.
Documentation[^]. Which error-code is it returning btw?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I am creating report for displaying continuous absent for more than 2 days.Query is build and working fine.Range is passed by using @StartDate and @EndDate. but problem is if In between two absent week off comes then it is also considered as continuous absent.i want to add that functionality to the existing query.How can i do that?
Existing query is:
WITH Dates
(
EntryDate,
EmployeeNumber,
Status,
Days,
EmployeeCode,
EmployeeName,
DeptName,
JobName,
HOD,
Supervisor
)
AS
(
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
1,a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a
WHERE
a.[Date] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O')
UNION ALL
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END,
a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a
INNER JOIN
Dates parent
ON
datediff(day, a.[DATE], DateAdd(day, 1, parent.EntryDate)) = 0
AND
a.EmployeeID = parent.EmployeeNumber where a.[Date] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O')
)
SELECT * FROM Dates where days>=2 order by EmployeeNumber, EntryDate
|
|
|
|
|
You may need to build a calendar of work days. Saw a link a couple of days ago but do not remember where. 
|
|
|
|
|
WITH Dates
(
EntryDate,
EmployeeNumber,
Status,
Days,
EmployeeCode,
EmployeeName,
DeptName,
JobName,
HOD,
Supervisor
)
AS
(
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
1,a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a
WHERE
a.[Date] between @StartDate and @EndDate and (a.status=
-- RECURSIVE
UNION ALL
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END,
a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a
INNER JOIN
Dates parent
ON
datediff(day, a.[DATE], DateAdd(day, 1, parent.EntryDate)) = 0
AND
a.EmployeeID = parent.EmployeeNumber where a.[Date] between @StartDate and @EndDate and (a.status=
)
SELECT * FROM Dates where days>=2 order by EmployeeNumber, EntryDate
I have added AND datepart(dw,a.[Date]) NOT IN (1,7), which will excludes weekly off days.
this would work perfactly.
|
|
|
|
|
i have three comboboxes and a sub form whose recordsource is from same table.these both and a save button are present in a main form .according to combo box selected values the sub form displays the corrresponding data. i can edit the sub form and when i click on save button the values in the combo boxes and corresponding data in sub form should be updated in the msaccess table from where they come..can any body tell solution to this?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
