oledb,.net-2.0,ibm-midrange , IBM i (AS/400) command works locally but not remotely

IBM i (AS/400) command works locally but not remotely


Tag: oledb,.net-2.0,ibm-midrange

I have a Windows service written in VB.NET 2.0 which connects to an IBM AS/400 server. Queries work fine, but when I try to do something like deleting a spool file, I get errors. For example:


Running this command with ExecuteNonQuery yields:

CPF3342 - Job not found 083064/ARUSER/POASYNCMON

However, if I run that same command locally in AS/400, it works just fine. We already checked permissions. What else could be causing the command to fail this way? How can I get more information about the error, or go about troubleshooting this?

EDIT: This problem (and a lot of other ones) appeared when we migrated our server (where the .NET service runs) from Windows Server 2003 to Windows Server 2008.


How can I get more information about the error, or go about troubleshooting this?

The first thing is to verify that the IBM AS/400 server [what OS Version Release and Modification level, Technical Refresh (TR) level (if instead IBM i), Cumulative PTF level were all omitted.?.?] used for the connection is the same server used to perform the command-line invocation; i.e. on the server where the command-line invocation will be made to verify the command is functional, find the active server job in which the CPF3342 is still visible in the log.

The second thing to do is to get the spooled joblog showing the full details of the CPF3342 [and possibly any preceding message(s) that might be related]. If for example the message is not actually that message or is not sent by the expected program QSPCPYF, then immediately the direction of investigation probably would change. What is shown is apparently what is presented at the client, not what came from the server joblog; the USEnglish formatting I believe is "Job &5/&4/&3 not found." for which the formatting "CPF3342 - Job not found &5/&4/&3" is suspect.

To ensure the most appropriate comparison to the request made from the client: • the local user that is signed-on to perform the same request should be the same user as the Current User of the active job found to be servicing the client request • the local user should establish the same System Library List as the active job found to be servicing the client request

If such an incident recurs or even if the same incident persists, then verify the once again the re-create is still possible using the same interface [i.e. the condition\failure persists] and again verify the command-line request is successful [i.e. the circumvention is confirmed, that the same request is possible to be performed at the command-line]; and according to my earlier comment, first ensuring the same server by finding the active job that logs the CPF3342. Immediately afterward:

• Collect a job trace for the Copy Spooled File (CPYSPLF) request; for the failing case, review for any exception\interrupt conditions [with or without a message as accompanying trace data] that precede the program flow for the issuance of the msg CPF3342.

• Review the audit log for any T-AF or anything odd\unexpected at very close to the time of the failing request; expansive auditing should have been established since before the connection to the server.

• Contrast those data collections of the failing case with the same data taken from the successful processing.

Although the symptom [as lightly described, without the full joblog] the possibility of command-exits seems remote, the trace would reveal if the command in either scenario were intercepted by the Command Exit points; these can be reviewed separately [rather than looking in the trace] for any Exit Program, using the Work With Registration Information (WRKREGINF) to review any QIBM_QCA* entries in the repository for what exit programs might impact the CL Command request. But IIRC the trace-data shows which command was invoked, so the trace would also reveal if the unqualified command requests resolved to different *CMD objects.


Getting the second record where the sql query is satisfied

I am trying to fill my list box with the task names from the database where the priority is equal to an item in my List<object>. The code below populates the list box, but the error is that in my database I have two records with the priority of 1...

DataTable.Rows returns second row in c#

Im reading excel sheet using oledbdatareader and convert to Datatable. So first few columns row in excel sheet tells information about the details of excel. Ex: Month ---- First Row Year --- Second Row Payment ---THird row Balance rows in that excel sheet have salary details of all employees. I...

How to force data type return when querying excel from Powershell?

I'm using Powershell to query an excel spreadsheet using an OLEDB connection. I have one column with multiple values in it. I have noticed that when 1 our of the 4 cells contains letters, the query will return a NULL and the data type will be a System.DBNull. See below:...

