SQL Update

[breeb]

I am trying to update the values in an ASP page read from a database. The values are stored in a table with the columns labeled from a-c (d is a hidden column containing the promary key) and the rows are from 1 to countsys which is a variable storing the number of systems. The error that I get is:

"Data type mismatch in criteria expression"

The code for populating the table from the database is as follows:

dim count
count = 1
While Not RS1.EOF%>
<tr>
<td width=&quot;30%&quot; height=&quot;1&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><input type=&quot;text&quot; name=&quot;<%response.write &quot;a&quot;&count%>&quot; size=&quot;25%&quot; value = &quot;<%=rs1 (&quot;System&quot%>&quot; onFocus=&quot;select(this)&quot;></font></td>
<td width=&quot;45%&quot; height=&quot;1&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><input type=&quot;text&quot; name=&quot;<%response.write &quot;b&quot;&count%>&quot; size=&quot;25%&quot; value = &quot;<%=rs1 (&quot;Description&quot%>&quot; onFocus=&quot;select(this)&quot;></font></td>
<td width=&quot;25%&quot; height=&quot;1&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><input type=&quot;text&quot; name=&quot;<%response.write &quot;c&quot;&count%>&quot; size=&quot;25%&quot; value = &quot;<%=rs1 (&quot;Responsible&quot%>&quot; onFocus=&quot;select(this)&quot;></font></td>
<input type=&quot;hidden&quot; name=&quot;<%response.write &quot;d&quot;&count%>&quot; size=&quot;25%&quot; value = &quot;<%=rs1 (&quot;SNo&quot%>&quot;>
</tr>
<%count = count+1

The update query is as follows:

'update row by row, columns a,b,c
for i = 1 to countsys

'update systems table
tSQL = &quot;UPDATE [Systems] SET&quot;

tSQL = tSQL & &quot; Systems.[System] = '&quot;&replace(request.querystring(&quot;a&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;', &quot;

tSQL = tSQL & &quot; Systems.[Description] = '&quot;&replace(request.querystring(&quot;b&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;', &quot;

tSQL = tSQL & &quot; Systems.[Responsible] = '&quot;&replace(request.querystring(&quot;c&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;'&quot;

tSQL = tSQL & &quot; WHERE (Systems.[SNo]) = &quot;&request.querystring(&quot;d&quot;&i)&&quot; AND (Systems.[System])=&quot; &pareaup

DB.execute (tSQL)
next

SNo is the primary key and is an integer value and pareaup is also a integer value.

Any help would be much appreciated!!

Bree

 

[cbokowski]

Have you tried to response.write your sql statement and run it in query analyzer or like ?

 

[breeb]

Yip, when I response.write, this is what I get:

UPDATE [Systems] SET Systems.[System] = 'CICS', Systems.[Description] = 'crap', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = '5' AND (Systems.[System])=6UPDATE [Systems] SET Systems.[System] = 'MQ', Systems.[Description] = 'fhfhfghfgh', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = '6' AND (Systems.[System])=6UPDATE [Systems] SET Systems.[System] = 'Merva', Systems.[Description] = '', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = '7' AND (Systems.[System])=6

Changing the '&quot;&request.querystring(&quot;d&quot;&i)&&quot;' in the WHERE part to &quot;&request.querystring(&quot;d&quot;&i)&&quot; gives this output:

UPDATE [Systems] SET Systems.[System] = 'CICS', Systems.[Description] = 'crap', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 5 AND (Systems.[System])=6UPDATE [Systems] SET Systems.[System] = 'MQ', Systems.[Description] = 'fhfhfghfgh', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 6 AND (Systems.[System])=6UPDATE [Systems] SET Systems.[System] = 'Merva', Systems.[Description] = '', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 7 AND (Systems.[System])=6

Both ways I get the same error. Any ideas?

Bree

 

[cbokowski]

dont you need semi colons in between sql statements.

 

[mwolf00]

The problem is definately here...

=6UPDATE

cbokowski nailed it...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[breeb]

I don't think that the semi colons are the problem, the same error is given with them in.

UPDATE [Systems] SET Systems.[System] = 'CICS', Systems.[Description] = '', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 5 AND (Systems.[System])=6; UPDATE [Systems] SET Systems.[System] = 'MQ', Systems.[Description] = 'fhfhfghfgh', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 6 AND (Systems.[System])=6; UPDATE [Systems] SET Systems.[System] = 'Merva', Systems.[Description] = '', Systems.[Responsible] = '' WHERE (Systems.[SNo]) = 7 AND (Systems.[System])=6;

Any other ideas?

 

[breeb]

Got it figured out. The following code works for the update query:

'update row by row, columns a,b,c

Dim sysno

for i = 1 to countsys

'update systems table
tSQL = &quot;UPDATE [Systems] SET&quot;

tSQL = tSQL & &quot; Systems.[System] = '&quot;&replace(request.querystring(&quot;a&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;', &quot;
tSQL = tSQL & &quot; Systems.[Description] = '&quot;&replace(request.querystring(&quot;b&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;', &quot;
tSQL = tSQL & &quot; Systems.[Responsible] = '&quot;&replace(request.querystring(&quot;c&quot;&i),&quot;'&quot;,&quot;''&quot&&quot;' &quot;

sysno = request.querystring(&quot;d&quot;&i)
tSQL = tSQL & &quot; WHERE (Systems.[SNo]) = &quot;&sysno
tSQL = tSQL & &quot;; &quot;

DB.execute (tSQL)
next

Only needed one statement in WHERE clause and declared it as a variable first.

Bree