How to insert large file into MSSQL field

I was to get rid of a agreement storing application. I'd needed to save Agreement file as attachment to agreement storing table. So i added a field c_file, type of Varbinary.
And when executed it this error was occurred.
System.Data.SqlClient.SQLException: String or binary date would be truncated.
The statement has been terminated.

I knew that this caused of 8K maximum size of Varbinary type. Then i changed the type to Image, it was OK. Following piece of code, i used.



private void simpleButton1_Click(object sender, EventArgs e)
{
CdsTraining.contractDataTable dtContract = new CdsTraining.contractDataTable();
CdsTrainingTableAdapters.contractTableAdapter taContract = new training.CdsTrainingTableAdapters.contractTableAdapter();
byte[] contract= ReadFully(btnedtAttachment.Text, 0);
try
{
taContract.Insert(Convert.ToInt32(cbbEmployee.EditValue), Convert.ToInt32(cbbTraining.EditValue), dateStart.DateTime, dateEnd.DateTime, txtContractNum.Text, mmDescr.Text, contract, btnedtAttachment.Text);
MessageBox.Show("Saved");
}
catch (Exception ex)
{
MessageBox.Show("Error: "+ ex.ToString());
}
//MessageBox.Show(Convert.ToString(cbbEmployee.EditValue));
}

