Pass Through Query From MS Access

Jan 2, 2008

I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.

Is thier a way to pass parameters to a pass through query from MS
Access?

SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
AS [Signed PP]
FROM dbo.tblPersActionLog INNER JOIN
dbo.tblPersActionHistory ON
dbo.tblPersActionLog.PersActionID =
dbo.tblPersActionHistory.PersActionID
WHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND
(dbo.tblPersActionLog.Rejected = 0) AND
(dbo.tblPersActionLog.IsPayAction = 0) AND
(dbo.tblPersActionHistory.ActionTypeID = 5) AND
(dbo.fn_IsParACorrection(dbo.tblPersActionHistory. PersActionID) = 0)
AND

([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)
AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND
(dbo.tblPersActionHistory.ItemDTG <= @EndDate)
GROUP BY
[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)

View 2 Replies


ADVERTISEMENT

SQL Pass Through Query Runs Twice From Access

Jul 18, 2007

Hi All,



Recently we started using Pass throughs to perofmr large inserts, however we have started to notice that some of these pass throughs are executing twice, and therefore duplicating data.



Is this a known bug, and if not has anyone got any advice on what could be causing it?



We're connecting from Access 2002 (SP6) to SQL Server 2000 Enterprise.



The conenction string / code is as follows :-



Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.Properties("Jet OLEDBDBC Pass-Through Statement") = True
cmd.Properties _
("Jet OLEDB*** Through Query Connect String") = _
"ODBC;DSN=" & myDatabaseShort & ";DATABASE=" & myDatabase & ";UID=sa;PWD=" & Left(myDatabaseShort, 4) & ";"



It connects fine. My sql string is a straightforward Insert statement that only executes once via SQL Query Analyzer.



I'm calling the pass through using the following lines of code:-



cmd.CommandText = mysql
cmd.Execute



Can anyone see anything obvious that I'm doing wrong, or is this a known issue?


Cheers

View 5 Replies View Related

Troubleshooting Slow Pass Through Query From Access

Jun 29, 2007

One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.



We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.



First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.



I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.



Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?

View 1 Replies View Related

Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them

Mar 17, 2008

Access Connection

create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!

Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db

Any ideas please help

thanks in advance
Dave

View 2 Replies View Related

Parameterized Pass-through Queries From Access Front-end?

Jul 23, 2005

Is there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.

View 1 Replies View Related

Crystal Reports And ODBC Getting Access Denied On Pass Thru

Jul 27, 2006

Hello,



Environment - Windows 2003 standard server SP1, SQL 2000 SP4 (installed mixed mode)

Goal: To have Crystal reports run via odbc dsn using windows authentication.

Big Honkin' Problem: I should say that if the users are local admins on this server then the windows authentication works just great, if not a local admin then it doesn't work. SQL gives an error access denied. (this shows in the logs btw.. to the end user crystal displays a generic database error) Since windows authentication failed, I then decided to use the dbo and switch to SQL auth. This dbo did not have a password and everything ran great. Then the IT dept. asked me to give the user a password, so I did. Then everytime Crystal opened, a window would appear asking the user to type the database , user and password. In Windows everytime this was done the crystal control files would change ownership to whomever entered the information making the report useless to any other user until I manually went into the file properties and changed it back. I develop these reports thru an VPN connection and on my desktop I said phooey on re-entering the password 50 zillion times while developing so I opened the dsn and put the PWD line in and it ran great. So we figured why not put this on the server - the password does not pass thru to SQL, even as admin user. The final problem that came up in relation to this is that I then created another user in SQL with no password so the reports would run. I developed a report that takes the current user credentials and passes it to the report via dde link. On my machine running over the VPN and launching the database program (GoldMine) over the WAN the report did everything it was suppose to. I high-fived myself and installed it on the server where the users would launch it and the dang thing wont pass the credentials over to the report.

I know there is not much to SQL security - it's either SQL or mixed mode. I have it on mixed mode and I know for fact it runs because Goldmine runs. I have to have some sort of security 'thang' in Windows 2003 somewhere that doesn't allow the passord to move and SQL only sees a blank password.



If any body can give me any help - I would so greatly appreciate it.



thanks

View 1 Replies View Related

Help Selecting A Value From SQL To Pass To Query

Mar 25, 2008

I am trying to do the following.  I am capturing the logged in username from Windows Authentication then stripping the domain name using the following codeProtected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting        Dim Bareusername        e.Command.Parameters("@strusername").Value = My.User.Name        Bareusername = My.User.Name.Replace("MyDomain", "")        e.Command.Parameters("@LoginName").Value = Bareusername    End Sub I use the derived value as the where clause in a select query Select * from Master Where AccountName = @LoginName.  This allows a user to only see their own information in DetailsView1.  This is working as desired. I have another column in the Master table named ID.  This ID is the key that I need to reference to allow users to see and edit data in other tables.  So I would like to be able to do something like Select ID from Master where AccountName = @LoginName and be able to pass this value to  a Select statement against another table(Awards) for a DetailsView2 on the same page. Alternatively the value that I am trying to query is in the first row of DetailsView1.  Is it possible to access the value in that field and pass it in the query for DetailsView2?  This would be simpler if possible.I am not sure how to go about this and would appreciate any guidance that you could provide.  I have found a lot on the web regarding this based on a selected value but not through the method that I am trying to use.  Thanks in advance,Ken  

View 4 Replies View Related

Pass Through Query With Parameter???

Dec 13, 2000

I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:

"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."


There is nothing wrong with the table name.
I cant understand what the problem is ?????

Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.

Thanks,
Ruaidhri



Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb

Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400

dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function

View 1 Replies View Related

Can We Pass Some Info From Sql Query To Dos

Oct 3, 2004

suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time

View 14 Replies View Related

Pass User Name As A Parameter In A Query

May 13, 2007

just getting started with my first db driven web project...
I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.
How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:
<asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"    ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"    SelectCommand="SELECT c.CourseName FROM courses c, enrolments e        WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses">    <ItemTemplate>        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br />    </ItemTemplate></asp:DataList>&nbsp;</p>
However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.
Suggestions pleeeeeese!!
 

View 1 Replies View Related

Pass The Mulitple Values To A Query

Jun 12, 2001

Hi!

Can anybody help me in solving this problem

SCRIPT

declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)