Where do these 1k threads come from

I am trying to create a application that multi threaded downloads images from a website, as a introduction into threading. (never used threading properly before) But currently it seems to create 1000+ threads and I am not sure where they are coming from. I first queue a thread into a...

SQL update query throwing a syntax error [closed]

Problem I am trying to create a database management system in the form of a form application. I am using a Microsoft Access database. I have one particular SQL update query which is throwing an error and I cannot work out what is the cause of the error and would...

Is it possible selecting “optional” column in single query?

I have three tables: // table item +-------+-------+----------+ | ID | Cat | SubCat | +-------+-------+----------+ | A001 | 001 | 001 | +-------+-------+----------+ | A002 | 001 | 000 | +-------+-------+----------+ // table category +-------+-------------+ | ID | CatName | +-------+-------------+ | 001 | MyCategory | +-------+-------------+ // table...

How to write OleDbCommand to get only distinct rows?

I just want to have rows that has distinct ManufacturerPartNumber from my excel sheet. So that, I gave my command text like this: cmdExcel.CommandText = "SELECT Name, DISTINT [ManufacturerPartNumber],Price From [" + SheetName + "] WHERE [ManufacturerPartNumber] IS NOT NULL"; But this's not working, and if I changed DISTINCT [ManufacturerPartNumber]...

Make connection string ADO.NET

How can you make a connection string for an OleDbConnection? I have tried a lot of combinations and variants, but nothing works. I use a MS-SQL platform with C# to make the connection for SQL EXPRESS. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Data\Northwind.mdb;User Id=admin;Password=; Data Source=(local);Provider=SQLOLEDB;Initial Catalog=FastCycle;Integrated Security=true Persist Security Info=False;Data Source=Northwind;Integrated Security=SSPI;Trusted_Connection=TRUE;providerName=System.Data.OleDb Provider=SQLOLEDB;Data...

How can I select multi-column from table where date is last day?

I'm trying to select a field from my table where date is less than today. connect.Open(); command.Connection = connect; today = DateTime.Today.ToString("dd/MM/yyyy"); string query = "select attendance as [Attend], Emp_UserId as ID, 'Date_ofday' as [Today Date] , Emp_UserName as Name ,Delay_Hours as [Delay Hours] from Attendance where Date_ofday > '"+...

Merged DataRows are not Updated in the database but Added DataRows are. Why?

I'm currently trying to read a big bulk of excel files which are stored in a datatable called total and then i need to save this to a access database, it works as it is now but i'm afraid it will be very inefficient with the amount of excel files...

Javascript: jQuery help getting
text from an append to new function for autocomplete (using OLEDB as souce)

I'm still learning JS and jQuery, so don't hate on my coding too much! :P Anyways, here's what I am trying to do: I have a database that has all cities and postal codes for a country. Some postal codes have multiple cities. I am first making an auto-complete for...

Adding data to DBF file adds column _NullFlags

Im currently working on a program that gets information from a sql database and creates a .DBF that has the information in there. I got it to the point where it makes the file and adds the columns/rows. But every time I run it, it adds a column named _NullFlags...

Cant find code to insert into AS access database using c#

