.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

help with access query

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
hi i have table `MyTbl` that contain `date1, date2, date3`     if date1=null i need that in field memo will be 'A'     elae if date2=null i need that in field memo will be 'B'     else if date3=null i need that in field memo will be 'C' is it can made on access 2007 in query ? thank's in advance

View Complete Post

More Related Resource Links

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

How to access and query SDF file?

I get a sdf file.  I try to access it using SSMS 2005 and I get this error: You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = D:\test.sdf ] (SQL Server Compact Edition ADO.NET Data Provider) How can I find out the edition of the SDF?

CAML query to access List data across multiple site collection.

Hi   Is there any way to get the Task List data across multiple site collection using CAML query. Currently, I have the code which will retrieve the data from a single site collection. Below is the code.                           /***************************************************I am using Moss2007.Thanks!Ravi             SPSiteDataQuery q = new SPSiteDataQuery();             q.ViewFields = "<FieldRef Name=\"Title\" />" "<FieldRef Name=\"Status\" Nullable=\"TRUE\"/>";             q.Lists = "<Lists ServerTemplate=\"107\"/>";             q.Webs = "<Webs Scope=\"SiteCollection\"/>";             q.Query = "<Where>" +                                "<Eq>" +                                     "<FieldRef Name=\"Status\" />" + "<Value Type=\"Choice\">Not  Started</Value>" +                                     "</Eq>" +                         "</Where>";             DataTable dt=null;             using (SPSite s = new SPSite("http://banglabguest1:31000/"))             {                 SPWeb w = s.OpenWeb();                 dt = w.GetSiteData(q);                 w = null;             }                              *************************

is there any tool or add-on for write color and comfortable query on access-2007 ?

hi is there any tool or add-on for write color and comfortable query on access-2007 ? thank's in advance

Converting an Access pivot query to SQL 2005


Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:


SELECT tbl_Proj_Budget.PROJ_ID,
FROM tbl_Proj_Budget 
GROUP BY tbl_Proj_Budget.PROJ_ID 

1) what does the 1st line mean?


2) How would I conver this to TSQL ?




"Access Denied" Error for WMI Reader Task Query


I am using SSIS to query all servers for their disk space from a remote machine using the WMI Reader Task by providing the Administrator user credentials to access all servers. The package execution fails and I get the following error in the Execution Results pane:

[WMI Data Reader Task] Error: An error occurred with the following error message: "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))".

However, I do get the results for the Development and Staging servers but the Production servers come back with an Access Denied message. The user credentials for the Dev and Staging servers belong to the "Administrator" group whereas for the Production, the user belongs to the "Administrator" and the "User" group as well.

I am pretty much sure that it has something to do with access to running WMI queries on the servers but I could not find a forum that discussed this issue elsewhere. I even checked for the DCOM Security settings after a few searches led me to a post about "Connecting to WMI on a Remote Computer" on an msdn forum but they seem to be enabled for all environments.

My production database sits on a cluster, so does that make any difference? I have not used the WMI ta

Access Services - The query has too many output columns.


Trying to port a database from Access (2000 or older! via an intermediate upgrade to 2010) to use Access Services on Sharepoint 2010.  The main data entry table contains 41 columns of data and I'm getting this error when trying to load it as a web form: "The query has too many output columns. The maximum number of output columns is 40."

I take it the database needs re-designing to split some of the data off into separate tables to be accessed on-demand?  Or is there a setting inside Sharepoint I can tweak to get the form to display?

Inconvenient SharePoint 2010 Content Query Web Part and Anonymous Access


Morning every one

I am hoping some one has found a work around regarding this.

I am using a JQuery random 'quote' generator (http://nextconnect.blogspot.com/2009/12/jquery-random-quote-viewer.html) in my site.I have enabled full anonymous access. My issue is that it still prompts you for the login in order to see the quote generator. I have tried the "ViewFormPagesLockDown" fix but it isn't even activated on my site. And now I am at a complete loss. I did some googling (Read 5 hours) and it seems that in Sharepoint 2010 doesn't have a work around for this and I am really hoping I am wrong.


Any ideas?


Have a great friday :)


SQL query to access data using C#



I am having 2 tables A and B

Am downloading data into A row by row which contains columns Id ,date,value .B has Id,date,Location.B is present in the database.

I want to write a sql query which compares the ids of A and B and stores the info only when ids are equal.

B is acting like a filter.Can anyone tell me the code to do this using C#

Thank you in advance.

How to query a datetime field in access database using ASP.NET



I am using an Access database with one of the Table columns defined as Date/Time field.


I am trying to use ASP.NET 4.0 Web forms application to query and retrieve the row based on the DateTime field.

Here is my code snippet:

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      DateTime dt = new DateTime(dtBatchCreation.Year, dtBatchCreation.Month, dtBatchCreation.Day, dtBatchCreation.Hour, dtBatchCreation.Minute, dtBatchCreation.Second);

      //string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = " + String.Format("{0:#yyyy-MM-dd hh:mm:ss tt#}", dt);
      string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = @BatchDate";

      string s = @String.Format("{0:yyyy-MM-dd HH:mm:ss}", dt);

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      //cmd.Parameters.AddWithValue("@BatchDate", s);


How to map a datetime variable into an access ole db query


I am extracting data from an access 2003 database by date. I have built a query in the oledb editor. If I hardcode the date, the query work as such

SELECT t_PlanActuals.pa_Date, t_PlanActuals.pa_Day, t_PlanActuals.pa_DailyPlanParkers, t_PlanActuals.pa_DailyPlanMovers
FROM t_PlanActuals
WHERE (((t_PlanActuals.pa_Date)=#12/20/2002#));

I have defined a user variable called sweepdate but I cannot get the access syntax to accept it in the query string.

Can anybody tell me the correct way of passing the syntax? I tried using ? but that did not work when I tried the preview

update query in ms access using c#---- pronlem with code



i am using MS Access as my database and visual studio as front end... and am using c# as middle ware....

i am trying to use update query, to update data into a table. 

insert  and delet query had been successfully runned for me.

the error showed to me is   --- "Data type mismatch in criteria expression."


name of table: people

connection name: connmod

coloum name are: name1, contact, address1, address2, address3, groupname

OleDbCommand commod = new OleDbCommand(("update people set name1 ='" + companyname.Text.ToString().Trim() + "' ,contact='" + name.Text.ToString().Trim() + "',address1='" + address1.Text.ToString().Trim() + "',address2='" + address2.Text.ToString().Trim() + "',address3='" + address3.Text.ToString().Trim() + "',groupname='" + comboBox1.Text.ToString().Trim() + "'  where id ='" + textBox1.Text + "' "), connmod);


Query Groups when logged in with anon access



I am trying to do the code below when there is anon access.  This code is called from a class constructor which I initialize with elevated privlidges.


                foreach (SPGroup sg in SPContext.Current.Web.SiteGroups)
                    if (sg.Name.ToLower() == "my reviewers")
                        _isReviewerGroupExisted = true;

                    if (sg.Name.ToLower() == "my contributors")
                        _isContributorGroupExisted = true;




That is called from a constructor.  The class is initialized like this:


Use an Access 2007 view query as a datasource in SharePoint


I have a legacy Access database with which I would like to display filtered data in SharePoint.  Specifically I would like to write an Access view query, and display the results of that query in a SharePoint page or list. 

I cannot load the source table into SharePoint because it contains sensitive data.  Is there a way to display this query view in SharePoint without having to move the entire database into SharePoint or convert the Access backend to SQL Server?

access query with multiple table


I have three table in MDB and i want to select all record from all three tables with three where clause query here is an example please sugget


da = new OleDbDataAdapter("select production.[500_Grid], QC.[500_Grid], PDF_EDIT.[500_Grid] from Production, QC, PDF_EDIT where production.[status]='IP' and QC.[status]='IP' and PDF_EDIT.[status]='IP'" , conn);

convert Access query into SQL query




I had a application that use Access database. Now I planned it to convert into SQL. I am unable to convert a query into SQL.

SELECT company.id, company.isCaseActive, company.isActive, company.ClientName, company.OutletName, company.OutArea, company.city, company.Tregion, company.status, company3D.cnt3D, companyDrawing.cntCD, PhotoCompany.cntPC, CompanySurvey.cntCS, companyPlanogram.cntCP, CompanyLayout.cntLay

FROM (((((company LEFT JOIN (SELECT id, count(*) AS cnt3D FROM company3D GROUP BY id)  AS company3D ON company.id = company3D.id) LEFT JOIN (SELECT id, count(*) AS cntCD FROM companyDrawing GROUP BY id)  AS companyDrawing ON company.id = companyDrawing.id) LEFT JOIN (SELECT id, count(*) AS cntCP FROM companyPlanogram GROUP BY id)  AS companyPlanogram ON company.id = companyPlanogram.id) LEFT JOIN (SELECT id, count(*) AS cntCS FROM CompanySurvey GROUP BY id)  AS CompanySurvey ON c

How does one properly access AttributeExtensions from a Bing Maps Data Query Syndication Feed respon




I'm able to get the basic SyndicationFeed response from a Bing Maps QueryByArea call via WCF 3.5, however, the valuable entry data with EntityID, Latitude, Longitude etc. are not accessible (not even if I have a break point and inspect the Atom Feed responsde directly).  I just see an empty XmlSyndicationContent entry.


What do I need to do to be able to expose and access the properties in the "content" entity:

<content type="application/xml"><m:properties><d:EntityID>-8174</d:EntityID><d:Latitude m:type="Edm.Double">40.849513</d:Latitude><d:Longitude m:type="Edm.Double">-74.292831</d:Longitude><d:__Distance m:type="Edm.Double">2.72010344668636</d:__Distance></m:properties></content>


Here's a full response from Bing Maps (thanks to Fiddler2):

<?xml version="1.0" encoding="utf-8"?>
<feed xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
<title type="text"></title>
<rights t

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend