I want to add records to a table an

melusi · Jan 27, 2003

I want to add records to a table and make sure that I do not add it when it's there.I have three fields I campare with and I get the following error message
Syntax error
/scheduletake.asp, line 18, column 4

and I have this section from the beginning of the file
where I think the error is.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">

<%@ Language = VBscript %>
<%

'Grab variables from the querystring.

lilanga = Request.Form("Days&quot

ummango = Request.Form("Comms&quot

lusuku = Request.Form("Tikhatsi&quot

set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "SKPE"
SQLIST="SELECT * FROM schedule"
set cars1=conn.execute(SQLIST)
Do While not cars1.eof
if (<%=cars1("Day&quot

%> = '"& lilanga &"') and (<%=cars1("Community&quot

%> ='"& ummango &"') and (<%=cars1("ScheduledTime&quot

%>='"& lusuku &"') then response.write "<b> A Similar Meeting has been scheduled <\b><\p>"

cars1.movenext loop

else
SQL="INSERT INTO schedule (Day,Officer,TaskToPerform,Department,ScheduledTime,DateofTask,Duration,Community) Values (" &_
"'" & request.form("Days&quot

& "', " &_
"'" & request.form("Officers&quot

& "', " &_
"'" & request.form("Details&quot

& "', " &_
"'" & request.form("Departments&quot

& "', " &_
"'" & request.form("Tikhatsi&quot

& "', " &_
"'" & request.form("Dates&quot

& "', " &_
"'" & request.form("Durations&quot

& "', " &_
"'" & request.form("Comms&quot

& "')"
set cars=conn.execute(SQL)
end if

Tarwn · Jan 28, 2003

I apologize if I seem short in this post anywhere, i typed it out once and accidentally refreshed the page, losing it all, so this is my post, take 2

There are a few problem here with your code.
1) Your If statement block and your Do Loop block are overlapping, this is not allowed as it would cause logic problems, leaving the server in a state where it wouldn't know quite how to proceed
2) You if statement is writing the values of recordset to the screen and then treating the variables as if they are in a string, when they are not. This causes it to get confused because writing the field values returns nothing it can comapre, while single quotes surrounding double quotes doesn't work to define strings in ASP

So here is what you can do, lets take the second problem first. In order to compare the values of the fields to the values of the variables you need to set the if statement up to check if the two in each pair are equvalent to each othr like so:

Code:

if (cars1(&quot;Day&quot;) = lilanga) and (cars1(&quot;Community&quot;) = ummango) and (cars1(&quot;ScheduledTime&quot;)=lusuku) then
   Response.Write &quot;<b> A Similar Meeting has been scheduled <\b><\p>&quot;

Now we are asking the computer to write out that statement if the value of cars1("Day&quot

is equal to the variable lilanga and cars1("community&quot

is equal to ummango, etc

Now we run into a problem. We want to loop through all the fields looking for a match, but we can't use an else statement on the above if statement because if we do it will tell us for every record that doesn't match. There are two ways to handle this.

We could create a boolean variable and set it to true before the loop. Then as we are looping through we could check it's value:

Code:

Dim notRepeated
notRepeated = true
Do While not cars1.eof
   'then we check with our if statement
   If (cars1(&quot;Day&quot;) = lilanga) and (cars1(&quot;Community&quot;) = ummango) and (cars1(&quot;ScheduledTime&quot;)=lusuku) then
      Response.Write &quot;<b> A Similar Meeting has been scheduled <\b><\p>&quot;
      notRepeated = False
   End If
   days1.MoveNext
Loop

'now we can check our boolean variable to see if the record already existed
If notRepeated = True Then
   'do your insert stuff here because it wasn't found in the recordset above
End If

The other option is to have the first SQL statement only return records that match the passed information. This wuill cut down on communication time and the time it takes us to loop through and check all the records. So we would change the first SQL statement to:

Code:

SQLIST=&quot;SELECT * FROM schedule WHERE Days = '&quot; & lilanga & &quot;' AND Community = '&quot; & ummango & &quot;' AND ScheduledTime = '&quot; & lusuku & &quot;'&quot;

Then we can siply check if cars1 has any records in it after we execute the sql query, which means we no long need the loop:

Code:

If cars1.eof Then
   'it only returns the recordset pointing to end of file (eof) when it is empty)
   'so we will do our insert stuff here
End If

And that will fix the top portion. The only other problem I see is that since you are inserting, that statement will return no records, so you don't need to set cars1 = conn.execute(SQL) you can shorten it to:

Code:

conn.execute(SQL)

without the recordset.

I hope all this helped and was clear, please feel free to ask if you would like me to go into greater detauil on anything or if I said anything in a confusing manner.

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

melusi · Jan 28, 2003

Thanks man,I've just checked and found the replies .I guess I'm gonna try this.I have this feeling that it is going to work

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I want to add records to a table an

melusi

IS-IT--Management

Tarwn

Programmer

melusi

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor