Re: SQL query... is there a better way?

From: Chris Taylor (chris_taylor_za_at_hotmail.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 16:30:52 +0200

Hi,

Sorry for the delay. The most likely reason for the exception is that one of
the values in your list is null (Nothing in VB.NET), if that is the case you
should pass DBNull.Value as the parameter value.

Try the following when adding the parameter.
oCmd.Parameters.Add(New SqlParameter(paramName, IIf(IsNothing(value),
DBNull.Value, value)))

Hope this helps 

-- 
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor
"Scott Reynolds" <scott@it-xenius.co.uk> wrote in message
news:OnouqS0$EHA.3708@TK2MSFTNGP14.phx.gbl...
> Hi Chris!
>
> Your code works well, but when I am usin parameterized query, it gives me
> followin error: "No value given for one or more required parameters."
>
> Could you please take a look at code samples below... Do you have any idea
> what may cause this error?
>
>
> Dim paramList As New StringBuilder
> Dim param As Integer = 0
> Dim paramName As String
>
> ' WORKING CODE
> For Each value As Object In values
>     paramName = value.ToString
>     If paramId > 0 Then paramList.Append(",")
>     paramList.Append(paramValue)
>     paramId += 1
> Next
>
> oCmd.CommandText = String.Format("select * from Buildings where
> SubwayStationId IN ({0})", paramList.ToString())
>
> ' NOT WOEKING CODE
> For Each value As Object In values
>     paramName = String.Format("@Param{0}", param)
>     If param > 0 Then paramList.Append(",")
>     paramList.Append(paramName)
>     oCmd.Parameters.Add(New SqlParameter(paramName, value))
>     param += 1
> Next
>
> oCmd.CommandText = String.Format("select * from MyTable where City IN
> ({0})", paramList.ToString())
>
>
> Thanks!
> Scott
>
>
>
> "Chris Taylor" <chris_taylor_za@hotmail.com> wrote in message
> news:ezoNTua$EHA.1260@TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > You could build a comma separated list of the values and use the IN
> > statement as an alternative to the OR not realy significant.
> >
> > Select * FROM MyTable WHERE City IN ('City1', 'City2')
> >
> > Personally I would use a parameterized query, the code might look
> > something
> > like this (untested)
> >
> >      Dim paramList As New StringBuilder
> >      Dim param As Integer = 0
> >      Dim paramName As String
> >      For Each value As Object In values
> >        paramName = String.Format("@Param{0}", param)
> >        If param > 0 Then paramList.Append(",")
> >        paramList.Append(paramName)
> >        oCmd.Parameters.Add(New SqlParameter(paramName, value))
> >        param += 1
> >      Next
> >      oCmd.CommandText = String.Format("select * from MyTable where City
IN
> > ({0})", paramList.ToString())
> >
> > Hope this helps
> >
> > -- 
>
>

Relevant Pages

  • Re: Suggestions to reduce memory use when splitting a string
    ... Public Function SplitDelimitedLine(ByVal CurrentLine As StringBuilder, ... Dim CountDelimiter As Boolean ... Dim lbResult As Boolean ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Suggestions to reduce memory use when splitting a string
    ... Public Function SplitDelimitedLine(ByVal CurrentLine As StringBuilder, ... Dim CountDelimiter As Boolean ... string in the resultant array if you want to get rid of the quotes. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Suggestions to reduce memory use when splitting a string
    ... Public Function SplitDelimitedLine(ByVal CurrentLine As StringBuilder, ... ByRef SplitString() As String) As Boolean ... Dim CountDelimiter As Boolean ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Wrap a div tag around viewstate output
    ... Protected Overrides Sub Render ... Dim re As Regex ... Dim sb As StringBuilder = New StringBuilder ... when you build a page entirely in css in a certain way ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Date/Time Picker Control
    ... concatenation of the SQL string. ... You could avoid any potential issues with the injection into your query ... Dim Conn As ADODB.Connection ... Dim Param As ADODB.Parameter ...
    (microsoft.public.vb.database.ado)