|
Hi,
I have no idea anymore...
there is a file named 'fdax.txt' and a MS Access DB with an existing table 'data_table'. I have created a void 'ReadWriteData()' that should read out the data from the file which contains maybe hundreds of lines and insert them into the 'data_table'. My StreamReader works right, the SQL-Syntax is ok. but not more than 30 lines of data will be inserted into 'data_table' before there comes up an OleDbException: Overflow. After clicking OK it will be continued and some lines will be inserted. Then a new OleDbException: Overflow... OK... some lines... Overflow... OK... some lines till the end of loop.
Can anyone help me?
Here is the code of my 'ReadWriteData()':
private void ReadWriteData()<br />
{<br />
StreamReader datei;<br />
datei = new StreamReader("fdax.txt", Encoding.ASCII, true,1);<br />
<br />
string zeile;<br />
string[] felder;<br />
<br />
int id = 1;<br />
while (datei.Peek() != -1)<br />
{<br />
zeile = datei.ReadLine();<br />
felder = zeile.Split(new char[] {','});<br />
<br />
string datumzeit = felder[1] + felder[2];<br />
string strOpen = felder[3];<br />
string strHigh = felder[4];<br />
string strLow = felder[5];<br />
string strClose = felder[6];<br />
string strVolume = felder[7];<br />
<br />
string strJahr = datumzeit.Substring(0, 4);<br />
int jahr = Convert.ToInt16(strJahr);<br />
string strMonat = datumzeit.Substring(4, 2);<br />
int monat = Convert.ToInt16(strMonat);<br />
string strTag = datumzeit.Substring(6, 2);<br />
int tag = Convert.ToInt16(strTag);<br />
string strStunde= datumzeit.Substring(8, 2);<br />
int stunde = Convert.ToInt16(strStunde);<br />
string strMinute= datumzeit.Substring(8, 2);<br />
int minute = Convert.ToInt16(strMinute);<br />
string strSekunde= datumzeit.Substring(8, 2);<br />
int sekunde = Convert.ToInt16(strSekunde);<br />
convertdatumzeit = new DateTime(jahr, monat, tag, stunde, minute, sekunde, 0);<br />
string strDatum = Convert.ToString(convertdatumzeit);<br />
string strId = Convert.ToString(id);<br />
<br />
string strInsertIntoMDB = "insert into data_table(id, datum, [open], high, low, [close], volume) values(";<br />
strInsertIntoMDB += ToSQL(strId) + ", '";<br />
strInsertIntoMDB += ToSQL(strDatum) + "', ";<br />
strInsertIntoMDB += ToSQL(strOpen) + ", "; <br />
strInsertIntoMDB += ToSQL(strHigh) + ", "; <br />
strInsertIntoMDB += ToSQL(strLow)+ ", "; <br />
strInsertIntoMDB += ToSQL(strClose) + ", "; <br />
strInsertIntoMDB += ToSQL(strVolume) + ")";<br />
oleDbCommand1 = oleDbConnection1.CreateCommand();<br />
oleDbCommand1.CommandText = strInsertIntoMDB;<br />
try<br />
{<br />
oleDbConnection1.Open();<br />
oleDbCommand1.ExecuteNonQuery();<br />
oleDbCommand1.Dispose();<br />
oleDbConnection1.Close();<br />
}<br />
catch(Exception ed)<br />
{<br />
MessageBox.Show("Error in inserting! "+ed.ToString(), "Error");<br />
}<br />
id++;<br />
} <br />
<br />
}<br />
<br />
private string ToSQL( string input )<br />
{<br />
return input.Replace( "'" , "''" ); <br />
}
|
|
|
|
|
Hi,
Does it throw the exception on the same lines of the data file every time? If so, you might want to verify that you are parsing the line correctly and that the fields in the Access database are large enough to accept the value.
Bill
|
|
|
|
|
Hi Bill,
Thank you for your reply! I just found my mistake... and i was felt embarrassed! It's so simple...
One field of my MS Access table has not the right type. The Integer type of MS Access is not the same as in C# or other prog language. And so by inserting the Dataset comes the error at this field with a number > 30,000.
Sebastian.
|
|
|
|
|
Anyone have any ideas on how to store richtext in an sql database; then retreive the data and reload the richtext control without losing the formatting?
I've spent days looking on the web for answers and so far came up empty.
Any help will be greatly appreciated...
|
|
|
|
|
Just store the data in a text or image column. You can store any type of binary document data in the image column and text formatted data in text columns.
onwards and upwards...
|
|
|
|
|
I Want to List the Table Names Of a MS Access Database Using ADO Connection.
This is How I tried
ADODB.Connection myConnection = new ADODB.ConnectionClass();
ADODB.Recordset rsTblNames = new ADODB.RecordsetClass();
if (optAccess.Checked)
{
ConnStr= "Provider=Microsoft.Jet.OLEDB.4.0;User ID=;Data Source="+ txtDbPath.Text +";Mode=ReadWrite;Extended Properties='';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
myConnection.Open(ConnStr,"","",0);
StrSql= "SELECT Name FROM MSysObjects WHERE Type = 1";
rsTblNames.Open(StrSql, myConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly,0);
}
But This returns a Error
|
|
|
|
|
You might want to try ADOX instead, although I'm not sure if it works with Access.
Ian Darling
"The different versions of the UN*X brand operating system are numbered in a logical sequence: 5, 6, 7, 2, 2.9, 3, 4.0, III, 4.1, V, 4.2, V.2, and 4.3" - Alan Filipski
|
|
|
|
|
Try this:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
Source: http://www.sadeveloper.net/Forum/ShowPost.aspx?PostID=5153
(There are more info if you need it)
|
|
|
|
|
Is there any way we can get the column/field names of a table in access??
|
|
|
|
|
Hi,
Try this:
<br />
dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB;User ID=xxx;Password=xxx;"; <br />
<br />
dbCon.Open(); <br />
DataTable dt = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null); <br />
<br />
foreach(DataRow myRow in dt.Rows) <br />
{ <br />
if (myRow["TABLE_NAME"].ToString() == tabSel) <br />
{ <br />
ColList.Items.Add(myRow["COLUMN_NAME"].ToString()); <br />
} <br />
}<br />
This is also from the link that I posted above, here it is again:
http://www.sadeveloper.net/Forum/ShowPost.aspx?PostID=5153
HTH,
-Thea-
|
|
|
|
|
How do you write a stored procedure/function that returns recordsets?
Thanks
Man Learns from History that he never learns from History
|
|
|
|
|
in sql?
Just have the last statement of the sproc be a select that returns the "table" of data you want.
|
|
|
|
|
Simply , when you write a query there it return recordsets,for example:
SELECT * FROM table1
when you put it in a SP it will return all recorsets from that table.
Mazy
"Improvisation is the touchstone of wit." - Molière
|
|
|
|
|
CREATE PROCEDURE MyProcedure
AS
SELECT * FROM SomeTable
I hope this helps. Functions are slightly different. There are three types of functions, those that return scalar value, inline table-valued functions and multi-statement table-valued function. The one that you might want is inline table-valued function
CREATE FUNCTION MyFunc
RETURNS TABLE
AS
RETURN (SELECT * FROM SomeTable WHERE SomeColumn = SomeCriteria)
|
|
|
|
|
I am writing a C#/.NET program for SQL Server which performs a bulk move of data from one database to another. This is intended to be a utility end users of our product can use to perform routine maintenance on the database and will alao have options to perform summarization on the data in the alternate database after it has been moved. The program dynamically builds an INSERT and a DELETE command and issues them to the database using the 'ExecuteNonQuery()' command. If I use the Query Analyzer to test the INSERT/DELETE commands, they complete without error.
The problem: The 'ExecuteNonQuery()' of the ODBC classes only returns an 'int' representing the number of rows effected. However, the number of records copied is often too large to fit within an integer. This, in turn, causes a '-1' to be returned instead. A careful examination of the data reveals that the INSERT actually did copy the data (most of the time), but the '-1' causes the program to not issue the DELETE command, because it uses the return value of 'ExecuteNonQuery()' as another indicator of an error condition.
This is unreliable and inconsistent (unfortunately the only consistency Microsoft follows is that they are consistently inconsistent) and can lead to duplicate information. Is there a way around this??? Is there a way to determine the TRUE number of records effected by the call??? Is there another, more reliable, way of determining whether the command completed successfully???
NOTE: my exception handler is not getting triggered in this case, nor does it look like the connection status is changing (I was assuming possibly a timeout was happening) since my connection status handler is not getting called either.
|
|
|
|
|
How about a sproc (Stored PROCedure) with an output parameter for the number of rows affected?
your sproc definition would look something like:
CREATE PROCEDURE spInsertData(@rows int output) as<br />
whatever your insert code is<br />
set @rows = @@rowcount<br />
Then create a SqlCommand object in your c#. Actually, if you drag and drop from the ServerExplorer, VS should do all the work for you.
Just ExecuteNonQuery, close the connection and look at the [1]'th element of the parameters collection
Bill
|
|
|
|
|
OOPS...hang on...if you're overflowing int, you're inserting more than 2 billion rows?
|
|
|
|
|
I think the "int" being returned is an int16 and not an int32. Here is why. I just ran several more tests and found one, consistently repeatable situation where it is copying over 450000 records, but the routine's return value was just over 2000!!!
I confirm this by using query analyzer to delete the contents of the destination table. Then I run the procedure. After nearly a minute, it returns and the number reported by the routine is just over 2600 rows effected. However, if I do a "SELECT count(*)" on the table in query analyzer, it reports over 461000. The 461000+ figure is correct!!!
It is acting exactly as if it is overflowing an int16 number.
Oh yeah. On the parameter thing. I think only parameters are set/returned by stored procedures. If I add a parameter, it's value is unchanged upon return from the command call, to spite the fact I set the parameter to contain the command returnvalue.
|
|
|
|
|
The first (stored procedure) won't work, because the record selection criteria is too flexible. When I said I "dynamically" built the command strings, I literally allow the user to choose one of several fields in the table and present them with allowed values. Those fields not selected by the user are not added to the "Where" clause. Then, I reuse the exact same "Where" clause on the DELETE command. This alone would require the creation of several stored procedures to make it as flexible as I need it to be.
The fewer the additional fields selected, the more records will be included in the process. The only non-optional selection criteria is record date.
The second part of your suggestion, about trying to pass in parameters the value... I am researching this. It could work, but I am a bit new to the passing of parameters in a db exec command call, so that will take a bit of experimentation to see if it will accomplish the task.
|
|
|
|
|
well...I am not sure what to tell you. All the doc's I've seen claim that ExecuteNonQuery returns int32. Perhaps someone else has some insight?
If you want, sent me the source code... I may be able to figure out what's going on from that.
Best,
Bill
|
|
|
|
|
I just tried one other thing. I ran the program in debug mode, and just after building the INSERT string, but just before calling the ExecuteNonQuery() call, I copied the string to SQL Query analyzer and ran it directly from there. It too copied the correct 461000+ records, but when I had the program continue and run the Execute command, it reported the usual 2600+ records. So, the INSERT command is correct as far as I am concerned.
I also tried to call the ExecuteReader() command and tried to extract the RowsEffected from the returned datareader. I saw it too is an 'int', and it too only reported 2600+ rows effected, even though 461000+ records were copied.
|
|
|
|
|
Yep. I just tried adding the following code to a stand alone program...
long fredl = 461000;
short freds = (short)fredl;
fredl = (long)freds;
Out.Text = fredl.ToString();
and got 2200+ as the result in 'Out', which is the kind of result I am getting from my ExecuteNonQuery() call. I think that when the procedure gets the return from the command, it is funneling it through a short int at some point and then returning it as an Int32. That explains why the return is so unstable.
|
|
|
|
|
I may have found a solution that seems to be working. I added the following to the end of the INSERT... and the DELETE... strings.
SELECT ROWCOUNT_BIG() AS CountOfRows
and then executed this using the datareader execution command. This returns the correct value in a datareader object where I simply retrieved the value as if it was table information. More testing to follow but this seems to have solved the problem.
|
|
|
|
|
Hi,
How to replace a string while retrieving data from an access database in ASP. NET.
I wrote the query as
Select replace(empname,"tt","mm") from employeetable;
but its giving the error as undefined function
pls do reply for this
regards
mijo thomas
|
|
|
|
|
Select IIF([empname]='tt','mm',[empname)) as empname
From employeetable;
This will give you mm if the value of empname is 'tt'; otherwise it will give you empname.
Hope that is what you are looking for 
Barbara
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
