从.NET参数化查询SQLServerCE问题 [英] SQLServerCE Problem with parameterized queries from .NET
问题描述
我拉了头发,我的头试图找出这一个。
我不能让参数化查询在VB.Net工作,当我使用的参数。
这是我发现,在函数中调用的参数,从.NET引发错误(见样品code)。但是,运行在查询窗口在Visual Studio中工作不查询工作正常。
提出的错误是:
25922 - 为函数的参数是无效的。
。
信息来源: http://msdn.microsoft.com/en-us /library/aa256772%28SQL.80%29.aspx
样品code:
进口System.Data.SqlServerCe
公共类MiniDemo
公共共享的Sub Main()
昏暗CS作为字符串=数据源= Inventory.sdf;坚持安全信息= FALSE;
昏暗DB作为新的SqlCeConnection(CS)
db.Open()
''#使用参数作品
昏暗CMD作为SqlCeCommand = db.CreateCommand()
cmd.CommandText =INSERT INTO inventory_type(inv_type_id,inv_type_name)VALUES(@id,@name)
cmd.Parameters.Add(@ ID,1)
cmd.Parameters.Add(@名,测试)
cmd.ExecuteNonQuery()
''在功能#使用参数不工作
昏暗CMD2作为SqlCeCommand = db.CreateCommand()
''#Broken对可读性多行(使用真正的code字符串生成器内置)
cmd2.CommandText =
选择
inv_type_id,
inv_type_name
从
inventory_type
哪里
((@id IS NULL)OR(inv_type_id = @id))AND
((@name IS NULL)OR(inv_type_name = @name))
cmd2.Parameters.Add(@ ID,1)
cmd2.Parameters.Add(@名,测试)''#不会与工作的DBNull.Value无论是
昏暗的DA2作为新SqlCeDataAdapter(CMD2)
昏暗DT2作为新的DataTable()
da2.Fill(DT2)
db.Close()
结束小组
末级
最后,我发现这个问题的解决方案。
在一个函数崩溃使用的参数,如果没有设置该参数的DbType属性:
这将崩溃:
昏暗CMD作为SqlCeCommand = db.CreateCommand()
cmd.CommandText =SELECT COALESCE(@参数1,@参数2);
cmd.Parameters.Add(@参数1,1)
cmd.Parameters.Add(@参数2,测试)
cmd.ExecuteScalar()
使用函数中的参数将工作,如果该参数的DbType属性的是设置
这是可行的:
昏暗CMD作为SqlCeCommand = db.CreateCommand()
cmd.CommandText =SELECT COALESCE(@参数1,@参数2);
cmd.Parameters.Add(@参数1,1).DbType = DbType.Int32
cmd.Parameters.Add(@参数2,测试)。的DbType = DbType.String
cmd.ExecuteScalar()
I am pulling the hair out of my head trying to figure this one out.
I can't make Parameterized queries to work in VB.Net, when I am using parameters.
From what I have found, using a parameter in a function, from .NET raises an error (see sample code). However, running the not working query in the Query Window in Visual studio works properly.
The error raised is:
25922 - The arguments for function are not valid.
Info from: http://msdn.microsoft.com/en-us/library/aa256772%28SQL.80%29.aspx
Sample Code:
Imports System.Data.SqlServerCe
Public Class MiniDemo
Public Shared Sub Main()
Dim cs As String = "Data Source=Inventory.sdf; Persist Security Info=False;"
Dim db As New SqlCeConnection(cs)
db.Open()
''#Using parameters works
Dim cmd As SqlCeCommand = db.CreateCommand()
cmd.CommandText = "INSERT INTO inventory_type (inv_type_id, inv_type_name) VALUES (@id, @name)"
cmd.Parameters.Add("@id", 1)
cmd.Parameters.Add("@name", "test")
cmd.ExecuteNonQuery()
''#Using parameters in functions does not work
Dim cmd2 As SqlCeCommand = db.CreateCommand()
''#Broken on multiple lines for readability (built using string builders in real code)
cmd2.CommandText =
"SELECT
inv_type_id,
inv_type_name
FROM
inventory_type
WHERE
((@id IS NULL) OR (inv_type_id = @id)) AND
((@name IS NULL) OR (inv_type_name = @name))"
cmd2.Parameters.Add("@id", 1)
cmd2.Parameters.Add("@name", "test") ''# Would not work with DBNull.Value either
Dim da2 As New SqlCeDataAdapter(cmd2)
Dim dt2 As New DataTable()
da2.Fill(dt2)
db.Close()
End Sub
End Class
Finally, I have found the solution for this problem.
Using a parameter in a function crashes if the DBType property of the parameter is not set:
This will crash:
Dim cmd As SqlCeCommand = db.CreateCommand()
cmd.CommandText = "SELECT COALESCE(@param1, @param2);"
cmd.Parameters.Add("@param1", 1)
cmd.Parameters.Add("@param2", "test")
cmd.ExecuteScalar()
Using a parameter in a function will work if the DBType property of the parameter is set
This will work:
Dim cmd As SqlCeCommand = db.CreateCommand()
cmd.CommandText = "SELECT COALESCE(@param1, @param2);"
cmd.Parameters.Add("@param1", 1).DbType = DbType.Int32
cmd.Parameters.Add("@param2", "test").DbType = DbType.String
cmd.ExecuteScalar()
这篇关于从.NET参数化查询SQLServerCE问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!