Hello.

Following SQL-statement works directly in Access

SELECT * FROM Users_ as U, Courses as C
WHERE (U.course_id = @course_id or @course_id is null)
AND U.Course_id = C.Course_id

Now, I'm trying to execute the Sql-statement from an AS.NET
application
It works as long as I don't specify null values for course_id.

When I do, using the following:

Dim cmdString As String = _
String.Format("SELECT * FROM Users_ as U, Courses as C " + _
"WHERE (U.course_id = @course_id or @course_id is
null) " + _
"AND U.Course_id = C.Course_id ")

Dim dbCommand As New OleDbCommand()
dbCommand.CommandText = cmdString
dbCommand.Connection = _dbConnection
Dim dataAdapt As New OleDbDataAdapter()
dataAdapt.SelectCommand = dbCommand

' for testing purposes
course_id = 0

Dim dbParam_CourseID As New OleDbParameter
dbParam_CourseID.ParameterName = "@course_id"
dbParam_CourseID.IsNullable = True
dbParam_CourseID.SourceColumnNullMapping = True
dbParam_CourseID.Value = IIf(course_id = 0, Nothing, course_id)
'passing null
dbCommand.Parameters.Add(dbParam_CourseID)

Dim objDataSet As New DataSet()
dataAdapt.Fill(objDataSet)

I get an error: "Parameter @course_id has no default value."

In access I explicitly specified 'Null' as default value for field
'course_id' but I still get the error

How to solve this?

thank you
Chris

On Apr 22, 11:45*am, Chris <cmr...@gmail.com> wrote:
> Hello.
>
> Following SQL-statement works directly in Access
>
> SELECT * FROM Users_ as U, Courses as C
> WHERE (U.course_id = @course_id or @course_id is null)
> AND * * U.Course_id = C.Course_id
>
> Now, I'm trying to execute the Sql-statement from an AS.NET
> application
> It works as long as I don't specify null values for course_id.
>
> When I do, using the following:
>
> * * * Dim cmdString As String = _
> * * * * String.Format("SELECT * FROM Users_ as U, Courses as C " + _
> * * * * * * * * * * "WHERE (U.course_id = @course_id or @course_id is
> null) " + _
> * * * * * * * * * * "AND U.Course_id = C.Course_id ")
>
> * * * Dim dbCommand As New OleDbCommand()
> * * * dbCommand.CommandText = cmdString
> * * * dbCommand.Connection = _dbConnection
> * * * Dim dataAdapt As New OleDbDataAdapter()
> * * * dataAdapt.SelectCommand = dbCommand
>
> * * * ' for testing purposes
> * * * * course_id = 0
>
> * * * * Dim dbParam_CourseID As New OleDbParameter
> * * * dbParam_CourseID.ParameterName = "@course_id"
> * * * dbParam_CourseID.IsNullable = True
> * * * dbParam_CourseID.SourceColumnNullMapping = True
> * * * dbParam_CourseID.Value = IIf(course_id = 0, Nothing, course_id)
> 'passing null
> * * * dbCommand.Parameters.Add(dbParam_CourseID)
>
> * * * * Dim objDataSet As New DataSet()
> * * * dataAdapt.Fill(objDataSet)
>
> I get an error: "Parameter @course_id has no default value."
>
> In access I explicitly specified 'Null' as default value for field
> 'course_id' but I still get the error
>
> How to solve this?
>
> thank you
> Chris

Instead of using Nothing in IIf(course_id = 0, Nothing, course_id) try
DBNull.Value

"Chris" <(E-Mail Removed)> wrote in message
news:a5333516-1786-40a7-bec8-(E-Mail Removed)...
> Hello.
>
> Following SQL-statement works directly in Access
>
> SELECT * FROM Users_ as U, Courses as C
> WHERE (U.course_id = @course_id or @course_id is null)
> AND U.Course_id = C.Course_id
> <etc>

The most probable solution is DBNull.Value instead of Nothing, but I
question the SQL, as it can return two things:

1. A list of all courses that link
2. Only the course that matches

Do you really want everything if there is no match? Think this through. I
know the answer may be yes, at a high level, but the question is in a
particular user story is a bucket o' everything equivalent to a single
course. In other words, should the two not fulfill different requirements.

Example:

User clicks on item on list, ID will always be non-null
(to fill list, however, you just get all or get all by parameter)

User types in particular course id and gets single item

At the point an ID is found, does it not make sense to show the information
differently, including some information that may not make sense in the
original grid?

In your application, I might be off, but I do list/detail type work all the
time and rarely do I find the list and detail working the same way. I would
rather give a "cannot find for that id" than have the user get a list. I can
offer "would you like to see the entire list", but if the user is looking
for something in particular, having him go through everything is not usually
a good option. It is more likely the user will try search again, thus
wasting all of the cycles and reducing the scalability of the app for
nothing.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

Gregory A. Beamer wrote:
>
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:a5333516-1786-40a7-bec8-(E-Mail Removed)...
>> Hello.
>>
>> Following SQL-statement works directly in Access
>>
>> SELECT * FROM Users_ as U, Courses as C
>> WHERE (U.course_id = @course_id or @course_id is null)
>> AND U.Course_id = C.Course_id
>> <etc>
>
> The most probable solution is DBNull.Value instead of Nothing, but I
> question the SQL, as it can return two things:
>
> 1. A list of all courses that link
> 2. Only the course that matches
>
> Do you really want everything if there is no match? Think this through.
> I know the answer may be yes, at a high level, but the question is in a
> particular user story is a bucket o' everything equivalent to a single
> course. In other words, should the two not fulfill different requirements.

Is the to let the same SP serve two purposes: return the users in a
given course, or return ALL users (which is the result when @courseID is
NULL)? Whether this SQL accomplishes this depends on the conventions
being followed for the equality operator when one or both sides are
NULL. In SQL Server, I think this depends on the compatibility level set
for the database.