I am very new to this. I have a MS access database (.mdb having only two columns) and i have linked it using a gridview. It displays whatever is there in the database on the webpage. I also have an post button for a text box (like the one in...

insert date with time in access database c#

In the access I'm only getting the date 5/12/2015 but no the time, I need something like this 5/12/2015 4:56 PM saved in the access database DateTime dtclickdate = DateTime.Now; OleDbParameter clickdate = new OleDbParameter("@clickdate ", OleDbType.DBDate); clickdate.Value = dtclickdate; cmd.Parameters.Add(clickdate); ...

How to create Unit Test in .NET 2.0 VS2013?

I have tried microsoft unit test package from nuget ,but it's failed to install, so please help, how to use unit test in visual studio using any package.

XML Parsing. Hide Parent node

So I have an xml file parsed to a TreeView list, currently I have about four levels of nodes when I only want three. I want to hide/remove the parent node or the top node since it's not much useful. I could do that by editing the XML file itself,...

IBM i (AS/400) command works locally but not remotely

I have a Windows service written in VB.NET 2.0 which connects to an IBM AS/400 server. Queries work fine, but when I try to do something like deleting a spool file, I get errors. For example: CPYSPLF FILE(PO630A) TOFILE(MPLCDATPAR/PO630APF) JOB(083064/ARUSER/POASYNCMON) SPLNBR(80) MBROPT(*REPLACE) Running this command with ExecuteNonQuery yields: CPF3342 -...

How to check a Cell contains formula or not in Excel through oledb reader or excel library, excel datareader or NPOI etc (Except Interop)?

How to check a Cell contains formula or not in Excel through oledb reader ? System.Data.OleDb.OleDbConnection conn2 = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=NO;IMEX=1\";"); conn2.Open(); string strQuery2 = "SELECT * FROM [" + Table + "]"; System.Data.OleDb.OleDbDataAdapter adapter2 = new System.Data.OleDb.OleDbDataAdapter(strQuery2,...

List.Find Method in a VB.NET2

Using VB in .NET2 Public Class GroupSID Private _groupName As String Private _sid As String Public Property GroupName() As String Get Return _groupName End Get Set(ByVal value As String) _groupName = value End Set End Property Public Property SID() As String Get Return _sid End Get Set(ByVal value As String)...

External table is not in the expected format.

I am creating a small application to import excel data into my database, when i click the button it crashes with the error External table is not in the expected format. I tried googling and changing the codes here and there but the problem still occurs. I tried saving the...

Register event handler for specific subclass

Ok, code structure question: Let's say I have a class, FruitManager, that periodically receives Fruit objects from some data-source. I also have some other classes that need to get notified when these Fruit objects are received. However, each class is only interested in certain types of fruit, and each fruit...

Switching from ODBC to OLEDB and getting “Object Required”

I have been tasked with modifying some VBA code to switch from using an ODBC connection to using an OLEDB connection so we can just code a username and password into a subroutine. I'm certainly not experienced with this but I think I'm managed to get the proper connection string...

The SLXOLEDB.1 provider is not registered on the local machine

I'm building a REST web service in WCF. When I run the WCF project from Visual Studio, everything works fine. Calling the 'Post' method on a specific endpoint works just fine. But when I try to run the same web service from the IIS Manager, calling the same method on...

oledb exception syntax error in insert into statement (0x80040E14) in C#

I'm having and exception in my insert into statement.But the data is inserting to the table correctly. can someone please show me the error in this code. private void btnAddNewSale_Click(object sender, EventArgs e) { string StrQuery; connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; for (int i = 0;...

Searching using like

When im searching my datagrid, it should be always the first word to type. If i type the next word or consecutive letter it contains, it doesnt show. Example: Im going to search Welcome Back. If i type "Elcome" or "back", it doesnt show. Here is my code... OleDbCommand cmd...

Parametric 'UPDATE' string running with no errors but not doing anything

I've recently changed an Update SQL string from dynamic SQL string to parametric SQL string. Here's what I had before: OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.DBstring); OleDbCommand comm = new OleDbCommand(); comm.CommandText = "UPDATE myTable SET MY_FIELD='" + myString + "' WHERE F_SERIAL = '"+mySerial+"'"; comm.CommandType = CommandType.Text;...

Can't insert record into MS Access through C# oledb

I have a table in my MS Access database called "Genres". It has the following columns: ID -> AutoNumber Index -> Number GenreText -> Text Here is the C# code that I use: public static void AddGenre(string text, int index) { string query = "INSERT INTO Genres(Index, GenreText) VALUES(@index, @text)";...

