参数化vb.net中的存储过程(vs 2010 express) [英] parameterizes stored procedure in vb.net (vs 2010 express)
本文介绍了参数化vb.net中的存储过程(vs 2010 express)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
的MySQL>创建过程登录(在用户varchar(20)中,在传递varchar(20),out date1
date)
- >开始
- > set date1 =(从emp3中选择Dt,其中userid = user,密码= pass);
- >结束$$
查询OK,0行受影响(0.00秒)
Dim con As MySqlConnection = 新 MySqlConnection( Server = localhost; Database = siddhu; Uid = root; Password = 1234;)
Dim 消息作为 String = [ String ]。清空
Dim cmd 作为 MySqlCommand = 新 MySqlCommand( login,con)
con.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(新 MySqlParameter( user,MySqlDbType.VarChar))
cmd.Parameters( user)。Direction = ParameterDirection.Input
cmd.Parameters( < span class =code-string> user)。值= prasad
cmd.Parameters.Add(新 MySqlParameter( 传递,MySqlDbType.VarChar))
cmd.Parameters( 传递)。Direction = ParameterDirection.Input
cmd.Parameters( pass).Value = 530
cmd.Parameters。添加(新 MySqlParameter( @ date1,MySqlDbType。日期))
cmd.Parameters( @ date1)。Direction = ParameterDirection.Output
尝试
Dim i As 整数
i = cmd.ExecuteNonQuery()
如果 i> 0 然后
MessageBox.Show(cmd.Parameters( @ date1)。值)
其他
MessageBox.Show(i)
结束 如果
Catch ex As Exception
MessageBox.Show(ex.ToString())
结束 尝试
con.Close()
它总是给我输出为零
解决方案
查询OK,0行受影响(0.00秒)
昏暗 con As MySqlConnection = 新 MySqlConnection(< span class =code-string> Server = localhost; Database = siddhu; Uid = root; Password = 1234;)
Dim 消息作为 字符串 = [ String ]。清空
Dim cmd As MySqlCommand = 新 MySqlCommand( login,con)
con.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( New MySqlParameter ( user,MySqlDbType.VarChar))
cmd.Parameters( user)。Direction = ParameterDirection.Input
cmd.Parameters(矿泉n> user)。值= prasad
cmd.Parameters.Add( New MySqlParameter( < span class =code-string> pass,MySqlDbType.VarChar))
cmd.Parameters( pass)。Direction = ParameterDirection.Input
cmd.Parameters( 传递)。值= 530
cmd .Parameters.Add( New MySqlParameter( @ date1 ,MySqlDbType。日期))
cmd.Parameters( @ date1)。Direction = ParameterDirection.Output
尝试
Dim i As 整数
i = cmd.ExecuteNonQuery()
如果 i> 0 然后
MessageBox.Show(cmd.Parameters( @ date1)。值)
其他
MessageBox.Show(i)
结束 如果
Catch ex As Exception
MessageBox.Show(ex.ToString())
结束 尝试
con.Close()
它总是给我输出为零
我不确定executetenonquery会返回1,我建议你检查date1的值,无论哪种方式,看看它是什么。
接收输出你必须写一行将接收输出见下面代码的下划线部分...
c md.Parameters.Add( New MySqlParameter( @ date1 ,MySqlDbType。日期))
cmd.Parameters( @ date1)。Direction = ParameterDirection.Output
' 然后执行......
dim outputdate = cmd.parameters( 0 )。值 ' 输出参数date1的索引
如果您要插入/更新/删除记录,请使用< br />
cmd .ExecuteNonQuery()< br />
< br />
如果只是选择< br />
cmd.ExecuteQuery()
快乐编码!
:)
mysql> create procedure login(in user varchar(20),in pass varchar(20),out date1
date)
-> begin
-> set date1 = (select Dt from emp3 where userid=user and password = pass);
-> end$$
Query OK, 0 rows affected (0.00 sec)
Dim con As MySqlConnection = New MySqlConnection("Server=localhost;Database=siddhu;Uid=root;Password=1234;")
Dim message As String = [String].Empty
Dim cmd As MySqlCommand = New MySqlCommand("login", con)
con.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New MySqlParameter("user", MySqlDbType.VarChar))
cmd.Parameters("user").Direction = ParameterDirection.Input
cmd.Parameters("user").Value = "prasad"
cmd.Parameters.Add(New MySqlParameter("pass", MySqlDbType.VarChar))
cmd.Parameters("pass").Direction = ParameterDirection.Input
cmd.Parameters("pass").Value = "530"
cmd.Parameters.Add(New MySqlParameter("@date1", MySqlDbType.Date))
cmd.Parameters("@date1").Direction = ParameterDirection.Output
Try
Dim i As Integer
i = cmd.ExecuteNonQuery()
If i > 0 Then
MessageBox.Show(cmd.Parameters("@date1").Value)
Else
MessageBox.Show(i)
End If
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
con.Close()
always it give me output as zero
解决方案
Query OK, 0 rows affected (0.00 sec)
Dim con As MySqlConnection = New MySqlConnection("Server=localhost;Database=siddhu;Uid=root;Password=1234;") Dim message As String = [String].Empty Dim cmd As MySqlCommand = New MySqlCommand("login", con) con.Open() cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New MySqlParameter("user", MySqlDbType.VarChar)) cmd.Parameters("user").Direction = ParameterDirection.Input cmd.Parameters("user").Value = "prasad" cmd.Parameters.Add(New MySqlParameter("pass", MySqlDbType.VarChar)) cmd.Parameters("pass").Direction = ParameterDirection.Input cmd.Parameters("pass").Value = "530" cmd.Parameters.Add(New MySqlParameter("@date1", MySqlDbType.Date)) cmd.Parameters("@date1").Direction = ParameterDirection.Output Try Dim i As Integer i = cmd.ExecuteNonQuery() If i > 0 Then MessageBox.Show(cmd.Parameters("@date1").Value) Else MessageBox.Show(i) End If Catch ex As Exception MessageBox.Show(ex.ToString()) End Try con.Close()
always it give me output as zero
I am not sure that executenonquery will return 1, I suggest you check the value of date1 either way and see what it is.
to receive out put you have to write a line that will receive output see underlined portion of below code...
cmd.Parameters.Add(New MySqlParameter("@date1", MySqlDbType.Date)) cmd.Parameters("@date1").Direction = ParameterDirection.Output 'execute then... dim outputdate = cmd.parameters(0).Value 'Index of your Output parameter date1
if you are inserting/updating/deleting record then use<br /> cmd.ExecuteNonQuery()<br /> <br /> if just selecting then<br /> cmd.ExecuteQuery()
Happy Coding!
:)
这篇关于参数化vb.net中的存储过程(vs 2010 express)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文