Unterminated string constant

cnottingham · February 16th, 2004, 10:22 PM

I have some code which I am attempting to search through a data base and display the records that meet the search criteria. Again, I am not a programmer, and have placed the Wrox ASP book on order. But in the mean time, could someone please help. Here is the Error, and then the code:

Thanks in advance, Cliff

Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/dnstream/dbcontrol.asp, line 86, column 108
strSQL = "SELECT ColunmNameIntable(1), ColunmNameIntable(3), ColunmNameIntable(2), ColunmNameIntable(5) FROM
-----------------------------------------------------------------------------------------------------------^
Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)
DIM strSQL
strSQL = "SELECT ColunmNameIntable(1), ColunmNameIntable(3), ColunmNameIntable(2), ColunmNameIntable(5) FROM
NameOfTableInDB WHERE (ColunmNameIntable(1) >= 'DataName(1)' AND ColunmNameIntable(4) = 'DataName(4)'"
Set RS = Server.CreateObject("ADODB.Recordset")
DB.Open strSQL

If RS.EOF And RS.BOF Then
Response.write "There are O records."
else
RS.MoveFirst
Response.write ("<form action=dbdelete.asp method=post>")
While Not RS.EOF

Imar · February 17th, 2004, 05:00 AM

What are those ColunmNameIntable(3) thingies? Are you trying to retrieve dynamic columnnames from an array?

If so, you can's use the array name inside the string directly; you'll need to concatenate the SQL statement and the column names:

strSQL = "SELECT " & ColunmNameIntable(1) & ", " & ColunmNameIntable(2)

If that's not the case, just drop the array name, and use real column names instead:

strSQL = "SELECT ColunmNameIntable1, ColunmNameIntable2 FROM YourTable WHERE ColunmNameIntable1 = 'SomeValue'"

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

cnottingham · February 17th, 2004, 03:56 PM

You are right, in the attempt to retrieve the data from an array, and I took your advice and just used the real column name instead, but I still get the same error, in fact at the same place.

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/test/dbcontrol.asp, line 90, column 69
strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM
--------------------------------------------------------------------^

This is the entire code. I may need to start from scratch, when I learn what the hell I am doing, but if you can provide any guidance, I would appreciate it. If you don't have the time, then I understand.

Again, thanks.

%

DIM ColunmNameIntable(5)

ColunmNameIntable(0)="ID"
ColunmNameIntable(1)="EDate"
ColunmNameIntable(2)="HoursWorked"
ColunmNameIntable(3)="OfficeList"
ColunmNameIntable(4)="EmpInitials"
ColunmNameIntable(5)="WorkDescription"

' When displaying data we will use this values
DIM DataName(5)

DataName(0)="ID"
DataName(1)="Date"
DataName(2)="Hours Worked"
DataName(3)="Down Stream Office"
DataName(4)="Employee"
DataName(5)="Work Description"
%>
<body BGCOLOR="#CCFFCC">
<h3 align="center"><b>Down Stream Work Accomplished</b></h3>
<form action=dbaddrecord.asp method=post>
<input type=hidden name=action value=add><br>
<% =DataName(1) %>:
<input type="EDate" name="<% =ColunmNameIntable(1) %>" size="20"><br>
<% =DataName(2) %>:
<input type=text name="<% =ColunmNameIntable(2) %>" size="20"><br>
<% =DataName(3) %>: <SELECT type=text name="<% =ColunmNameIntable(3) %>">
<OPTION>MakahNFH
<OPTION>NisqNWR
<OPTION>NOAA
<OPTION>OFHC
<OPTION>QuilNFH
<OPTION>QuinaultNFH
<OPTION>Redmond
<OPTION>WMNWR
</SELECT><br>
<% =DataName(4) %>: <SELECT type=text name="<% =ColunmNameIntable(4) %>">
<OPTION>CL
<OPTION>CN
<OPTION>JO
<OPTION>ND
<OPTION>SD
<OPTION>ZM
<OPTION>ZT
</SELECT><br>
<% =DataName(5) %>: <TEXTAREA input type=text name="<% =ColunmNameIntable(5) %>" Rows="4" COLS="75" Wrap=soft></TEXTAREA><br>

<input type=submit value=submit></form>

<h4 align="center"><b>Search for records</b></h4>
<%
DIM SearchDate
DIM SearchUsr
%>
<% =DataName(1) %>:
<input type="EDate" name="<% =SearchDate %>" size="20"><br>
<% =DataName(4) %>: <SELECT type=text name="<% =SearchUsr %>">
<OPTION>CL
<OPTION>CN
<OPTION>JO
<OPTION>ND
<OPTION>SD
<OPTION>ZM
<OPTION>ZT
</SELECT><br>
<%
DIM DB
DIM RS
DIM data1st
if request.querystring="1" then

DIM PathToDatabase ' The location of our database within our server
PathToDatabase="c:\Inetpub\wwwroot\test\downstream work.mdb"

DIM NameOfTableInDB ' The name of the table in our Access database. In our case this name is "Table1"
NameOfTableInDB="tblmain"

' The name of each colunm in the table is contained in the array "ColunmNameIntable".
' The sample database provided in this tutorial has up to 20 colunms
' with names "Data1" to "Data20" (and the first colunm, which is named "Id"
' As in this script we will not use all of them, only the ones we will use are included in this array

Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)
DIM strSQL
strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM
tblmain WHERE &ColunmNameIntable(1) >= &SearchDate AND &ColunmNameIntable(4) = &SearchUsr"
Set RS = Server.CreateObject("ADODB.Recordset")
DB.Open strSQL