Query to get the count of records from MS Access database

I am using below code to get the count of records present in database, but somehow it is not working. throwing me an error: Input string was not in a correct format. conn.Open(); OleDbCommand cmd1 = new OleDbCommand(); cmd1.CommandText = "select count(*) from Orders_Description where (ItemCode=" + Convert.ToInt32(row.Cells[1].Value) + ")...

how to create master detail combobox in C#?

I Have a excel sheet test.xlsx, two sheets within Sheet name : Product1 has following data : ID ParentID Title 1 0 P1_1 2 0 P1_2 Sheet name : Product2 has following data : ID ParentID Title 1 1 P2_1 2 1 P2_2 3 1 P2_3 4 1 P2_4 5...

Dapper, MS Access, Integers and “no value given for one or more required parameters”

I keep getting the error "no value given for one or more required parameters" on the third attempt to insert in the code below. The first two attempts work fine. I'm running in .NET 4.0, against MS Access in VS 2012. And this problem appears to be consistent whether I'm...

Select statement using OleDbCommand throws InvalidOperationException

I need to find a record in C# but I get an InvalidOperationException: public static Auto findAuto(int kfznr) { Auto retAuto = new Auto(); try { myOleDbConnection.Open(); string query = "SELECT * FROM Auto WHERE Auto.KFZNR = " + kfznr; OleDbCommand select = new OleDbCommand(); select.Connection = myOleDbConnection; select.CommandText =...

Do I need to use try-catch for every command?

