Now onto UPDATE SQL

[ceaton]

I need to retreive my id key from an Access database and compare it to the current record I'm working on how do I go about doing that, this is what I've come up with so far and this doesn't seem to work. <br>
<br>
docketNum is within my access db and fileNum is the current record I'm working with, I want to find out if this records exists this way I know if I need to perform an UPDATE or an INSERT? Am I on the right track with this?<br>
<br>
I also get an error when I run it: <br>
SQLException: General Error<br>
<br>
<br>
String find = &quot;SELECT docketNum FROM caseInfo WHERE docketNum = '&quot; +fileNum+&quot;'&quot;;<br>
try<br>
{ <br>
con = DriverManager.getConnection (url,&quot;&quot;,&quot;&quot;<br>
stmt = con.createStatement(); <br>
ResultSet ts = stmt.executeQuery(find);<br>
// this prints the correct text<br>
System.out.println(ts); <br>
<br>
// these two println do not print out <br>
while ( ts.next()){<br>
v = ts.getString(1);<br>
System.out.println(v);<br>
System.out.println(find);<br>
}<br>
System.out.println(v);<br>
stmt.close();<br>
con.close();<br>
<br>
<br>
}catch (SQLException ex){<br>
System.err.println(&quot;SQLException: &quot;+ ex.getMessage());<br>
} <br>
<br>
<br>
if ( v == fileNum )<br>
<br>
String query2 =(&quot;UPDATE caseInfo,<br>
+ &quot;SET caseName = getAsSQLString(labelcaseName),&quot;<br>
+ &quot;evalDate = getAsSQLString(labelevalDate),&quot;<br>
+ &quot;docketNum = getAsSQLString(labelfileNum)&quot; <br>
+ &quot;WHERE docketNum = fileNum&quot; <br>
<br>
else <br>
<br>
<br>
String query = &quot;INSERT INTO caseInfo(caseName, evalDate, docketNum, &quot;<br>
<br>
<br>
// rest of query, etc.<br>
<p> Courtney<br><a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a><br><a href= > </a><br>

 

[RLM]

Hi Courtney<br>
<br>
I'd first suggest that you cstr(fileNum) and replace the + in your sql with &... the problem may be with the concatenation of a string ("select...") to a numeric (fileNum). You'd also not want to enclose the fileNum within quotes unless it's defined as a text string in your table as opposed to a numeric. <br>
<br>
So you'd <br>
find = "select..." & cstr(fileNum) <br>
as your sql.<br>
<br>
Another method to consider when you don't know if you'd need an update or insert is to put a unique index on the table, and try the insert first. If the key exists, an error is flagged. Trap the error, and you'll have the opportunity to do an update. You can turn the order around (perform update first, if record doesn't exist, that error will come up) depending on how many new records you'll have as opposed to existing ones. The goal should be to reduce the number of table scans, thus reducing query time. A select on the entire table may become very time consuming...<br>
<br>
Hope this helps....<br>
Russell L.