|
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
He said he wanted to see the null values for when records are not found, and your inner join will simply disregard (not display at all) the rows that have no correspondence.
EDIT: I've upvoted your solution, as it seemed to be what the OP wanted.
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
modified 26-Sep-12 3:14am.
|
|
|
|
|
Andrei Straut wrote: Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
Of course! You are absolutely right! Thanks!
------------------------------
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.
|
|
|
|
|
You're welcome
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Hello All,
I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".
I tried
select convert(char(10),'2012-09-16T20:08:30Z',101) - but this did not work.
Is there any other method to try before trying to write a whole bunch of casts, replaces!
|
|
|
|
|
vanikanc wrote: I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".
Doesn't seem a valid datetime format to me
As a string-function use:
SELECT LEFT('2012-09-16T20:08:30Z', 10) ...
With values in datetime-format:
SELECT CONVERT(DATE,@datetime)
------------------------------
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.
|
|
|
|
|
I Would like to ask about sql server commands and materials,could you provide me some topics related to sql server
Thanks,
|
|
|
|
|
MSDN SQL Server Developer Training[^] is a good place to start.
But you didn't Google[^] that, did you?
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Start from here:
http://www.w3schools.com/sql/default.asp[^]
http://www.microsoft.com/en-in/SQLserver/default.aspx[^]
http://learnsqlserver.in/[^]
Start to learn about:
- Various query types - CREATE, UPDATE, DELETE, ALTER, SELECT, INSERT, GROUP BY , HAVING etc
- Various query functions - COUNT, LENGTH, LTRIM, RTRIM, SUBSTRING, etc
- Various query statements - Distinct, Union, Union all, Intersect, Order by, etc
- Various query grouping and execution - Store procedures, Trigger, Cursor, Views, etc
Try!
|
|
|
|
|
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.
|
|
|
|
|
Sorry its kinda messy, its from access.. the bold part is the 1 have issue. once againg sorry
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.
|
|
|
|