I have oledb connection. I use try-catch because commands sometimes getting error. Like this: OleDbConnection Connection; Connection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("~/db.mdb")); OleDbCommand Command1, Command2, Command3; Command1 = new OleDbCommand("SELECT a FROM Table1 WHERE ID = 1", Connection); Command2 = new OleDbCommand("SELECT a FROM Table1 WHERE ID = 2",...

fill command cannot be used

This Is my code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.SessionState; //db con using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; namespace SOMaintenance.App_Code { public class readdata { string sConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public void selectdata(DataSet dataset1, string sqlcommand) { string strConString =...

Inserting special chars (such as ' or +) into an Access database

I have not much experience with SQL, Access nor C# but I find no solution to a problem that should look quite simple for someone who has more expertise. Basically, the user fill some textboxes in a Winform and he might insert some "special" characters (at least, special for DB...

VB.NET Releasing Excel file after reading so it can be overwritten

I am having the same issue that many before me have had, and I have found several threads on the issue but my application of their fixes have not yielded any change. I am reading an excel file and populating a DataGridView with it. Very simply the user can modify...

Python removes quotes

I'm trying to make a simple gui with tkinter utilizing windows built-in search functionality. This is the code I'm having issues with. #gets search keywords from user input and splits into list keyword_list = self.entry.get() keyword_list = keyword_list.split() #creates empty string keyword_string = "" #fills empty string with list, appends...

Find all k-size subsets with sum s of an n-size bag of duplicate unsorted positive integers

Please note that this is required for a C# .NET 2.0 project (Linq not allowed). I know very similar questions have been asked here and I have already produce some working code (see below) but still would like an advice as to how to make the algorithm faster given k...

Getting value of Date Time Picker and using BETWEEN for filtering date in Ms Access database

I have a From and To DateTimePicker. I converted them into a String and use it to filter a MsAccess database of a specific range but there is a problem with the filtering. For example, I would like to filter from January 1, 2015 to January 31, 2015. The results...

I have a visual C# project and I'm trying to insert data in a MS Access Database when I press a button [closed]

private void btnSave_Click(object sender, EventArgs e) { string str = "insert into Checklist_Master values('"+comboBox2.Text+"','"+txtaurno.Text+"','"+comboBox1.Text+"','"+txtdeduction.Text+"','"+txtceckpoint.Text+"','"+mdlconnection.user_name+"')"; int dd = mdlconnection.excuteQuery(str); //MessageBox.Show(str); if (dd > 0) { MessageBox.Show("Data Saved Successfully..!!!"); reset(); } } And connectionstring is : public static void getconn() { con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data...

Syntax error in UPDATE statement OleDbException

I'm writing an application which stores user information. Currently the user is supposed to update their Name, Height, Weight and Birthday. string height = TB_ClientHeight.Text; string weight = TB_ClientWeight.Text; string name = TB_ClientName.Text; string bday = dateTimePicker1.Value.ToString("dd-MM-yyyy"); int heightint = Convert.ToInt32(height); int weightint = Convert.ToInt32(weight); It's updated by calling the...

No value given for required parameters, with value

I'm working in a c#/ASP.NET on a web app. As part of the app, I want to connect to an ms-access database, and get values from it into a dataset. For some reason, I get the error in the title when filling the dataset with a DataAdaptor- despite this, when...

Data in Excel with OLEDB connection from access don't update when use LIKE operator

When I use LIKE operator in a Query in Access and I create a OLEDB connection in Excel to import this data to Excel, the data don't update even after change the query with another filter. Is like if I had not changed the query with the new filter. I...

How to use Oracle connection in SSIS?

I must develop a SSIS Package with Oracle connection. I'm using Windows 8.1 64bit with Visual Studio 2010 and Oracle version 12c(remotely). I would use "Microsoft OLE DB Provider for Oracle" like picture: Where I insert my connection parameters: After "TEST CONNECTION" command I get this error: [UPDATE] After installation...

Syntax Error In Insert Statement SQL C#

I'm having trouble with the df.ExecuteNonQuery(); claiming that the insert statement has SQL errors. I'm not sure why the other parts of the program is working with the Insert statement, but the student one just refuses to work. Database: http://puu.sh/hoTCv/c1ccb77551.png OleDbCommand df = new OleDbCommand("INSERT into Students(ID,Password,FirstName,LastName,Street,City,State,Zip,EMail,GPA)" + "VALUES (?,?,?,?,?,?,?,?,?,?)",...

Loop to insert data in OleDb breaks when I try to execute more than 1 query

I'm working on a small offline C# application with an Access 2002 database (.mdb) and OleDb. I have 2 tables where I need to insert data at the same time, one holding a foreign key of the other. So, to simplify let's say one table has 2 attributes: "idTable1" (auto-increment...

How to get the data from Excel into C#?

I have a C# program that takes some data from Excel and saves it to an SQL database. When I open the Excel file and run the program it is working successfully but if I close the Excel file and try to run the program again there is an error...

Filling DataGridView row by row only populates last row

I'm trying to fill a datagridview with one column of integers using a loop. I'm pretty sure I've got the loop and excel reference correct, as I've used this on other projects, but it won't display correctly. Here's the code: Dim da As OleDbDataAdapter Dim ds As DataSet xlWorkbook2 =...

Trying to create foreign keys and insert their values in a .mdb database using C# and OleDb

I have a database with 3 tables. One of them holds a relation of the other two. E.g.: Table1 = idTable1 (PK_Table1), attribute1, attribute2, attribute3 Table2 = idTable2 (PK_Table2), attribute1 Table3 = idTable3 (PK_Table3), attribute1 (FK relating to idTable1), attribute2 (FK relating to idTable2) All primary keys are auto-incrementing fields...

Why am i getting a “Syntax error in UPDATE statement.” exception?

public void EditUser(string id, string userid, string email, string password) { try { _dbConn.Open(); OleDbCommand updateCmd = new OleDbCommand("UPDATE LOGINS SET Username=?, Password=?, Email=? WHERE ID=?", _dbConn); OleDbParameter param1 = new OleDbParameter("Username", userid); OleDbParameter param2 = new OleDbParameter("Password", password); OleDbParameter param3 = new OleDbParameter("Email", email); OleDbParameter param4 = new...