ITKeyword,专注技术干货聚合推荐

注册 | 登录

架构设计:系统间通信(40)——自己动手设计ESB(1)

yinwenjie 2016-07-21

相关推荐:高负载系统架构设计(转)

一个小型的网站,比如个人网站,可以使用最简单的html 静态页面就实现了,配合一些图片达到美化效果,所有的页面均存放在一个目录下,这样的网站对系统架构、性

up vote 1 down vote favorite

for a few days I've been trying and trying to get the stored procedure to work in vb.net, but I can't seem to make it work.

I'd really appreciate it if someone could help me out here. I've been trying really hard to solve this problem, but I just can't find a solution.

Private Sub getID()
    Dim ds As New DataSet
    Dim con_str = "
    Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySql.Data.MySqlClient.MySqlConnection(con_str)
    con.Open()
    Dim cmd As MySql.Data.MySqlClient.MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "getID()"
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@id", "113")
    Dim da As MySql.Data.MySqlClient.MySqlDataAdapter = New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
    Dim dt As New DataTable
    da.SelectCommand.Connection = con
    da.Fill(dt)
End Sub

When I try to run this I get the exception "Incorrect number of arguments for PROCEDURE otrs.getID; expected 1, got 0" on the line da.Fill(dt). I don't really know why this happens. There is no argument declared in my stored procedure.

My stored procedure should be fine. Calling it out of the dbms worked out fine, but here is the code anyways:

    delimiter $$

CREATE DEFINER=`otrs`@`%` PROCEDURE `getCIs`()
BEGIN
SELECT  
    l.source_key AS base_id,  
    c1.configitem_number AS base_number,
    v.name as base_name,
    l.target_key AS link_id,  
    c2.configitem_number AS link_number,
    v1.name as link_name,
    t.name as link_type
FROM  
    otrs.link_relation AS l
        INNER JOIN  
    otrs.configitem AS c1 
        ON l.source_key = c1.id     
        INNER JOIN  
    otrs.configitem AS c2 
        ON l.target_key = c2.id    
        INNER JOIN
    otrs.link_type AS t
        INNER JOIN
    otrs.configitem_version AS v
        ON l.source_key = v.configitem_id
        INNER JOIN
    otrs.configitem_version AS v1
        ON l.target_key = v1.configitem_id;
END$$

And yes, I did use the search-function and I googled quite a lot, but I'm still thankful for any kind of information regarding my problem.

So I found out was wrong. Apparently you dont need the () after the procedure-name. Im not 100% sure though, because now nothing happens at all:

相关推荐:系统集群架构设计需考虑的几个因素

大型分布式系统,通常是由一组服务器集群构成的。对于这样的系统架构,设计往往比较复杂,需要考虑的因素也相对较多。从总体的设计原则来说,在系统架构和设计的

Private Sub getID()
    Dim ds As New DataSet
    Dim con_str = ""
    Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySql.Data.MySqlClient.MySqlConnection(con_str)
    con.Open()
    Dim cmd As MySql.Data.MySqlClient.MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand
    cmd.CommandType = CommandType.StoredProcedure
    Try
        cmd.CommandText = "getID"
    Catch ex As Exception
        MsgBox(ex.Message())
    End Try

    cmd.Parameters.AddWithValue("@id", 113)
    cmd.Connection = con
    Dim da As MySql.Data.MySqlClient.MySqlDataAdapter = New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
    Dim dt As New DataTable
    Try
        da.Fill(dt)
    Catch ex As Exception
        MsgBox(ex.Message())
    End Try
End Sub
mysql vb.net stored-procedures datagridview share edited Mar 1 '12 at 10:33 asked Dec 20 '11 at 7:35 OhSnap 26 1 3 21 add a comment  | 

2 Answers 2

active oldest votes up vote 0 down vote accepted

This line is wrong it creates a new command with the command text of "call getID()" and adds it to the adapter with no parameters

  Dim da As MySql.Data.MySqlClient.MySqlDataAdapter = _
   New MySql.Data.MySqlClient.MySqlDataAdapter("call getID()", con_str)

you want

  cmd.CommandText = "getID"  
  Dim da As MySql.Data.MySqlClient.MySqlDataAdapter = _
   New MySql.Data.MySqlClient.MySqlDataAdapter(cmd, con_str)
share edited Dec 20 '11 at 15:17 answered Dec 20 '11 at 7:39 Conrad Frix 41.4k 11 57 104      Thank you for the quick answer. I did that, but now I get another exception: "Procedure or function ' getID()' cannot be found in database ' otrs'." which is weird, because the procedure is definitely in the database. Changed the code just now :) –  OhSnap Dec 20 '11 at 8:03      After researching a little I found out that this exception is mostly related to lower-case upper-case, but the db-name is lower case in the connection string as well as in the db. –  OhSnap Dec 20 '11 at 8:39      You need to use just the name getid when the command type is CommandType.StoredProcedure and not call getID(). I updated the question to reflect this –  Conrad Frix Dec 20 '11 at 15:18 add a comment  |  up vote 1 down vote

You never actually execute cmd. Also, as you mentioned above, you don't need the parenthesis for the proc call. It should look something like this.

MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter("getID", con);
DataSet DS = new DataSet();
mySqlDataAdapter.Fill(DS);
share answered Oct 5 '15 at 21:05 Brett Spence 29 4 add a comment  | 

Your Answer

  draft saved draft discarded

Sign up or log in

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Post as a guest

Name Email discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged mysql vb.net stored-procedures datagridview or ask your own question.

相关推荐:大型网站动态应用系统架构设计思想

http://www.y513.com/201010658.html   大型动态应用系统平台主要是针对于大流量、高并发网站建立的底层系统架构。大型网站的运行需要一个可靠、安全、可扩展、

1、概述在我开始构思这几篇关于“自己动手设计ESB中间件”的文章时,曾有好几次动过放弃的念头。原因倒不是因为对冗长的文章产生了惰性,而是ESB中所涉及到的技术知识和需要突破的设计难点实

相关阅读排行


用户评论

游客

相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。