Hello ASP experts - I have some 1

[JohnBates]

Hello ASP experts -

I have some checkboxes on my input form (ASP)

When I get to the ASP that performs the INSERT
to write the record, the boolean values for the checkboxes are correct... they are ON or OFF
correctly. But the INSERT statement fails with the error mentioned below.

What is the correct syntax to write the value to
the bit fields?

Thankyou. John


*** The Error *****
Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting the varchar value ', ' to a column of data type bit.
*******************************
In the ASP where the error occurs:

<%
Dim blnNewWS
blnNewWS = Request.Form(&quot;NewWS&quot

sql = &quot;INSERT INTO SystemsAccessReqs (UserNameL, UserNameF, UserNameMI, UserPhoneW, UserPhoneH, UserFax, Dept#, JobTitle, NewWS, ExistingWS, NewUser, ExistingUser, Marcam, JDE, WMS, Payroll, TA, NT, NTRoaming, LNotes, ColorLaser, DialUpAccess, Modem, LNotesGroup, ModelAS400Profile, ModelNTProfile, SerReqComments, DateSubmitted) VALUES ('&quot; & strNameLast & &quot;', '&quot; & strNameFirst & &quot;', '&quot; & strNameMI & &quot;', '&quot; & strPhoneW & &quot;', '&quot; & strPhoneH & &quot;', '&quot; & strPhoneFax & &quot;', '&quot; & intDept & &quot;', '&quot; & strJobTitle & &quot;', '&quot; & blnNewWS & &quot;', '&quot; & blnExWS & &quot;', '&quot; & blnNewUser & &quot;', '&quot; & blnExUser & &quot;', '&quot; & blnMarcam & &quot;', '&quot; & blnJDE & &quot;', '&quot; & blnWMS & &quot;', '&quot; & blnPR & &quot;', '&quot; & blnTA & &quot;', '&quot; & blnNT & &quot;', '&quot; & blnNTRoam & &quot;', '&quot; & blnLNotes & &quot;', '&quot; & blnCLaser & &quot;', '&quot; & blnDialUp & &quot;', '&quot; & blnModem & &quot;', '&quot; & strModelAS400 & &quot;', '&quot; & strModelNT & &quot;', '&quot; & strLNotesGroup & &quot;', '&quot; & strSRComments & &quot;', &quot; & datCurDate & &quot;&quot;

conn.execute sql
%>

 

[rac2]

I don't think you need single quotes around the boolean values. Are the boolean values, bit values 0 and 1?

Instead of this -

",'" & blnNewWS & "', '" & blnExWS & "',

try this -

", " & blnNewWS & ", " & blnExWS & ",

 

[Mighty]

If they are just checkboxes, then the blnNewWS variables is just going to contain either &quot;on&quot; or &quot;off&quot;. I have never used SQL server but I know that this will not work with MS Access. You either need to use true/false or 0/1. You will have to translate your on/off to one of these values. You could try writing a little translate function something like:

function translate(status)
if status = &quot;on&quot; then
translate = true
else
translate = false
end if
end function

Then just call this function to insert the values into your SQL command (without the quotes as suggested by rac2):

....strJobTitle & &quot;', &quot; & translate(blnNewWS) & &quot;, &quot; & translate(blnExWS) & &quot;, &quot; & translate(blnNewUser) & &quot;, &quot; & translate(blnExUser) & &quot;..............
Mise Le Meas,

Mighty

 

[JohnBates]

Thanks Mighty and rac2 !

Good suggestions. John

 

[JohnBates]

Mighty - I like the idea of using the function.

As I mentioned, the data type in my SQL Server table is bit
which according to Books Online can have values of 1, 0 or null....

When I used your function, I got error Type mismatch: Translate (the Line # is the INSERT stmt)

So, I removed the function and just used
&quot; & Cbool(blnNewWS) & &quot;,

got error Type mismatch: 'Cbool' (the Line # is the INSERT stmt)

In each case I tried with all checkboxes unchecked and again with all checked.

ok I admit it.... I'm lost.
Any ideas? Thanks alot. John

 

[Mighty]

Try just changing the function to return numbers:

function translate(status)
if status = &quot;on&quot; then
translate = 1
else
translate = 0
end if
end function

Mise Le Meas,

Mighty

 

[JohnBates]

Mighty - I had already tried that. I have confirmed that
the value is &quot;on&quot; when a box is checked...

here is the entire ASP:
Again, Thanks John

<!--#include file=&quot;ISSystemsAccessConnect.asp&quot;-->


function translate(status)
if status = &quot;on&quot; then
translate = 1
else
translate = 0
end if
end function


<%
Dim sql
Dim strNameLast, strNameFirst, strNameMI, strPhoneW, strPhoneH, strPhoneFax
Dim intDept, strJobTitle, datCurDate
Dim blnNewWS, blnExWS, blnNewUser, blnExUser, blnMarcam, blnJDE, blnWMS
Dim blnPR, blnTA, blnNT, blnNTRoam, blnLNotes, blnCLaser, blnDialUp, blnModem
Dim strModelAS400, strModelNT, strLNotesGroup, strSRComments


strNameLast = Request.Form(&quot;NameLast&quot
strNameFirst = Request.Form(&quot;NameFirst&quot
strNameMI = Request.Form(&quot;NameMI&quot
strPhoneW = Request.Form(&quot;PhoneW&quot
strPhoneH = Request.Form(&quot;PhoneH&quot
strPhoneFax = Request.Form(&quot;PhoneFax&quot
intDept = Request.Form(&quot;Dept&quot
strJobTitle = Request.Form(&quot;JobTitle&quot
blnNewWS = Request.Form(&quot;NewWS&quot
blnExWS = Request.Form(&quot;ExWS&quot
blnNewUser = Request.Form(&quot;NewUser&quot
blnExUser = Request.Form(&quot;ExUser&quot
blnMarcam = Request.Form(&quot;Marcam&quot
blnJDE = Request.Form(&quot;JDE&quot
blnWMS = Request.Form(&quot;WMS&quot
blnPR = Request.Form(&quot;PR&quot
blnTA = Request.Form(&quot;TA&quot
blnNT = Request.Form(&quot;NT&quot
blnNTRoam = Request.Form(&quot;NTRoam&quot
blnLNotes = Request.Form(&quot;LNotes&quot
blnCLaser = Request.Form(&quot;CLaser&quot
blnDialUp = Request.Form(&quot;DialUp&quot
blnModem = Request.Form(&quot;Modem&quot
strModelAS400 = Request.Form(&quot;ModelAS400&quot
strModelNT = Request.Form(&quot;ModelNT&quot
strLNotesGroup = Request.Form(&quot;LNotesGroup&quot
strSRComments = Request.Form(&quot;SRComments&quot

datCurDate = Date




sql = &quot;INSERT INTO SystemsAccessReqs (UserNameL, UserNameF, UserNameMI, UserPhoneW, UserPhoneH, UserFax, JobTitle, NewWS, ExistingWS, NewUser, ExistingUser, Marcam, JDE, WMS, Payroll, TA, NT, NTRoaming, LNotes, ColorLaser, DialUpAccess, Modem, LNotesGroup, ModelAS400Profile, ModelNTProfile, SerReqComments, DateSubmitted) VALUES ('&quot; & strNameLast & &quot;', '&quot; & strNameFirst & &quot;', '&quot; & strNameMI & &quot;', '&quot; & strPhoneW & &quot;', '&quot; & strPhoneH & &quot;', '&quot; & strPhoneFax & &quot;', '&quot; & strJobTitle & &quot;', &quot; & Translate(blnNewWS) & &quot;, &quot; & Translate(blnExWS) & &quot;, &quot; & Translate(blnNewUser) & &quot;, &quot; & Translate(blnExUser) & &quot;, &quot; & Translate(blnMarcam) & &quot;, &quot; & Translate(blnJDE) & &quot;, &quot; & Translate(blnWMS) & &quot;, &quot; & Translate(blnPR) & &quot;, &quot; & Translate(blnTA) & &quot;, '&quot; & Translate(blnNT) & &quot;, &quot; & Translate(blnNTRoam) & &quot;, &quot; & Translate(blnLNotes) & &quot;, &quot; & Translate(blnCLaser) & &quot;, &quot; & Translate(blnDialUp) & &quot;, &quot; & Translate(blnModem) & &quot;, '&quot; & strModelAS400 & &quot;', '&quot; & strModelNT & &quot;', '&quot; & strLNotesGroup & &quot;', '&quot; & strSRComments & &quot;', &quot; & datCurDate & &quot;&quot;

conn.execute sql

%>

 

[Mighty]

Still thinking - but have you tried printing out the sql command to the screen to make sure that it looks OK. Mise Le Meas,

Mighty

 

[JohnBates]

Mighty -

here is what we see when we print the sql statement:

INSERT INTO SystemsAccessReqs (UserNameL, UserNameF, UserNameMI, UserPhoneW, UserPhoneH, UserFax, JobTitle, NewWS, ExistingWS, NewUser, ExistingUser, Marcam, JDE, WMS, Payroll, TA, NT, NTRoaming, LNotes, ColorLaser, DialUpAccess, Modem, LNotesGroup, ModelAS400Profile, ModelNTProfile, SerReqComments, DateSubmitted) VALUES ('Jagger', 'Mick', '', '615', '615', '615', 'musician', on, , on, , , , , , , , , , , , , , '', '', '', '', 12/13/01);

I had checked the first 2 ckboxes on the form, remaining boxes were not checked.

*** The error ***
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'on'.

/Forms/ISSystemsAccessWrite.ASP, line 51

 

[JohnBates]

Thanks friends, let's close this one out......

I was able to get the correct bit values written to the SQL Server table, but it isn't pretty: I have 16 bit fields.

Sub SetBitValues

If blnNewWS = &quot;on&quot; Then
blnNewWS = &quot;1&quot;
Else
blnNewWS = &quot;0&quot;
End If

..... there must be a better way.

Thanks for all your ideas. John

 

[CWWarrior]

This is because a bit field has the value of 0 or 1, where a checkbox has the value true or false which vb interprets as -1 for true or 0 for false.

One way to do it would be to say

ssqlinsert = &quot; ....&quot;
if checkbox.value = true then
ssqlinsert = ssqlinsert & 1
else
ssqlinsert = ssqlinsert & 0
End If
...

you could then declare an application(&quot;true&quot variable to be equal to 1 to utilize in all of your bit fields.