Problem with Bit DataType being passed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Bit DataType being passed

Author

Topic

TomDaJester
Starting Member

2 Posts

Posted - 2009-07-17 : 11:24:42

I have a recordset that contains a logical 1 or 0. Of course it is a BIT datatype. I'm using the recordset to duplicate a row then insert it back into the same table. When the data is passed to the INSERT statement the logical is converted to "True" or "False" and I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Here is the code. The field is in bold:

Dim OptionsSource
Dim OptionsSource_numRows

Set OptionsSource = Server.CreateObject("ADODB.Recordset")
OptionsSource.ActiveConnection = MM_ConnectionWAGSQL_STRING
OptionsSource.Source = "SELECT * FROM dbo.ItemOptions WHERE ProdID = '" + DupID + "' ORDER BY OptionNo ASC"
OptionsSource.CursorType = 0
OptionsSource.CursorLocation = 3
OptionsSource.LockType = 1
OptionsSource.Open()

while not OptionsSource.EOF
Set sqlCmd = Server.CreateObject("ADODB.Command")
sqlCmd.ActiveConnection = MM_ConnectionWAGSQL_STRING
sqlCmd.CommandText = "insert into dbo.ItemOptions (ProdID, OptionNo, OptionType, OptionTitle, OptionPrice, Required, TextVal, TextChar, OptionVal) values ('" & ProdId & "', '" & OptionsSource.fields("OptionNo") &"', '" & OptionsSource.fields("OptionType") &"', '" & OptionsSource.fields("OptionTitle") &"', " & OptionsSource.fields("OptionPrice") &", "&OptionsSource.fields("Required")& ", '" & MyReplace(OptionsSource.fields("TextVal"), "'", "''") &"', " & OptionsSource.fields("TextChar") &", '" & MyReplace(OptionsSource.fields("OptionVal"), "'", "''") & "')"

I'd greatly appreciate any help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-17 : 12:16:36

...

Convert(bit,"&OptionsSource.fields("Required")& ")

TomDaJester
Starting Member

2 Posts

Posted - 2009-07-17 : 12:53:16

Thank You Russell. I took another run at it and did it a different way, but I'll keep this reply in mind for future reference. Here's how I made it work. More lines, but effective:

Dim OptionsSource
Dim OptionsSource_numRows

Set OptionsSource = Server.CreateObject("ADODB.Recordset")
OptionsSource.ActiveConnection = MM_ConnectionWAGSQL_STRING
OptionsSource.Source = "SELECT * FROM dbo.ItemOptions WHERE ProdID = '" + DupID + "' ORDER BY OptionNo ASC"
OptionsSource.CursorType = 0
OptionsSource.CursorLocation = 3
OptionsSource.LockType = 1
OptionsSource.Open()

If NOT OptionsSource.eof THEN
If OptionsSource.fields("Required") = True Then
ReqVal = 1
Elseif OptionsSource.fields("Required") = False Then
ReqVal = 0
End If

End If
%>

<br>

<%
while not OptionsSource.EOF
Set sqlCmd = Server.CreateObject("ADODB.Command")
sqlCmd.ActiveConnection = MM_ConnectionWAGSQL_STRING
sqlCmd.CommandText = "insert into dbo.ItemOptions (ProdID, OptionNo, OptionType, OptionTitle, OptionPrice, Required, TextVal, TextChar, OptionVal) values ('" & ProdId & "', '" & OptionsSource.fields("OptionNo") &"', '" & OptionsSource.fields("OptionType") &"', '" & OptionsSource.fields("OptionTitle") &"', " & OptionsSource.fields("OptionPrice") &", "&ReqVal& ", '" & MyReplace(OptionsSource.fields("TextVal"), "'", "''") &"', '" & OptionsSource.fields("TextChar") &"', '" & MyReplace(OptionsSource.fields("OptionVal"), "'", "''") & "')"

Thaks again for the quick response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources