I have a rather robust HTA that connects to my Access database tables for the control of 3rd Party Software for our UNIX servers. I would like to use SQL to add new items to the Applications table. (I use SQL for all of the other functions that I have enabled, thus far.) I know that I can use something like:
objConn.AddNew
..
..
..
objConn.Update
However, I would prefer to use SQL. Here is the code that I have developed:
< script language="vbscript" >
' Connect to the database (3rd Party Software.mdb)
Const adOpenStatic = 3
Const adLockOptimistic = 3
workDir = "C:temp3rd Party Software"
fileName1 = workDir & "3rd Party Software 2.mdb"
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")
objConnection.Open "Provider= Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:temp3rd Party Software3rd Party Software 2.mdb"
Sub AddItems
strProduct = "'" & document.all.product.value & "'"
strVendor = "'" & document.all.Vendor.value & "'"
strVersion = "'" & document.all.Version.value & "'"
strLicReq = "'" & document.all.LicReq.value & "'"
strLicType = "'" & document.all.LicType.value & "'"
strPurDate = "#" & document.all.PurDate.value & "#"
strExpDate = "#" & document.all.ExpDate.value & "#"
h33t - Visual Studio 2008 Express Edition All-in-One DVD [h33t ::
Build applications using LINQ (Language Integrated Query) which adds data querying capabilities for SQL Server, XML, and objects to Visual Basic
http://www.h33t.com/details.php?id=1be69b6fc4964d2a145e3d5cd7f63aeaf18bb6b7HOME |
Human Tissue Authority::
File Format: Microsoft Word - View as HTMLThe Human Tissue Authority (HTA) is an independent Executive S/he will develop and run reports using Microsoft Access and SQL to analyse and interpret
http://www.hta.gov.uk/_db/_documents/2007_09_19_HTA_Application_Pack_Information_Analyst.docHOME |
strLicCount = document.all.LicCount.value
strException = document.all.Exception.value
strNotes = "'" & document.all.Notes.value & "'"
Installing ObjectVideo OnBoard With TMS320DM6437 EVM::
File Format: PDF/Adobe Acrobat - View as HTMLSetup_ThirdParty.hta. The ObjectVideo Software Setup – Prerequisite Software . applications using TI components. To minimize the risks associated with
http://focus.ti.com/lit/an/spraat0/spraat0.pdf?source=googlebaseHOME |
sql = ""
sql = sql & "INSERT "
ssql = sql & "INTO Applications "
sql = sql & "( Product, Vendor, Version, Lic_Required, Lic_Type,
Purchase_Date, "
sql = sql & "Exp_Date, Lic_Count, Exception, Notes ) "
sql = sql & "VALUES ( strProduct, strVendor, strVersion, strLicReq,
strLicType, "
sql = sql & "strPurDate, strExpDate, strLicCount, strException,
strNotes ); "
objRecordSet.Open sql, objConnection, adOpenStatic, adLockOptimistic
End Sub
When I run the HTA and enter the data into the screen, I receive an error message that there is a syntax error in the SQL. I am using the conventions that I have seen in books, but I am at a loss to explain the problem.
Can anyone see where I went astray?
***modified by Admin, Code tags inserted and also extra space around < vb, etc. Please be more careful when posting code to the site****
sql = sql & "VALUES ( strProduct, strVendor, strVersion, strLicReq,
strLicType, "
sql = sql & "strPurDate, strExpDate, strLicCount, strException,
strNotes ); "
is wrong.
try this:
sql = sql & "VALUES ( " & strProduct & ", " & strVendor & ", " & strVersion & ", " & strLicReq & ", " & strLicType
sql = sql & ", " & strPurDate & ", " & strExpDate& ", " & strLicCount & ", " & strException & ", " &
strNotes & " ); "
 Nobody understands...any help please?
 50 points for someone willing to do this!!!!!!!!!!?
|
Using SQL in Access (via an HTA application)