In this case, you use the sqlparameter constructor:
sqlparameter(string,object). Set the object to zero means the sqlparameter
is nothing.
You can declare the sqlparameter as sqlparameter(string, sqldbtype), then
assign the parameter to a value.
Sincerely,
Kevin
Microsoft Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! -
www.microsoft.com/security
--------------------
| From: "Jim Butler" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
<#
[email protected]>
<
[email protected]>
<ODoSkN#
[email protected]>
<
[email protected]>
| Subject: Re: ado.net parameters/sp error?
| Date: Mon, 25 Aug 2003 14:55:59 -0400
| Lines: 255
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <
[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: user1.sonu.org 12.39.200.113
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:59242
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Kevin,
|
| I really appreciate your work on this, but it's probably a moot issue
anyway
| because we usually use variables for most stuff....
|
| the 0 would represent the actual value 0 for the proc not the datatype,
|
| anyway, try this, no proc needed, just some vb stuff....
|
| Dim Params(5) As SqlParameter
| dim i as int16=0
|
| Params(0) = New SqlParameter("@v1", 0)
| Params(1) = New SqlParameter("@v2", 1)
| Params(2) = New SqlParameter("@v3", "test")
| Params(3) = New SqlParameter("@v4", i
| Params(4) = New SqlParameter("@v5", "0")
|
| debug.writeline(params(0).value)
| debug.writeline(params(1).value)
| debug.writeline(params(2).value)
| debug.writeline(params(3).value)
| debug.writeline(params(4).value)
|
| Output is:
| Nothing
| 1
| test
| 0
| 0
|
| I just don't understand the nothing for the first output....
|
| thanks again,
|
| jim
|
|
|
| | > Hi Jim,
| >
| > If I understand properly, the 0 number represents a data type in
| > SqlParameter("@CategoryID", 0)), the 0 means default and doesn't address
| > any data type.
| >
| > You have to set a data type in the SQLParmeter function. For example:
| >
| >
http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp?frame=true
| >
| > [Visual Basic]
| > 'Initialize the connection string and command text
| > 'These will form the key used to store and retrieve the parameters
| > Const CONN_STRING As String = _
| > "SERVER=(local); DATABASE=Northwind; INTEGRATED SECURITY=True;"
| > Dim sql As String = _
| > "SELECT ProductName FROM Products WHERE CategoryID=@Cat " + _
| > "AND SupplierID = @Sup"
| >
| > 'Cache the parameters
| > Dim paramsToStore(1) As SqlParameter
| > paramsToStore(0) = New SqlParameter("
@cat", SqlDbType.Int)
| > paramsToStore(1) = New SqlParameter("@Sup", SqlDbType.Int)
| > SqlHelperParameterCache.CacheParameterSet(CONN_STRING, sql, _
| > paramsToStore)
| >
| > 'Retrieve the parameters from the cache
| > Dim storedParams(1) As SqlParameter
| > storedParams = SqlHelperParameterCache.GetCachedParameterSet(
| > CONN_STRING, sql)
| > storedParams(0).Value = 2
| > storedParams(1).Value = 3
| >
| > 'Use the parameters in a command
| > Dim ds As DataSet
| > ds = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.Text, sql, _
| > storedParams)
| > =======================
| >
| > HTH.
| >
| > Sincerely,
| >
| > Kevin
| > Microsoft Support
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| > Get Secure! -
www.microsoft.com/security
| >
| > --------------------
| > | From: "Jim Butler" <
[email protected]>
| > | References: <
[email protected]>
| > <
[email protected]>
| > <#
[email protected]>
| > <
[email protected]>
| > | Subject: Re: ado.net parameters/sp error?
| > | Date: Thu, 21 Aug 2003 08:54:41 -0400
| > | Lines: 122
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| > | Message-ID: <ODoSkN#
[email protected]>
| > | Newsgroups: microsoft.public.dotnet.framework.adonet
| > | NNTP-Posting-Host: user1.sonu.org 12.39.200.113
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.framework.adonet:58902
| > | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
| > |
| > | i need to explain some more here... if you take the sample ms data
| > | application block (i used the vb version) and run it, it works fine.
If
| > you
| > | change the code below, it will fail, and if you use sql profilier you
| will
| > | see the how the proc is called.
| > |
| > | from the
| > | cmdSample2_Click
| > |
| > | change
| > |
| > | ds = SqlHelper.ExecuteDataset(txtConnectionString.Text,
| > | CommandType.StoredProcedure, "getProductsByCategory", New
| > | SqlParameter("@CategoryID", 1))
| > |
| > | to
| > |
| > | ds = SqlHelper.ExecuteDataset(txtConnectionString.Text,
| > | CommandType.StoredProcedure, "getProductsByCategory", New
| > | SqlParameter("@CategoryID", 0))
| > |
| > | it will fail, and the proc is called this way (through sql profilier)
| > |
| > | exec getProductsByCategory @CategoryID = default
| > |
| > | this is the problem i am trying to illustrate. It is the exact same
| > problem
| > | i am having when trying to pass a hard coded 0 into a proc when using
a
| db
| > | class (basically the same principles as the ms data application block,
| but
| > | more flexible and added features)
| > |
| > | thanks,
| > |
| > | jim butler
| > |
| > |
| > | | > | > The second parameter in the SqpParameter seems to be the date type,
| > | instead
| > | > of a default value. For example:
| > | >
| > | > Dim Param1 As New SqlParameter("@Description",
| > SqlDbType.NVarChar)
| > | >
| > | > Param1.Value = Description
| > | >
| > | > Command.Parameters.Add(Param1)
| > | >
| > | >
| > | >
| > | >
| > | > Sincerely,
| > | >
| > | > Kevin
| > | > Microsoft Support
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | rights.
| > | > Get Secure! -
www.microsoft.com/security
| > | >
| > | > --------------------
| > | > | From: "Jim Butler" <
[email protected]>
| > | > | References: <
[email protected]>
| > | > <
[email protected]>
| > | > | Subject: Re: ado.net parameters/sp error?
| > | > | Date: Wed, 20 Aug 2003 09:29:48 -0400
| > | > | Lines: 38
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| > | > | Message-ID: <#
[email protected]>
| > | > | Newsgroups: microsoft.public.dotnet.framework.adonet
| > | > | NNTP-Posting-Host: user1.sonu.org 12.39.200.113
| > | > | Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| > | > | Xref: cpmsftngxa06.phx.gbl
| > | microsoft.public.dotnet.framework.adonet:58730
| > | > | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
| > | > |
| > | > | thanks for the reply, but it does not answer nor address the issue
| > that
| > | i
| > | > | presented.
| > | > |
| > | > | The point is, why is ado.net changing a value of 0 to default when
| > | calling
| > | > | the stored procedure. especially since i can wrap the zero in
| quotes
| > or
| > | > | pass it in as a variable or pass in any other value (ie 1,2,3) and
| it
| > | will
| > | > | work fine.
| > | > |
| > | > | jim
| > | > |
| > | > |
| > | > | | > | > | > >' proc fails for line below/with sql profilier output
| > | > | > >Params(0) = New SqlParameter("@site_id", 0)
| > | > | > >'sql profilier call
| > | > | > >exec usp_info @site_id = default
| > | > | > >' error returned
| > | > | > >expects parameter '@site_id', which was not supplied (i
| > | > | > >don't have a default supplied in the proc declaration, but
| > | > | > >i don't think i should need one, it should use 0)
| > | > | >
| > | > | > Nothing is implicit about SQL Server -- it is considered good
| > | > | > programming practice to always supply a default value for
| parameters
| > | > | > in stored procedures. This allows you to validate incoming
values
| > | > | > against the defaults. For example, you might have a stored
| procedure
| > | > | > that is contingent on an order date being supplied. So you'd
| declare
| > | > | > it as @OrderDate datetime = NULL and then in your T-SQL test to
| see
| > if
| > | > | > one was supplied: IF @OrderDate IS NULL ... The bottom line is
| that
| > | > | > SQL Server isn't going to assume anything. If you want it to use
| 0,
| > | > | > then declare that the default value.
| > | > | >
| > | > | > -- Mary
| > | > | > MCW Technologies
| > | > | >
http://www.mcwtech.com
| > | > | >
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
