Help about Oledb, MS Access and WinXP

[eljack]

Hi,

I have a big problem interacting with the MS Access database. I have read again and again all the topics related to the error "The number of query values and destination field are not the same" on this forum but still I can not fix. Here is my story:

I have developed a C# App reading the information from one MS Access DB and then write to another MS Access DB. On my developing machine, everything is OK but when installed my app on some another machine installed MS Win XP - SP2 then that error appears. It seems something happened with ODBC on XP in my opinion. Pls help me! Thanks

[LizR]

Then post your table definition, and your c# insert code.

[eljack]

Here's my table definition:

[B]table DSTSCD_MOI[/B](
[INDENT][/INDENT]MaSoTSCD  Number, 
[INDENT][/INDENT]TenTSCD  Text, 
[INDENT][/INDENT]MaSoMHTSCD  Number, 
[INDENT][/INDENT]SoTheTSCD  Text, 
[INDENT][/INDENT]SoHieuTSCD Text, 
[INDENT][/INDENT]SoHieuTieuKhoan Text, 
[INDENT][/INDENT]MaSoTinhTrangTaiSan Number, 
[INDENT][/INDENT]MaSoLoaiDonViTinh Number, 
[INDENT][/INDENT]MaSoCTyCungCap Number, 
[INDENT][/INDENT]MaSoChiNhanh Number, 
[INDENT][/INDENT]MaSoPhongBan Number, 
[INDENT][/INDENT]MaSoNhanVien Number, 
[INDENT][/INDENT]SanXuat Text, 
[INDENT][/INDENT]Model Text, 
[INDENT][/INDENT]SoHieuModel Text, 
[INDENT][/INDENT]SoHieuSeri Text, 
[INDENT][/INDENT]SoHieuMaVach Text, 
[INDENT][/INDENT]NgayNhap DateTime, 
[INDENT][/INDENT]NgayBatDauSuDung DateTime, 
[INDENT][/INDENT]NgayBatDauKhauHao DateTime, 
[INDENT][/INDENT]NgayXuatSoKhauHao DateTime, 
[INDENT][/INDENT]NgayXuat DateTime, 
[INDENT][/INDENT]GiaMua Number, 
[INDENT][/INDENT]MaSoPhuongPhapTinhKhauHao Number, 
[INDENT][/INDENT]TyLeKhauHaoCoBanNam Number, 
[INDENT][/INDENT]SoKyTinhKhauHao Number, 
[INDENT][/INDENT]KyMoi Number, 
[INDENT][/INDENT]TyLeMoi Number, 
[INDENT][/INDENT]KhongTinhKhauHao Yes/No, 
[INDENT][/INDENT]GiaTriThuHoi  Number, 
[INDENT][/INDENT]HienGia  Number, 
[INDENT][/INDENT]GhiChu Memo
)

and my insert code:

public void insertToDBNew(String maTS, String maSoHieuTS, String tenTS, String maMH, String maTinhTrang, String ngayBDKH, String nguyenGia, String tyLeCu, String kyKHCu, String kyMoi, String tyLeMoi)
{


[INDENT][/INDENT]String wery = @"insert into DSTSCD_MOI
(MaSoTSCD,TenTSCD,MaSoMHTSCD,SoTheTSCD,SoHieuTSCD,SoHieuTieuKhoan,
MaSoTinhTrangTaiSan,MaSoLoaiDonViTinh,MaSoCTyCungCap,MaSoChiNhanh,MaSoPhongBan,
MaSoNhanVien,SanXuat,Model,SoHieuModel,SoHieuSeri,SoHieuMaVach,NgayNhap,
NgayBatDauSuDung,NgayBatDauKhauHao,NgayXuatSoKhauHao,NgayXuat,GiaMua,
MaSoPhuongPhapTinhKhauHao,TyLeKhauHaoCoBanNam,SoKyTinhKhauHao,KyMoi,TyLeMoi,
KhongTinhKhauHao,GiaTriThuHoi,HienGia,GhiChu)  " +
"values("+maTS+","+"'"+tenTS+"',"+maMH+","+"'','"+maSoHieuTS+"','',"+maTinhTrang+
",0,0,0,0,0,"+"'','','','','',#1/1/9999#,#1/1/9999#,#"+ ngayBDKH+ "#,"+"#1/1/9999#,#1/1/9999#,"+ nguyenGia+",0,"+tyLeCu+","+
kyKHCu+","+kyMoi+","+tyLeMoi+",No,0,0,'')";

myDB.executeNonQuery(wery);
          
 
}

This code is compiled and executed correctly on my machine and friends but some others is not. I dont know why. When I put the query string directly on Access and run then it works.

Thanks for your reply!

[LizR]

IMHO you could do better to change how you've done your data string, because all the " and ' marks and then the gazillion + marks do make it hard to read. Especially where you have things like ","+"'','"

What id suggest is rather than RUN that code, place it into a text box, and place an example of the SQL to run in a message as I think you maybe missing some ' marks

[eljack]

I got what you mean but how difficult to explain. I have checked this code many times and run many places. Some is OK but some is not. If something wrong with the code then I can not run it anywhere. Here is my example of the query string from debug mode:

insert into DSTSCD_MOI(MaSoTSCD,TenTSCD,MaSoMHTSCD,SoTheTSCD,SoHieuTSCD,SoHieuTieuKhoan,
MaSoTinhTrangTaiSan,MaSoLoaiDonViTinh,MaSoCTyCungCap,MaSoChiNhanh,MaSoPhongBan,
MaSoNhanVien,SanXuat,Model,SoHieuModel,SoHieuSeri,SoHieuMaVach,NgayNhap,
NgayBatDauSuDung,NgayBatDauKhauHao,NgayXuatSoKhauHao,NgayXuat,GiaMua,
MaSoPhuongPhapTinhKhauHao,TyLeKhauHaoCoBanNam,
SoKyTinhKhauHao,KyMoi,TyLeMoi,KhongTinhKhauHao,GiaTriThuHoi,HienGia,GhiChu) values(291,'MAY VI TINH CPQ DC7100 S7540 17',89,'', 'A69.89.026','',1,0,0,0,0,0,'','','','','',#1/1/9999#,#1/1/9999#,
#1/1/2006#,#1/1/9999#,#1/1/9999#,15882143,0,33.3, 0,72,0,No,0,0,'')

If I copy this code and run from Access SQL designer then nothing happens. But from C# app then ... I don't know :)

[LizR]

well you wouldnt see much in access except a new row in your table assuming it didnt violate any key restraints.

next question is is that a line that worked or fails?
Have you determind any data entry specifics for when someone adds a line that fails?

[eljack]

Thanks for your reply!

That line worked on my machine but failed on some other machines.

I've copied the line of data that exposes error from "someone" (as you said ) and run on my machine. It's perfectly. No errors, no bugs. So I began to focus on ODBC and the operating system. I have checked the .Net Framework2 by installing the newest one, virus scanning, updating ... but I couldnt fix. I even re-installed a new MS Access 2003 on that machine.

Do I have to re-install a new OS?

That dirty machine is so far from my working place so I can not install the Visual studio .Net to check. Oh! poor I am!

[LizR]

no you dont need to install a new OS, whats most likely is that either the db is not the same and has other fields or such. Get hold of a copy and check things like casing on the field names, additional fields, maybe even changes such as different field types.

regional settings

[eljack]

Wow, regional settings. Number setting, just the commas and period. Thank you so so much. It help. My friend said that who could help you and I said I dont know, haha ... thanks alot.

[LizR]

cool - please mark your thing as solved