小妹初学C#,我在SQL里建了一个存储过程,程序调用的时候查询结果都是错的,急死了!向大家请教请教!

[meitingting]

我的存储过程如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pr_psr3] @vessel nvarchar(25)
as
begin
SELECT distinct t_cvm_address_1.company_name, t_cvm_address_2.company_name AS Expr1, t_cvm_address3.company_name AS Expr2, eCargoData.dbo.t_oeh_hbl.hbl_no,
eCargoData.dbo.t_oem_master.master_bl_no, SMLRPT.dbo.f_so(eCargoData.dbo.t_oeh_hbl.hbl_no) AS Expr5, t_cvm_address4.company_name AS Expr4,
eCargoData.dbo.t_oem_master.vessel_name, eCargoData.dbo.t_oem_master.voyage_no, eCargoData.dbo.t_oem_master.ams_vessel_name, eCargoData.dbo.t_oem_master.ams_voyage_no,
eCargoData.dbo.t_oem_master.on_board_date, eCargoData.dbo.t_oem_master.loading_port, eCargoData.dbo.t_oem_master.eta_date, eCargoData.dbo.t_oeh_hbl.hbl_discharge_port,
eCargoData.dbo.t_oeh_hbl.place_of_delivery, eCargoData.dbo.t_oem_master.place_of_delivery AS Expr6,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[container_no] ELSE [oem_container_1].[container_no] END AS Expr3,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[container_type] ELSE [oem_container_1].[container_type] END AS Expr7,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[container_seal_no] ELSE [oem_container_1].[container_seal_no] END AS Expr8,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[piece_count] ELSE [oem_container_1].[piece_count] END AS Expr9,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[weight_kg] ELSE [oem_container_1].[weight_kg] END AS Expr10,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[cbm] ELSE [oem_container_1].[cbm] END AS Expr11,
CASE WHEN [eCargoData].[dbo].[t_oeh_hbl_container].[container_no] IS NULL THEN [t_oem_container_2].[freight_cost] ELSE [oem_container_1].[freight_cost] END AS Expr12
FROM eCargoData.dbo.t_oem_master_container AS t_oem_container_2 RIGHT OUTER JOIN
eCargoData.dbo.t_oem_master INNER JOIN
eCargoData.dbo.t_oeh_hbl ON eCargoData.dbo.t_oem_master.oem_master_id = eCargoData.dbo.t_oeh_hbl.oem_master_id ON
t_oem_container_2.oem_master_id = eCargoData.dbo.t_oem_master.oem_master_id LEFT OUTER JOIN
eCargoData.dbo.t_oem_master_container AS oem_container_1 RIGHT OUTER JOIN
eCargoData.dbo.t_oeh_hbl_container ON oem_container_1.oem_master_id = eCargoData.dbo.t_oeh_hbl_container.oem_master_id AND
oem_container_1.container_no = eCargoData.dbo.t_oeh_hbl_container.container_no ON
eCargoData.dbo.t_oeh_hbl.oeh_hbl_id = eCargoData.dbo.t_oeh_hbl_container.oeh_hbl_id LEFT OUTER JOIN
eCargoData.dbo.t_cvm_address AS t_cvm_address4 ON eCargoData.dbo.t_oem_master.carrier_id = t_cvm_address4.cvm_address_id LEFT OUTER JOIN
eCargoData.dbo.t_cvm_address AS t_cvm_address3 ON eCargoData.dbo.t_oeh_hbl.shipper_id = t_cvm_address3.cvm_address_id LEFT OUTER JOIN
eCargoData.dbo.t_cvm_address AS t_cvm_address_1 ON eCargoData.dbo.t_oeh_hbl.consignee_id = t_cvm_address_1.cvm_address_id LEFT OUTER JOIN
eCargoData.dbo.t_cvm_address AS t_cvm_address_5 ON eCargoData.dbo.t_oem_master.agent_id = t_cvm_address_5.cvm_address_id LEFT OUTER JOIN
eCargoData.dbo.t_cvm_address AS t_cvm_address_2 ON eCargoData.dbo.t_oeh_hbl.notify_id = t_cvm_address_2.cvm_address_id
where (eCargoData.dbo.t_oem_master.vessel_name like '%'+@vessel+'%');
end
--------------------------------------------------------------------------------------------------
程序代码如下:
private void fillToolStripButton_Click(object sender, EventArgs e)
{
try
{
this.pr_psr3TableAdapter.Fill(this.sMLRPTDataSet.pr_psr3, vesselToolStripTextBox.Text);
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}

}
我在SQL下执行我的存储过程查询出来的结果都是正确的,可是在C#下面就不行,我怀疑是数据类型不对,但不知道怎么办,小妹分少,望大侠别嫌弃,帮帮忙