RESULT

----------
1,4,9,10

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.

I want to pass a list of values to a column of DATA Type smallint.

Thanks in advance.

View 1 Replies View Related

Pass The Mulitple Values To A Query

Jun 13, 2001

What is the way to pass the values throug varibale as shown below?

SCRIPT

declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)

RESULT

----------
1,4,9,10

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.

I want to pass a list of values to a column of DATA Type smallint.

View 1 Replies View Related

Do Pass Thru Query Connections Persist?

Oct 18, 2006

I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.

View 9 Replies View Related

Pass Parameters To A Query Using Subquery?

Apr 23, 2012

I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)

Example

table1
id - autonumeric
id_user - id from user
dates - date of register

table2
id - user id
name - user name

query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;

subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;

This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?

View 3 Replies View Related

Pass Date To Sp In Query Analyzer

Mar 30, 2007

I am trying to run an sp in query analyzer like this. It expects two parameters a number and a date.

Exec "spGetCancelleddates" "351", "30/03/07"

However I get an error on the date

Error converting data type nvarchar to datetime.

How should I pass this parameter ?

TIA

View 3 Replies View Related

Need To Pass User Input Through To SQL Query

May 20, 2008

Hello Everybody,

I am sure this has been asked a thousand times before in a thousand different ways, but here goes...

I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.

Thanks in advance for your time and patience,

Rocky Mountain Tech

View 1 Replies View Related

How To Pass Results Of A SQL FOR XML Query To An XML Task?

Aug 15, 2006

How do I pass the results of a sql 'FOR XML' query to an XML Task? I tried using a user variable of type string. In my Exec SQL Task on the General tab I set the ResultType to XML. Then on the Result Set tab I map it (Result 0) to my user variable (string). Then in the XML Task I use that variable as the Source. However, it fails in the Exec Sql Task with error
"End tag 'CustomJob秱孒秱怈пField' does not match the start tag 'CustomJobField'

It looks like some extraneous chars are getting inserted into my tags? 
I also tried setting the ResultType to SingleRow but that failed with error:

"The type of the value being assigned to variable "User::ForXmlResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

Any ideas?  Thanks.

View 2 Replies View Related

New To SSRS - Pass Query Paramater At Runtime

Jun 21, 2007

Hi all,

Im new to SSRS, basically I have created and deployed a report to my reporting server.

Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.

Thanks for your time.
Pace

"Impossible is Nothing"

View 1 Replies View Related

How To Pass Array (query Parameter) Into Stored Pr

Jan 31, 2008

how to pass array(query parameter) into stored procudure. with this array i need to retrive data,with another array i have to retrive another set of data.
eg: suppose @param1 contains chapter1,Chapter2,Chapter3,
@param2 contains unit1.1,unit2.1,unit2.2,unit3.1,unit3.6
how can i do in stored procedure

love all

View 1 Replies View Related

New To SSRS - Pass Query Paramater At Runtime

Jun 21, 2007

Hi all,

Im new to SSRS, basically I have created and deployed a report to my reporting server.

Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.

Thanks for your time.
Barry Andrew

View 3 Replies View Related

Pass A Variable To A Linked Server (FoxPro) Query

Nov 22, 2004

I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.


Here is the code:
-------------------------------------
DECLARE @LastDate datetime
SELECT @LastDate = MAX(DateChaged)
FROM tblPersonel
WHERE ACTIVE IN( 'A', 'T')