If RS.EOF And RS.BOF Then
  Response.write "There are O records."
else
    RS.MoveFirst
  Response.write ("<form action=dbdelete.asp method=post>")
    While Not RS.EOF

        Response.write (DataName(1) & ": " & RS.Fields (ColunmNameIntable(1)) & "<BR>")

        Response.write (DataName(2) & ": " & RS.Fields (ColunmNameIntable(2)) & "<BR>")

        Response.write (DataName(3) & ": " & RS.Fields (ColunmNameIntable(3)) & "<BR>")

        Response.write (DataName(4) & ": " & RS.Fields (ColunmNameIntable(4)) & "<BR>")

        Response.write (DataName(5) & ": " & RS.Fields (ColunmNameIntable(5)) & "<BR>")

        Response.write ("<input type=checkbox name=" & RS.Fields (ColunmNameIntable(0)) & " value=" & RS.Fields (ColunmNameIntable(0)) & ">Delete this record</a><BR>")
        Response.write ("")
        RS.MoveNext

    Wend
  Response.write ("<input type=submit value=Submit></form>")
    End If

else
Response.write("<a href=dbcontrol.asp?1>Submit</a>")
end if
%>

Imar · February 17th, 2004, 04:40 PM

Well, you change part of it, but not all. The problem was with the column names in the SELECT statement. You fixed that, but the same problem is present in the WHERE clause:

strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM tblmain WHERE & ColunmNameIntable(1) >= & SearchDate AND & ColunmNameIntable(4) = &SearchUsr"

Again, you need to separate variables from hard coded strings. Maybe a short example will help:

Code:

Dim TestString
Dim TestVariable
TestVariable = 10

TestString = "This will print the number & TestVariable"

I think that you think that TestString now contains:

This will print the number 10

But in fact, it contains:

This will print the number & TestVariable

To concatenate the value of the variable to your string, close the string, and then use the ampersand to add the value:

Code:

TestString = "This will print the number " & TestVariable"

The same applies to your SQL statement:

Code:

strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM tblmain WHERE " & ColunmNameIntable(1)& " >= " & SearchDate & " AND " & ColunmNameIntable(4) & " = " & SearchUsr

Besides fixing this, I guess you'll also need to enclose the values for the columns in the WHERE clause in ' symbols:

Code:

strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM tblmain WHERE " & ColunmNameIntable(1)& " >= '" & SearchDate & "' AND " & ColunmNameIntable(4) & " = '" & SearchUsr & "'"

It looks like you're building some general table editor, right? If I were you, I'd drop the dynamic stuff first. Learn how to access a database with fixed, hardcoded queries first. That alone is difficult enough at first. Once you understand how it works, modify the hardcoded stuff so it becomes dynamic. This way, you learning curve will be less steep.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

pgtips · February 18th, 2004, 08:28 AM

Imar, I agree with your comments about separating variables from hard-coded strings. But also it looks like the "unterminated string constant" error occurs becuase the SQL string is built over 2 lines without a line-continuation character.

This code should be all on one line (in addition to sorting out the issues that Imar has raised)
strSQL = "SELECT EDate, OfficeList, HoursWorked, WorkDescription FROM
tblmain WHERE &ColunmNameIntable(1) >= &SearchDate AND &ColunmNameIntable(4) = &SearchUsr"

rgds
Phil

Imar · February 18th, 2004, 08:43 AM

Hi Eagle Eye,

Yes, you're completely right. I was focussing on the syntax to build the WHERE clause, and I forgot to look at the real problem ;)

Looking again at the same code, the problem I fixed should have been caught by the database, not by the ASP page. The line break is indeed the cause of the error...

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

cnottingham · February 18th, 2004, 11:51 AM

