Genius123
asked on
Field type vbscript.
Hello,
I run VBScript from Outlook to populate a text field. I'm grabbing a value from a SQL table:
ctls("lblPartDesc").Captio n = "Part Desc: " & rst.Fields("description")
When the field in SQL is a text field, it gives a data type mismatch error. But when it is a varchar type it's fine. I've tried cstr() and it still doesn't work. Also, I'm not allow to change the field in the sql table to varchar because it's not my database.
Thanks, Joel
I run VBScript from Outlook to populate a text field. I'm grabbing a value from a SQL table:
ctls("lblPartDesc").Captio
When the field in SQL is a text field, it gives a data type mismatch error. But when it is a varchar type it's fine. I've tried cstr() and it still doesn't work. Also, I'm not allow to change the field in the sql table to varchar because it's not my database.
Thanks, Joel
in the table what is the data type? given that what do you need?
When the field in SQL is a text field, it gives a data type mismatch error.
Can you confirm that the "description" column is in fact using the deprecated text data type?
If so, we may need to change your code where you SELECT it. I suspect you are using SELECT *, right?
In any case:
1, Post your code including the SELECT statement.
2. Tell us what data provider you are using. If you don't know what that means just post the connection string (obviously obfuscating any sensitive info)
3. Post the schema to your table (CREATE TABLE statement).
Can you confirm that the "description" column is in fact using the deprecated text data type?
If so, we may need to change your code where you SELECT it. I suspect you are using SELECT *, right?
In any case:
1, Post your code including the SELECT statement.
2. Tell us what data provider you are using. If you don't know what that means just post the connection string (obviously obfuscating any sensitive info)
3. Post the schema to your table (CREATE TABLE statement).
ASKER
Certainly. Please see the attached code. The SQL field called "description" is a text data type.
select Case Name
case "PartName"
Dim DesciptionSQL
Set ins = Item.GetInspector
Set pgs = ins.ModifiedFormPages
Set pg = pgs("Inventory")
Set ctls = pg.Controls
'--------------
Set oADOConn = Nothing
Set oADOConn = CreateObject("ADODB.Connection")
If TypeName(oADOConn) = "Nothing" Then
'MsgBox "Error"
'Exit Function
End if
sConnectionString = "Driver={SQL Server}" & _
";Server=SQL02" & _
";Database=TGPInventory" & _
";User ID=inventoryadmin;Password=Wirelite!NT" & _
";"
err.Clear
'oADOConn.Mode = adModeWrite
oADOConn.ConnectionString = sConnectionString
oADOConn.Open
If err.Number <> 0 Then
'MsgBox "(Error: " & err.Number & "/" & err.Description & ")"
'Exit Function
End If
PartNameParse = split(Item.UserProperties("PartName"), " - ")
sSQL = "SELECT * FROM framing_components WHERE compID = '" & PartNameParse(0) & "'"
Set rst = Nothing
Set rst = CreateObject("ADODB.Recordset")
rst.Open sSQL, oADOConn, adOpenForwardOnly 'for very fast reads
msgbox "asdf" & rst.Fields("description") & "asdf"
ctls("lblPartDesc").Caption = cstr(rst.Fields("description"))
'ctls("lblPartDesc").Caption = "Part Desc: " & rst.Fields("description")
ctls("lblQTY").Caption = "Part Desc: " & FormatCurrency(rst.Fields("l_cost"))
rst.Close
Set rst = Nothing
Set sSQL = Nothing
oADOConn.Close
Set oADOConn = Nothing
end select
Exactly, as I guessed, you are using SELECT *. This is never a good idea and as you have discovered is fatal with BLOBs.
Solution: Change SELECT * to SELECT Col1, Col2, Col3 and place all your BLOBs at the end. So in your case instead of this:
sSQL = "SELECT * FROM framing_components WHERE compID = '" & PartNameParse(0) & "'"
Do this:
sSQL = "SELECT l_cost, description FROM framing_components WHERE compID = '" & PartNameParse(0) & "'"
Solution: Change SELECT * to SELECT Col1, Col2, Col3 and place all your BLOBs at the end. So in your case instead of this:
sSQL = "SELECT * FROM framing_components WHERE compID = '" & PartNameParse(0) & "'"
Do this:
sSQL = "SELECT l_cost, description FROM framing_components WHERE compID = '" & PartNameParse(0) & "'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Solved myself. Thanks to others for help.