Sending null values in SQL statements

[James Bowman]

I am using an authoring program called Authorware to send
student scores to ASP pages which send SQL INSERT and
UPDATE statements to an Access database. Everything
works well as long as I provide a value for each variable
that places a value in one of the Access database
fields. For both the INSERT and UPDATE statements, I
would like to provide "real" values for some fields and
null values for the others. For example, I would like to
INSERT a new record with strings in some fields and null
values in others. Later, an UPDATE statement may place
values in some of the null fields and place no value in
the other null fields.

I use two ASP pages: one for INSERT and one for UPDATE.
I would like to use one complete SQL statement in each
ASP page that specifies all fields. Then, I would
provide values for some fields and not others. In my
tests, I'm unable to do this. Is there a workaround?

Thanks.

Jim Bowman

[Bob Lehmann]

You could write a function that formats the value....

Function FormatDBStr(Field)
If Not Field = "" Then
FormatDBStr = "'" & Field & "'"
Else
FormatDBStr = "NULL"
End If
End Function

sql = "UPDATE table_name SET field_name=" & FormatDBStr(FieldValue) & "
WHERE unique_field =something"

Bob Lehmann



"James Bowman" <james@jbassoc.biz> wrote in message
news:10b7a01c44066$5cba4690$a601280a@phx.gbl...

> I am using an authoring program called Authorware to send
> student scores to ASP pages which send SQL INSERT and
> UPDATE statements to an Access database. Everything
> works well as long as I provide a value for each variable
> that places a value in one of the Access database
> fields. For both the INSERT and UPDATE statements, I
> would like to provide "real" values for some fields and
> null values for the others. For example, I would like to
> INSERT a new record with strings in some fields and null
> values in others. Later, an UPDATE statement may place
> values in some of the null fields and place no value in
> the other null fields.
>
> I use two ASP pages: one for INSERT and one for UPDATE.
> I would like to use one complete SQL statement in each
> ASP page that specifies all fields. Then, I would
> provide values for some fields and not others. In my
> tests, I'm unable to do this. Is there a workaround?
>
> Thanks.
>
> Jim Bowman
>

[James Bowman]

Thanks Bob. Based on your input, I put this code in the
asp page and it works well:

if myvar="NULL" then
SQL=SQL & "FieldName=NULL,"
else
SQL=SQL & "FieldName='" & myvar & "',"
end if

SQL is the UPDATE statement. The above code is inserted
inside the SQL string.

I note that you use the Function/End Function format. I
used a simple if-then statement. What's the value of the
Function/End Function format in this case?

Thanks.

James

>-----Original Message-----
>You could write a function that formats the value....
>
>Function FormatDBStr(Field)
> If Not Field = "" Then
> FormatDBStr = "'" & Field & "'"
> Else
> FormatDBStr = "NULL"
> End If
> End Function
>
>sql = "UPDATE table_name SET field_name=" & FormatDBStr

(FieldValue) & "

>WHERE unique_field =something"
>
>Bob Lehmann
>
>
>
>"James Bowman" <james@jbassoc.biz> wrote in message
>news:10b7a01c44066$5cba4690$a601280a@phx.gbl...

>> I am using an authoring program called Authorware to

send

>> student scores to ASP pages which send SQL INSERT and
>> UPDATE statements to an Access database. Everything
>> works well as long as I provide a value for each

variable

>> that places a value in one of the Access database
>> fields. For both the INSERT and UPDATE statements, I
>> would like to provide "real" values for some fields and
>> null values for the others. For example, I would like

to

>> INSERT a new record with strings in some fields and

null

>> values in others. Later, an UPDATE statement may place
>> values in some of the null fields and place no value in
>> the other null fields.
>>
>> I use two ASP pages: one for INSERT and one for

UPDATE.

>> I would like to use one complete SQL statement in each
>> ASP page that specifies all fields. Then, I would
>> provide values for some fields and not others. In my
>> tests, I'm unable to do this. Is there a workaround?
>>
>> Thanks.
>>
>> Jim Bowman
>>

>
>
>.
>

[Bob Lehmann]

The function can be reused so you don't have to keep writing the same,
lengthier code over and over.

Bob Lehmann

"James Bowman" <james@jbassoc.biz> wrote in message
news:1222401c44286$d8201a90$a601280a@phx.gbl...

> Thanks Bob. Based on your input, I put this code in the
> asp page and it works well:
>
> if myvar="NULL" then
> SQL=SQL & "FieldName=NULL,"
> else
> SQL=SQL & "FieldName='" & myvar & "',"
> end if
>
> SQL is the UPDATE statement. The above code is inserted
> inside the SQL string.
>
> I note that you use the Function/End Function format. I
> used a simple if-then statement. What's the value of the
> Function/End Function format in this case?
>
> Thanks.
>
> James
>

> >-----Original Message-----
> >You could write a function that formats the value....
> >
> >Function FormatDBStr(Field)
> > If Not Field = "" Then
> > FormatDBStr = "'" & Field & "'"
> > Else
> > FormatDBStr = "NULL"
> > End If
> > End Function
> >
> >sql = "UPDATE table_name SET field_name=" & FormatDBStr

> (FieldValue) & "

> >WHERE unique_field =something"
> >
> >Bob Lehmann
> >
> >
> >
> >"James Bowman" <james@jbassoc.biz> wrote in message
> >news:10b7a01c44066$5cba4690$a601280a@phx.gbl...

> >> I am using an authoring program called Authorware to

> send

> >> student scores to ASP pages which send SQL INSERT and
> >> UPDATE statements to an Access database. Everything
> >> works well as long as I provide a value for each

> variable

> >> that places a value in one of the Access database
> >> fields. For both the INSERT and UPDATE statements, I
> >> would like to provide "real" values for some fields and
> >> null values for the others. For example, I would like

> to

> >> INSERT a new record with strings in some fields and

> null

> >> values in others. Later, an UPDATE statement may place
> >> values in some of the null fields and place no value in
> >> the other null fields.
> >>
> >> I use two ASP pages: one for INSERT and one for

> UPDATE.

> >> I would like to use one complete SQL statement in each
> >> ASP page that specifies all fields. Then, I would
> >> provide values for some fields and not others. In my
> >> tests, I'm unable to do this. Is there a workaround?
> >>
> >> Thanks.
> >>
> >> Jim Bowman
> >>

> >
> >
> >.
> >