You all have been very helpful. I was able to get by the error I was getting, and then came across a second problem. Not sure if it is major or not, but could not locate any information when I did a search. I found this code on one of the ASP sites as free code, and attempted to modify it to meet my requirements. But, I believe I bit off more then I can chew at this time. I received the WROX Beginning ASP 3.0 yesterday, and will start the learning process from the beginning, so that I better understand all of the idiosyncrasies of ASP before I tackle this again. Also, I believe I will start from scratch so that I understand what the code is doing from start to finish. Again, you have been real helpful, and I greatly appreciate the time you provided. I am sure you will here from me again, as I get into this again, and find myself beating my head against a wall.

Thanks, Cliff

DaveGerard · February 20th, 2004, 12:18 PM

How about posting your CURRENT code?

zfpt1ster · October 28th, 2011, 01:43 PM

Error Message:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/sample/Sample_return.asp, line 19, column 129
Set rsAdd = oConn.Execute("INSERT INTO tblSample (Last_Name, First_Name, Street_Address, City, State, Zip_Code, Contact_PhoneNo),
--------------------------------------------------------------------------------------------------------------------------------^

I am trying to get my text fields from Sample.asp to fill in my Sample.mdb on the tblSample inside it & then return it to Sample_return.asp listing all entries that have been posted so far. I am using Sample_odbc.asp to access my MS Access Database & all of this is through IIS connection on my local machine.

This is my code so far:

Sample.asp
--------------------------------------------------------------------
<body bgcolor="#EEEEEE">
<center>
<fieldset style="width:50%;">
<form name="myForm" action="Sample_return.asp" method="post" onsubmit="return validateForm()" >
<table cellpadding="3" cellspacing="3" bordercolor="#000066">
<tr>
<td>
Your Last Name:
</td>
<td>
<input type="text" name="Last_Name" size="20"/>
</td>
</tr>
<tr>
<td>
Your First Name:
</td>
<td>
<input type="text" name="First_Name" size="20"/>
</td>
</tr>
<tr>
<td>
Your Street Address:
</td>
<td>
<input type="text" name="Street_Address" size="50" />
</td>
</tr>
<tr>
<td>
Your City:
</td>
<td>
<input type="text" name="City" size="20" />
</td>
</tr>
<tr>
<td>
Your State:
</td>
<td>
<input type="text" name="State" size="5" />
</td>
</tr>
<tr>
<td>
Your Zip Code:
</td>
<td>
<input type="text" name="Zip_Code" size="5" />
</td>
</tr>
<tr>
<td>
Your Contact Phone Number:
</td>
<td>
<input type="text" name="Contact_PhoneNo" size="20" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="Submit" />
<input type="reset" value="Reset" />
</td>
</tr>
</table>
</form>
</fieldset>
</center>

</body>
</html>

---------------------------------------------------------------------

Sample_return.asp

---------------------------------------------------------------------



<html>
<head>
<%
'Dimension variables
Dim oConn 'Holds the Database Connection Object
Dim rsAdd 'Holds the recordset for the records in the database
Dim rsEasy 'Holds the SQL query to query the database

strLname = Request.form("Last_Name")
strFname = Request.form("First_Name")
strAddr = Request.form("Street_Address")
strCity = Request.form("City")
strState = Request.form("State")
strZip = Request.form("Zip_Code")
strPhone = Request.form("Contact_PhoneNo")

********************************************
********************************************

Set rsAdd = oConn.Execute("INSERT INTO tblSample (Last_Name, First_Name, Street_Address, City, State, Zip_Code, Contact_PhoneNo)
values ('" & strLName & "', '" & strFName & "', '" & strAddr & "','" & strCity & "',
'" & strState & "','" & strCity & "','" & strZip & "','" & strPhone & "')")

********************************************
********************************************

Set rsEasy = oConn.Execute("SELECT * FROM tblSample")
%>
</head>
<body>
Names in the database:
<br><br>

<%
Do while NOT rsEasy.EOF
Response.Write("Name: " & rsEasy.Fields("Last_Name") & ", " & rsEasy.Fields("First_Name") & ": "
& rsEasy.Fields("Street_Address") & ", " & rsEasy.Fields("City") & ", " & rsEasy.Fields("State")
& ", " & rsEasy.Fields("Zip_Code") & ", " & rsEasy.Fields("Contact_PhoneNo"))
%>
<br><br>
<%
rsEasy.MoveNext
Loop
%>
</body>
</html>

----------------------------------------------------------------------

Sample_odbc.asp

----------------------------------------------------------------------

<%

'Create an ADO connection object
Set oConn = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Sample.mdb")
%>

----------------------------------------------------------------------

The Error that I get will be from the code I have surrounded in stars.
If you have any suggestions on how to rewrite this code to make my Guest list return to the Sample_return.asp page, I am all ears.

Parker