[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Field type vbscript.

Posted on 2014-12-29
6
Medium Priority
?
172 Views
Last Modified: 2015-01-19
Hello,

I run VBScript from Outlook to populate a text field.  I'm grabbing a value from a SQL table:

ctls("lblPartDesc").Caption = "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
0
Comment
Question by:Genius123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 40522886
in the table what is the data type? given that what do you need?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40522996
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).
0
 

Author Comment

by:Genius123
ID: 40523718
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

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40524981
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) & "'"
0
 

Accepted Solution

by:
Genius123 earned 0 total points
ID: 40549581
This did not work, however, when I convert the field to a varchar it works.  Here's the code:

sSQL = "SELECT compID,part_number, CONVERT(varchar(255), description) as descrip FROM framing_components WHERE part_number LIKE '%"& Trim(Item.UserProperties("PartNameSearch").value) & "%' or description LIKE '%"& Trim(Item.UserProperties("PartNameSearch").value) & "%' ORDER BY part_number"
0
 

Author Closing Comment

by:Genius123
ID: 40557333
Solved myself.  Thanks to others for help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question