Ver 3.5.1, Runtime ver 2.0.50727 Message=System.ArgumentException: Date

Jude

Sorry, I missed that on my cut and paste. The close bracket is there.

Jude

I am attempting to rewrite a program using SqlCE database vs an OleDb database. The code below worked using OleDb, but now throws a System.Argument exception.

SqlCeCommand GetAcctsCmd = new SqlCeCommand("SELECT ACCTSTORE, ACCTMGR, Count(ACCTMGR) AS [CountOfACCTMGR] FROM ALLACCOUNTS WHERE (DATEDIFF(d, ACCTNEXTDUE, @TESTDATE) > 0 )";
GetAcctsCmd.Parameters.Add("@TESTDATE", SqlDbType.DateTime);
GetAcctsCmd.Parameters["@TESTDATE"].Value = dtPD;

The exception is being thrown on the last line of code above. dtPD is a DateTime. What am I missing? Thanks in advance.

Jude

Solved: The data types in the data table were off.

Jude

I am receiving an Overflow error from an INSERT statement. I understand this is because a value that is assigned to a variable is larger than the data type, correct? The database was built in Access. I pinned the values on screen while debugging and took a screen shot, which is linked herewhich is linked here. I have double checked the data types and they all seem to match.What am I missing? Thank you in advance. Here is the code:

oleDbComQC.CommandText = "INSERT INTO RANKS ([ACCOUNTS], [EOB], [FPD], [F90P], [PDCHECK], [INCOME], [PD], [PD90P]) " +
"VALUES(@ACCTS, @EOB, @FPD, @F90P, @PDC, @INC, @PD, @PD90)";
oleDbComQC.Parameters.AddWithValue("@ACCTS", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@EOB", OleDbType.Decimal);
oleDbComQC.Parameters.AddWithValue("@FPD", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@F90P", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@PDC", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@INC", OleDbType.Decimal);
oleDbComQC.Parameters.AddWithValue("@PD", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@PD90", OleDbType.Integer);

oleDbComQC.Parameters["@ACCTS"].Value = iAccts;
oleDbComQC.Parameters["@EOB"].Value = decEOB;
oleDbComQC.Parameters["@FPD"].Value = iFPD;
oleDbComQC.Parameters["@F90P"].Value = i90PD;
oleDbComQC.Parameters["@PDC"].Value = iNumChks;
oleDbComQC.Parameters["@INC"].Value = decIncome;
oleDbComQC.Parameters["@PD"].Value = iPPD;
oleDbComQC.Parameters["@PD90"].Value = iP90PD;

Jude

Thank you all for dropping all this knowledge on me. It's been a while since I have done any coding, and I am not a programmer by trade. I am in IT for the company I work for, doing mostly tech work (very small business). I write small desktop apps for reporting from our POS system and use C# as I understood it most with VS 2003 (.net 1.1). As I saw a need to update from XP to Win 7, I am trying to rewrite these programs with .net 4, so I am just now, as a few days ago, trying to learn the new IDE in VS 2010, the new classes and deprecated classes, etc, etc. Thank you for your understanding and patience. Trying to google and study everything while taking calls all day for every problem from 'the internet is down' to 'this document is not printing correctly' is quite a task. Again, thanks!

Jude

I apologize for my ignorance.

Jude

I am rewriting a desktop application in VS2010 that was written in .Net 1.1. While doing so, I decided to add a couple of features to the app. One is a data source for a drop down list. I am using a local database file (.sdf) for the data. I noticed in the .Properties.Settings file that the connection string is a hard link to where I entered the file location (which is C:\Users\\Documents\db\). I need this to be more dynamic, due to the fact this app will be on many different computers. I tried editing to :

"Data Source = " + System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments) + @"\dbs\latefee.sdf")]

But I receive the following error at compile time:

Error 1 An attribute argument must be a constant expression, typeof expression or array creation expression of an attribute parameter type C:\Users\brian\AppData\Local\Temporary Projects\latefeedisc\Properties\Settings.Designer.cs 29 68 latefeedisc

How do I correct this? Thank you in advance.

Jude

I hope I posted in the correct forum, as I did not see a VBA forum. My apologies if not. I am needing to solve a problem for a couple of coworkers. They have a workbook where several sheets are being utilized. The first sheet is a form, the second keeps track of each time the form is filled out. To keep the original form, I was planning on having a database to hold the information on the second sheet that both offices could have access. The problem is that I have never passed parameters to a sql statement in VBA and never wrote to a database with an UPDATE statement in VBA. I've googled a bit about the subject but did not get any relevant results. So I came here, hoping someone would have some experience with this. Any help is appreciated.

Jude

Yowza! That too the 27 minute query to less than 4 seconds! Thank you for all of your direction as I am not extremely fluent in SQL.

Jude

Sorry for th late reply. I received more documentation on the keys and indexes today. Here are the keys and indexes for the NINVREC table:

Table Indexes
Name Number of Fields
Key 1 2
Fields:
INV-MK-MODEL-NBR Ascending
INV-MK-SERIAL-NBR Ascending
Key 2 1
Fields:
INV-SERIAL-NBR-KEY Ascending
Key 3 1
Fields:
INV-PRIMARY-SERIAL-NBR Ascending
Key 4 1
Fields:
INV-DATE-SOLD Ascending
Key 5 1
Fields:
INV-LOCATION-REC-1 Ascending
Key 6 1
Fields:
INV-TICKET-NBR Ascending

Here are the keys and indexes for the MISCITEM table:

Table Indexes
Name Number of Fields
Key 1 6
Fields:
MISCITEM-DATE Ascending
MISCITEM-HOUR Ascending
MISCITEM-MINUTE Ascending
MISCITEM-SECOND Ascending
MISCITEM-DUPLICATE-TIME Ascending
MISCITEM-STORE-KEY Ascending
Key 2 1
Fields:
MISCITEM-SALE-DATE Ascending
Key 3 3
Fields:
MISCITEM-CUST-ACCT-NBR Ascending
MISCITEM-TICKET-NBR Ascending
MISCITEM-TICKET-SEQ-NBR Ascending
Key 4 2
Fields:
MISCITEM-MODEL-NBR Ascending
MISCITEM-SERIAL-NBR Ascending
Key 5 2
Fields:
MISCITEM-EXCHANGE-MODEL-NBR Ascending
MISCITEM-EXCHANGE-SERIAL-NBR Ascending
Key 6 1
Fields:
MISCITEM-PENDING-SALE-DATE Ascending

So if I do a join on the INV-MK-MODEL-NBR-INV-MK-SERIAL-NBR and MISCITEM-MODEL-NBR-MISCITEM-SERIAL-NBR that should speed it up, correct?

Jude

I edited the program and inserted a Timespan object. 27 minutes for the query! Ouch! It doesn't matter to me, as the report gets generated on a Sunday while nobody is working. But it just seems like it should not take this long.

Jude

True! Hmmm, but that seems to have broke it. It has been running in a console for about 10 minutes so far. One thing did change. The point of the stall is one step down in the program at the while(DbReader.Read()) statement.

Jude

Did not like. I am receiving the error "Required text is missing. : at 350 : Next Token '@' " Hmmm.....

Jude

Unfortunately it is a read only database...or should I say we only have read only access for reporting purposes.

Jude

Thank you for the quick replies. The definition for the table states DATE TIME for that field. I did a q&d query on the field and it returned in the format of mm/dd/yyyy hh:mm:ss am, but all of the times were 12:00:00 am. I will try your solution and get back.

Jude

I have two tables on a database where I need to gather information for a report. I have built a select statement and run it through an Odbc.DataReader. Stepping through the debugger, once ExecuteReader is executed the program stalls, for a period of minutes. Is there anyway to make this select statement more efficient? The fields that start with INV are in the NINVREC table. The fields that start with MISCITEM are in the MISCITEM table. The matching keys are MISCITEM-SERIAL-NBR and INV-MK-SERIAL-NBR. The database is read only.

OdbcDataReader DbReader;
OdbcConnection DbConnection = new OdbcConnection("DSN=rsss");
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= '" + strEOMDate + "' AND MISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "') AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";

		try
		{
			DbConnection.Open();
			DbReader = DbCommand.ExecuteReader(); **//stalls here**
			while(DbReader.Read())

Jude

Thank you much for your reply. What I am confused about in your example is the comment to 'continue to the next row'. How do I advance the cursor without calling Read()?

Jude

Here is the exact error I am receiving: System.InvalidOperationException: Invalid attempt to read when no data is present But sqlReader.HasRows property is True.

Jude

I have the following

while (sqlReader.Read())
{
//test for data field
while(sqlReader["SOME FIELD"] == variable)
{
//do work, write data to file, yada, yada
sqlReader.Read()//advance to next record
if (!sqlReader.HasRows)//there's no more data
{
blEnd = true;
break
}
if (blEnd){break} //break out of sqlReader.Read() loop
//do more work, yada, yada
}
//end of sqlReader.Read(), loop
}

Jude