[meitingting]

小阳阳哥,小妹愚钝,初学,您能给说明白点么?这代码要怎么用啊?

[xuyang840117]

IDataParameter[] parPage = new IDataParameter[]
{
new SqlParameter("@vessel ",SqlDbType.VarChar,25),

};

parPage[0].Value = vesselToolStripTextBox.Text.ToString();

OdbcCommand mCommand = new OdbcCommand();
mCommand = BuildQueryCommand(sMLRPTDataSet.pr_psr3, parameters);
OdbcDataAdapter mdataAdapter = new OdbcDataAdapter(mCommand);

mdataAdapter.Fill(returnDataSet);
mCommand.ExecuteNonQuery();

[xuyang840117]

下班了 88

[halk]

private void fillToolStripButton_Click(object sender, EventArgs e) 
        { 
            try 
            { 
                this.pr_psr3TableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                this.pr_psr3TableAdapter.SelectCommand.CommandText = "[dbo].[pr_psr3]";
                this.pr_psr3TableAdapter.SelectCommand.Parameters.AddWithValue("@vessel", vessel的值);
                this.pr_psr3TableAdapter.Fill(this.sMLRPTDataSet.pr_psr3); 
            } 
            catch (System.Exception ex) 
            { 
                System.Windows.Forms.MessageBox.Show(ex.Message); 
            } 

        } 

[meitingting]

我汗啊...还有没有大侠给指点指点啊?

[ericzhangbo1982111]

真厉害，写这么长的存储过程

[meitingting]

不当老大很久了
小妹没明白您的意思...怎么用啊?
("@vessel", vessel的值)???
我是通过vesselToolStripTextBox.Text得到值的啊?
我在VS2005的数据源预览数据的地方直接预览我的这个存储过程,得到的结果都是错的,但是我在SQL下执行就没问题..??

[halk]

[Quote=引用 9 楼 ericzhangbo1982111 的回复:]
真厉害，写这么长的存储过程
[/Quote]
人家是用构造器构造的，呵呵…… 没看到关键字全大写，表名都写的很全（库.用户.表名）吗？

[halk]

1、用 vesselToolStripTextBox.Text 这一堆字母替换我给你的代码中的 vessel的值 这几个字；
2、用我给你的fillToolStripButton_Click函数整个替换你原来的fillToolStripButton_Click函数；
3、编译，运行。
4、真累！

[RichardSzwz]

关注中...

[meitingting]

不当老大很久了
按你说的做了,报错说
错误 4 “Fill”方法没有采用“1”个参数的重载 E:\BegVCSharp\SMLReport\SMLReportSystem(0708)a\SMLReportSystem\ucm_psr.cs 27 17 SMLReportSystem

错误 1 “SMLReportSystem.SMLRPTDataSetTableAdapters.pr_psr3TableAdapter”并不包含“SelectCommand”的定义 E:\BegVCSharp\SMLReport\SMLReportSystem(0708)a\SMLReportSystem\ucm_psr.cs 24 42 SMLReportSystem

[mytvgames]

貌似楼主的没有先和数据库连接，也没有执行指定的存储过程，也没有DataSet来存储查询结果吧。

[halk]

晕死，我没辙了。

[meitingting]

哪位大侠再给帮帮忙啊?我就是搞不明白,为啥在SQL下执行我的存储过程就没问题,但是在C#下就不行,我不通过程序,直接用VS2005的数据源预览,输入参数预览出来的数据都是错的,晕死了

[meitingting]

我觉得应该是数据类型上出的问题,C#下的数据类型是String,不知道怎么转成unicode类型,小妹初学,基本的代码都不会写,请大侠们多原谅,帮帮忙吧

[DalyQiao]

SqlCommand comm = new SqlCommand ( "[dbo].[pr_psr3]" , conn );
comm.CommandType= CommandType.StoredProcedure;
你是不是漏了第二行了

[DalyQiao]

或者生成数据集时，为SelectCommand指定的语句不对

[mytvgames]

[Quote=引用 18 楼 meitingting 的回复:]
我觉得应该是数据类型上出的问题,C#下的数据类型是String,不知道怎么转成unicode类型,小妹初学,基本的代码都不会写,请大侠们多原谅,帮帮忙吧
[/Quote]

C#的string 本来就是unicode

[meitingting]

呜呜呜..............那可咋办啊?没有办法了么?我在查询分析器下输入参数查询得到的结果一点问题都没有,大概查到500条
C#下输入同样的参数,查出来的完全不对,查出1万多条,乱七八糟的,一点都不对

[meitingting]

真对不起大家,我在VS2005下把数据源重新填加了一次就OK了,真是郁闷死了,其实根本就没什么错误...
分也少,就分给大家吧,谢谢各位仁兄了噢!