public static byte[] ReadFully(string fileName, int initialLength)
{
// If we've been passed an unhelpful initial length, just
// use 32K.
if (initialLength < 1)
{
initialLength = 32768;
}
Stream stream = File.OpenRead(fileName);

byte[] buffer = new byte[initialLength];
int read = 0;

int chunk;
while ((chunk = stream.Read(buffer, read, buffer.Length - read)) > 0)
{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{
int nextByte = stream.ReadByte();

// End of stream? If so, we're done
if (nextByte == -1)
{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;
}
}
// Buffer is now too big. Shrink it.
byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;
}

Connection Interrupted

Connection Interrupted
The connection to the server was reset while the page was loading.
The network link was interrupted while negotiating a connection. Please try again.

This is error shown in Firefox, when i upload large files bigger than 4096KB. I found that 4MB is maximum default request length.
You can solve this issue by inserting <httpRuntime maxRequestLength="2048000"> to web.config file. So you upload files up to 2GB total size.

ASPxCallback && ASPxCallbackPanel

I was to do a web form for filling site information in some regions. To do that, i must select Province/City first in ComboBox and then a Region/District that belongs to the Province/City respectively; means when i select Province in combobox1, only regions in that province must be retrieved from server to combobox2.

I use Developer Express components regularly and if i used the latest versions of DevXpress ASPx components, it would be made easy by using ASPxCallbackPanel. But my DevXpress version is DevX 7.2 and there is no ASPxCallbackPanel component as is MS AJAX UpdatePanel. So i solved it with ASPxCallback component.

site_main.aspx:

<dxcb:aspxcallback id="ASPxCallback1" runat="server" clientinstancename="Callback1" oncallback="ASPxCallback1_Callback"></dxcb:aspxcallback>
<clientsideevents callbackcomplete="function(s, e) { var items= e.result.split("|") var i; for (i= 0; i< items[0]; i++) { cbbRegion.AddItem(items[2*i+ 2], items[2*i+ 1]); } }"></clientsideevents>



<dxe:aspxcombobox id="cbbCity" runat="server" height="19px" width="100px" datasourceid="dsrcCity" textfield="city_name" valuefield="city_code"></dxe:aspxcombobox>
<clientsideevents selectedindexchanged="function(s, e) { //alert(s.GetValue()); cbbRegion.ClearItems(); Callback1.SendCallback(s.GetValue()); }"></clientsideevents>


<asp:objectdatasource id="dsrcCity" runat="server" oldvaluesparameterformatstring="original_{0}" selectmethod="GetData" typename="CdsCityTableAdapters.vw_cityTableAdapter"></asp:objectdatasource>



<dxe:aspxcombobox id="cbbRegion" runat="server" clientinstancename="cbbRegion" datasourceid="dsrcRegion" textfield="region_name" valuefield="region_code" width="120px"></dxe:aspxcombobox>

<asp:objectdatasource id="dsrcRegion" runat="server" deletemethod="Delete" insertmethod="Insert" oldvaluesparameterformatstring="original_{0}" selectmethod="GetDataByCity" typename="CdsRegionTableAdapters.hr_regionTableAdapter" updatemethod="Update"></asp:objectdatasource>
<deleteparameters></deleteparameters>
<asp:parameter name="Original_region_id" type="Decimal"></asp:parameter>

<updateparameters></updateparameters>
<asp:parameter name="region_code" type="String"></asp:parameter>
<asp:parameter name="region_name" type="String"></asp:parameter>
<asp:parameter name="city_code" type="String"></asp:parameter>
<asp:parameter name="Original_region_id" type="Decimal"></asp:parameter>

<selectparameters></selectparameters>
<asp:parameter name="city_code" type="String"></asp:parameter>

<insertparameters></insertparameters>
<asp:parameter name="region_code" type="String"></asp:parameter>
<asp:parameter name="region_name" type="String"></asp:parameter>
<asp:parameter name="city_code" type="String"></asp:parameter>



<dxe:aspxmemo id="mmAntenna" runat="server" height="74px" width="729px"></dxe:aspxmemo>

site_main.aspx.cs:
protected void ASPxCallback1_Callback(object source, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
CdsRegionTableAdapters.hr_regionTableAdapter regionTableAdapter = new CdsRegionTableAdapters.hr_regionTableAdapter();
CdsRegion.hr_regionDataTable regionDataTable= new CdsRegion.hr_regionDataTable();
regionTableAdapter.FillByCity(regionDataTable, e.Parameter);
String res = regionDataTable.Rows.Count.ToString();
for (int i = 0; i < regionDataTable.Rows.Count; i++)
{
res = res + "|"+ regionDataTable.Rows[i]["region_code"]+ "|"+ regionDataTable.Rows[i]["region_name"]+ "";
}
e.Result = res;
}

The mechanic is, when selected a city, selectedindexchanged="function(s, e) { //alert(s.GetValue()); cbbRegion.ClearItems(); Callback1.SendCallback(s.GetValue()); } is called. In it, cbbRegion is cleaned up and value of cbbCity is sent to server. Then, on server, ASPxCallback1_Callback is called and it sends corresponding "|" separated region names and codes back to the client using e.Result string. After that, callbackcomplete="function(s, e) { var items= e.result.split("|") var i; for (i= 0; i< items[0]; i++) { cbbRegion.AddItem(items[2*i+ 2], items[2*i+ 1]); } } is called automatically. As you see, the function parses e.Result and adds each to cbbRegion. I think the way i used is not good. But it was working so quick. I prefer coding style, but when result is good, it's ok to hardcode kk.

Тусламж хэрэгтэй байна

Юу вэ гэхлээр мань хүн MT буюу машин орчуулга (Компьютерээр текст орчуулах) сонирхдог юм л даа. Цаг зав гарсан үед ном товхимол шагайж, арга чарга судалдаг гэхүүдээ. Сүүлийн үед сонирхол дээд цэгтээ тулаад цагийг умартан судалгаа хийх болов. Энэ мэдээж ганц хүний чөлөөт цагаараа хийчихдэг ажил биш боловч нэгдүгээрт нэн шаардлагатай судлагдахуун, хоёрдугаарт миний туйлын хүсэл тул өөрийн эрхгүй ханцуй шамлан оров.

Судалгааны явц эхлэл төдий боловч ирээдүйдээ хэрэг болох (одоо ч гэсэн туршилт хийхэд жаахан хэрэг болоод л байна) Өгөгдлийн сан бүрдүүлэх ажлыг нэн түрүүнд хийх шаардлагатай байна. Тиймээс Өгөгдлийн сан бүрдүүлэхэд хамгийн хэрэгтэй эд болох Англи-Монгол, Монгол-Англи орчуулга цуглуулж эхэлж байна. Үүнд таны болон хэн нэгний орчуулсан ном, өгүүлэллээс эхлээд нэг өгүүлбэр ч байсан их тус болох нь дамжиггүй. Тиймээс надад туслая, мөн өөрийн хувь нэмрээ Англи-Монгол өгүүлбэр орчуулагчид оруулъя гэж бодож байвал otgontugs(at)gmail(dot)com* хаягаар явуулна уу.

Таны явуулсан материалийг судалгаанаас өөр зорилгоор ашиглахгүй бөгөөд ямар нэг зохиогчийн эрх тухай зүйл байвал гадагш тараахгүй хав дарж чадна гэдэгээ амлая.

Хэрэв та Машин орчуулгын ямар үр ашигтай өөрийн биеээр мэдрэхийг хүсвэл http://translate.google.com/translate_t хаягаар ороод үзээрэй. Гэхдээ харамсалтай нөгөө л Монгол гэдэг хэл байхгүй л байгаа.

* - (at)-ийн оронд "@", (dot)-ийн оронд "."

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

This error occurred while executing following MSSQL code.

declare @sql varchar(8000);
declare @params varchar(4000);

set @sql= 'select @perf_max= max(avrg)
from (
select avg(coalesce(ea_score, 0)) as avrg
from employee_appraisal
where ea_ap_id= 1 and ea_year='+cast(@yahr as varchar)+' and ea_emp_id in '+ @filter+
'group by ea_emp_id) v1';
set @params= '@perf_max float output';
exec sp_executesql @sql,@params,@perf_max output

I found the error was caused of calling sp_executesql with parameter type of varchar, NOT Nvarchar. @sql is dynamic query which is maximum length of 6K chars in my case. But NVARCHAR is 4K, so it's not enough.
Can anybody provide me solution?
Thanks in advance

Machine Translation/NLP Resources

Here is something useful for NLP/MT:

http://wt.jrc.it/lt/acquis/
http://www.mt-archive.info/
http://nl.ijs.si/telri/Vanilla/
http://www.essex.ac.uk/linguistics/clmt/MTBook/
http://portal.acm.org/
http://www.cs.unt.edu/~rada/wpt/WordAlignment.Guidelines.txt
http://www.iro.umontreal.ca/~simardm/lrec98/
http://www.informatik.uni-trier.de/~ley/db/conf/acl/acl2005.html
http://www.informatik.uni-trier.de/~ley/db/conf/acl/acl2006.html
http://www.informatik.uni-trier.de/~ley/db/conf/acl/acl2007.html
http://en.wikipedia.org/wiki/Bilingual_Evaluation_Understudy
http://nl.ijs.si/telri/
http://en.wikipedia.org/wiki/AntConc
http://corpora.wordpress.com/category/antconc/
http://nltk.sourceforge.net/

Related Mongolian:
http://acl.ldc.upenn.edu/P/P06/P06-1083.pdf
http://crlp.num.edu.mn/
http://www.infocon.mn/tts/ (Mongolian TTS engine in progress)
http://if-lab.slis.tsukuba.ac.jp/fujii/paper/ijcnlp2008khab.pdf

Difference between Response.Redirect and Server.Transfer

I've a secured page that requires users to be logged in.
At the beginning of the page, checks if an user is authenticated and is in right role. When not authenticated, control is redirected to login.aspx which is included Login form.

if (!User.Identity.IsAuthenticated)
{
if (!User.IsInRole("admin"))
{
Server.Transfer("login.aspx");
}
}

This piece of code is telling you cannot see the page if you are not an administrator.
Another one is:

if (!User.Identity.IsAuthenticated)
{
if (!User.IsInRole("admin"))
{
Response.Redirect("login.aspx");
}
}

The difference is Redirect tells client to see login.aspx, Transfer directly executes login.aspx and returns result. I choose Server.Transfer, cus it can be faster.

One more thing:

if (!User.Identity.IsAuthenticated)
{
if (!User.IsInRole("admin"))
{
Response.Redirect("login.aspx?ref=any_page.aspx");
}
}
In login.aspx.cs:
LoginView1.DestinationPageUrl= Request.Params["ref"];

In this case, control jumps to any_page.aspx. I didn't experience it when using Server.Transfer.
I'm not sure its possible.

Failed to access IIS metabase problem

I was using VS2008. it had installed .NET framework 3.5 version. This error occurred when i try to view web application that is in IIS. I googled for it and found following soln. It's working perpectly ;)

Possible Cause:-
When you install IIS AFTER .NET 2.0 framework, the rights of the ASPNET user had not been set correctly.

Resolution
Repair (Uninstall if repair does not work for you) .NET Framework 2.0
Simply run the following from command line to reset the IIS registry settings for aspnet user. Usually framework directory for .Net Framework 2.0 resides under C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

Tree in a datagrid

I used DevExpress.XtraTreeList.TreeList component to show tree structured DB table on a form. It was so easy.

create table treesamp (
nodeid number(5),
parentnodeid number(5),
datatext varchar2(50)
);

Sample table data:
1 0 "Root"
2 1 "Leaf 1"
3 1 "Leaf 2"
4 2 "Leaf 1 of Leaf 1"
5 2 "Leaf 2 of Leaf 1"
6 3 "Leaf 1 of Leaf 2"
7 3 "Leaf 2 of Leaf 2"
8 4 "Leaf 1 of Leaf 1 of Leaf 1"
...

Place your TreeList on a form and just set following values:

treelist1.DataSource= treeDataTable;
treelist1.KeyFieldName= "nodeid";
treelist1.ParentFieldName= "parentid";
treelist1.PreviewFieldName= "datatext";
RootValue= 0; // if id of parent is zero, it must be root node

To print the tree treeList1.ShowPrintPreview();
It prints it in XtraReport Preview, the same as in form grid.

oracle cast function in stored procedure

There is a thing can be called stupid in oracle. I can't understand the reason!
This is a piece of code in a Oracle Trigger. Decimal numbers are compared roundly by double precision.

if (cast(to_char(:new.unit_price) as number(14,2)) = cast(to_char(v_ppl_unit_price) as number(14,2))) then
:new.unit_price := v_temp_new_unit_price;
end if;

But after executed this code, an error is occurred:

Error: PLS-00103: Encountered the symbol "(" when expecting one of the following:
. ) @ %
The symbol ")" was substituted for "(" to continue.
Line: 122
Text: if ( cast(:new.unit_price as number(14, 5)) =

And i found out how to fix it.
that is declare a subtype, and use it on cast:

declare
subtype number_14_2 is number(14, 2);
...
begin
...
if (cast(to_char(:new.unit_price) as number_14_2) = cast(to_char(v_ppl_unit_price) as number_14_2)) then
:new.unit_price := v_temp_new_unit_price;
end if;
...
end

It's done! How strange!

What's the difference between varchar, varchar2?

Монгол хэл дээр бичигдсэнийг харахыг хүсвэл:
http://mongolcoder.blogspot.com/2008/03/varchar-varchar2.html

Varchar and varchar2 are data types of Oracle DB and what's the difference between them?
First: Size, varchar can store 2000 bytes information, but varchar2 can store 4000 bytes information.
Second: Encoding or language chooser(:D), varchar supports ascii, but varchar2 supports unicode.
In addition to this, mongolian character's take 2 bytes space, but english characters take 1 bytes space.

ORA-06502 error occurred while calling stored procedure from C#

Here is a Oracle stored procedure.

create or replace procedure GETVPNOFROMPNO(p_pno in varchar2, ret out varchar2) is
      r SYS_REFCURSOR;
      npno1 varchar2(255);
      retval varchar2(255);
begin
 retval:= p_pno;
 open r for
      select npno from parts_for_update_npno where  parts_for_update_npno.pno= p_pno and npno is not null;
 fetch r into npno1;
 if r%NOTFOUND then
    ret:= retval;
    return;   
 end if;
 retval:= p_pno||' /'||npno1;
 ret:= retval;
end GETVPNOFROMPNO;

As you see, there is no need of REF CURSOR. I declared it just for further use.
And here is a function that calls the stored procedure above.

       public static string getVPNOfromPNO(OracleConnection c, string pno)
       {
           OracleCommand cmd = null;
           try
           {
               c.Open();
               cmd = c.CreateCommand();
               cmd.CommandText = "GETVPNOFROMPNO";
               cmd.CommandType = CommandType.StoredProcedure;

               cmd.Parameters.Add("p_pno", OracleType.VarChar);
               cmd.Parameters["p_pno"].Value = pno;
               cmd.Parameters["p_pno"].Direction = ParameterDirection.Input;

               cmd.Parameters.Add("ret", OracleType.VarChar);
               cmd.Parameters["ret"].Value = "";
               cmd.Parameters["ret"].Direction = ParameterDirection.Output;

               cmd.ExecuteNonQuery();
               c.Close();
               //MessageBox.Show("Suces");
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
               Program.setStatus("Error while getting ...!", ex);
           }
           return cmd.Parameters["ret"].Value.ToString();
       }

Do you think there is any error? I thought not, but there was.
While executing, this code throws "ORA-06502: numeric or value error: character string buffer too small" exception.

The error is corrected by changing cmd.Parameters["ret"].Value = ""; to cmd.Parameters["ret"].Value = "__________________";

Why? I used parameter for getting output value. The too small string buffer in the error description is cmd.Parameters["ret"].Value that is length of zero. After setting enough space for this variable, it was OK. If i used oracle function, it would be OK without any problem.

Database programming method

I'm talking about a method of writing a database application. You can use this method surely, because this is experience based way of programming.

The steps are:
1. Collect your User Requirement Definitions.
2. Now draw your Database Diagram. In database diagram must have all required outputs (reports).
3. You may need to draw Data Flow Diagram to make the system clear.
4. Create all forms that register data. On all command actions call an empty stored procedure that will be created later.
5. Create all program reports by using user requirement defns. Any report must be bound to exactly one database table according to Step 2.
After this 5 ways, you have an application that gets inputs, gives reports, but no processing involved. You can give it to customer to try how to work.
6. Now start writing stored procedures for main processing. It's easy. You have already named stored procedures and defined what to do in it.

My teacher said that:
Success of developing application program is based on systematic method. That's why there are many methods such as traditional system analysis, UML, RUP and so on. But if you have your way of developing programs, that is better than these standards.

I agreed it. Because i created it, suits me and my environment perfectly, no more work, can be changed freely ...

Oracle sequence autoincrement field and DataGrid

I needed to create a Form that does insert/edit/update/delete an Oracle table data in a DataGrid. The Oracle table has primary key that is autoincremented. I was using a trigger on before insert new row that sets sequence's nextval into primary key field. I bound DataTable to a GridControl and then inserted new row. The key value of new row in table in DBMS was correct, but GridControl's primary key field value was not updated as in DBMS. I wasted so much time to make it formally done.

My first solution was a forced one. I wrote a stored procedure that returns the sequence's next value. Called it from my C# code and set it to DataTable's primary field column. To use that i disabled the trigger.

Next to it found a Oracle 10g new feature. It's not supported in previous versions of Oracle, i heard. It's RETURNING clause. RETURNING clause is used for return values after executing a query.
For instance:

INSERT (pk_field, field1, field2, field3) INTO table1 VALUES (seq1.NEXTVAL, 'field1', 'field2', 'field3') RETURNING pk_field INTO :pk_field;

This is an example of insertQuery property of TableAdapter. Insert query itself tells that "I" insert nextval of sequence into primary key field and return it back to the grid using :pk_field parameter.

Unfortunately i couldnt get it worked. Visual Studio 2005 was not "understand" RETURNING clause, sayd me wrong SQL.

Later, a few days ago used it Visual Studio 2008. It's working. But i'm still disappointed. I need to do it in VS2005. I hope there must have any solutions. Please let me know if you have any other.

How to show large table in a data grid with .NET

I think showing large amount of data in a grid is a problem, especially for greenhorns like me. Yesterday I found a way to solve it. It doesn't use TableAdapter.Fill method and direclty shown without loading large amount of data. It will load data partially when you scroll down or filter.
To see how it works, follow this:

I tried GridControl Component of Developer eXpress Components. This GridControl control has ServerMode property.

Using ServerMode:
1. Create a new form (ex: Form1)
2. Add DataSet and typed datatable to it; Recommend you to create it using Add DataSource wizard (ex: DataSet1; CustomersDataTable)
3. Add GridControl to a Form1 (ex: GridControl1)
4. Set GridControl1.ServerMode property to True
5. Add XpServerCollectionSource control (ex: xpServerCollectionSource1)
6. Set DataSet1.CustomerDataTable to ObjectClassInfo property of xpServerCollectionSource1
you can select it from property combobox, shown as WindowsApplication1.DataSet1+CustomerDataTable
7. Select xpServerCollectionSource1 as GridControl1.DataSource
8. type following code to Form1_Load or anywhere before GridControl1 is loaded
XpoDefault.ConnectionString= DevExpress.Xpo.DB.OracleConnectionProvider.GetConnectionString("local", "scott", "tiger");
// in this case i used Oracle 10g

for other DBMS you can use other connection providers included in DevExpress.Xpo.DB package
ServerMode supports manay databases such as Oracle, MSSQL, MySQL, Access ...

You can download evalution version of the component from http://www.devexpress.com/Downloads/