1. I tried:
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')

This line gives me an error msg:

Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]


2. I tried to use CTOD() - FOXPRO function to convert character to date.

SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')

-this doesn't give any error, but doesn't limit the result set as it should.

Thanks all.

View 2 Replies View Related

How To Pass Pass The Parameter In SQL Command In SSIS Package

Jul 31, 2006

Hi

   We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

 

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )



 

Query 2: (look up )

 


SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')  AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')



 

please anyone give the sample control flow  and how to pass the parameter?

 

Thanks & regards

Jeyakumar.M

 

View 1 Replies View Related

Pass String Into Passthrough Query - Multipart Identifier Could Not Be Found

Mar 28, 2013

I have a function in VBA (Access) that passes a sql string into a passthrough query that is server side executed

I changed the inner joins and left joins to where statements but this particular sql string crashes with the titled error folowed by the multi-part identifier "task_backup.activityid" could not be bound (#4104) and then it repeats that one more time in the same message

Which i think the second "sub message" is for the null criteria

INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line)
SELECT [TASK Excel Data].[Activity ID],[TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], [TASK Excel Data].Area, [TASK Excel Data].Line
FROM [TASK Excel Data]
where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] and TASK_BackUP.[Activity ID] Is Null

View 2 Replies View Related

Pass-thru Query Doesn't Work With A SQL 2005 SSIS Package

Dec 14, 2007

I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:

Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".


What am I doing wrong?

View 4 Replies View Related

Reporting Services :: Pass A Parameter To LDAP Query In SSRS

Aug 20, 2015

Is it possible to use a parameter in a ldap query using the ADsDSOObject provider?  I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider".  Command parameters are unsupported with the current provider.

I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design.  Here is my query for my dataset.  If I hard code an example it works.  Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of.  Is this possible? or do I just need to push for a linked server?

="SELECT sAMAccountName, displayName, distinguishedName " +
"FROM 'LDAP://DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' " +
"WHERE objectCategory = 'Person' " +
"AND objectClass = 'user' " +
"AND memberOf = '" + @GlobalGroup + "'"

View 3 Replies View Related

Update Query In Ms-access Doesn't Workin C#, But Does Work In Ms-access

Apr 18, 2007

Hi,

I have an application that uses following code:



Code Snippet







using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;

//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}

//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;

//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);

SelectProjectID.Parameters.AddWithValue("@Project", m_Project);

try
{
//open the connection
conn.Open();

OleDbDataReader Dataset = SelectProjectID.ExecuteReader();

while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);

SelectActionID.Parameters.AddWithValue("@Action", m_Action);

try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();

while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}



//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);

Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);

try
{
Query.ExecuteNonQuery();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}

Code Snippet



The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.

What am I overseeing here?
--Pascal

View 13 Replies View Related

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

Can I Access MS Access Table In A Select Query Of SQL Server

Nov 29, 2006

Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.

Ex.:

MSAccessTable (in file.mdb)



col1
col2

a1
a2

b1
b2

SQL query in SQL Server:

SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;

Thanks,

View 3 Replies View Related

Urgent Urgent Please.(Access SQL Pass Through Queries)

Jul 6, 2000

Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;

It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))

Thank you very much for your concern.

View 2 Replies View Related

Update SQL 2000 Query (converting An Old Access 2k Query To SQL)

Mar 30, 2006

Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR

View 2 Replies View Related

MS Access Query That I Would Like To Convert To MS SQL Server Query

Jun 28, 2005

I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.


Code:

SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate

View 3 Replies View Related

Convert Access Query To Sql 2000 Query

Jan 30, 2008

I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:

IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))

Here is what I have, but I'm not confident it is correct:
CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket

View 1 Replies View Related

A For All Query In MS Access?

Dec 7, 2004

I have three tables, Equipment, LookUpGroups, and EquipmentGroups.

Equipment
----------
TagNumber
LocationCode

LookUpGroups
-------------
GroupTagNumber
TagNumber

EquipmentGroups
----------------
GroupTagNumber

I want my query to return the GroupTagNumber ONLY IF all of the TagNumbers in that group have a given LocationCode (Given by User).

Example:
GroupTagNumber: G1
TagNumber: A1
LocationCode: 1

GroupTagNumber: G1
TagNumber: A2
LocationCode: 2

GroupTagNumber: G2
TagNumber: A3
LocationCode: 1

So if the user enters LocationCode 1, then he should only get G2 back.

I've started by trying to do a count query:

Code:

SELECT tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
FROM tblEquipmentGroups, tblEquipment, tblLookUpGroups
WHERE tblEquipmentGroups.GroupTagNumber = tblLookUpGroups.GroupTagNumber
AND tblEquipment.TagNumber = tblLookUpGroups.TagNumber
GROUP BY tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode



But really I'm completely stuck. Any help/suggestions would be appreciated!
Thanks,
BB

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved