insert into table ??

[petertran123]

Dear Whammy,

can you check and see what is wrong with this..i can't insert value to table

<%

Dim strPrimarySSN
Dim strSQL
Dim conn
Dim rsData

'***************************************

strPrimarySSN = Request.Form("PrimarySSN")
strSQL = "Select * from Multi_Submission"

Set conn = GetDBConnection(DATABASE_CONNECT_STRING_HRAL)
Set rsData = conn.Execute( strSQL )
If rsData.eof then
strSQL = "Insert Into Multi_Submission(PrimarySSN)VALUES('" & strPrimarySSN & "')"

else
msg = ("A ssn already exists for this application.")
End if
%>

[rcreyes]

what kind of error messages are you getting, if any? Also, where is your statement executing the actual INSERT statement?

Your SQL statement looks good!

Thanks,
Ray

[petertran123]

Thanks for your responding Rays,

i'm using remote SQL 7.0 server, beside not getting any value into table i did not have any kind of error. I don't know why i can't insert value into table name.

[rcreyes]

The only thing I can is that you are missing a CONN.Execute (strSQL) after the IF THEN statement. Looking at your code (if this is in fact the complete code you are using) you are creating the SQL string statement, but not executing it. It should be:

If rsData.eof then
strSQL = "Insert Into Multi_Submission(PrimarySSN)VALUES('" & strPrimarySSN & "')"
CONN.Execute(strSQL)


Thanks,
Ray

[whammy]

What Ray said. :)

[whammy]

Also, besides not executing your second statement, shouldn't you have a WHERE clause in your select statement as well?:

strSQL = "SELECT * FROM Multi_Submission WHERE PrimarySSN = '" & strPrimarySSN & "'"

If you don't have a WHERE clause like that, then you will never get EOF (end of file) since you haven't told SQL what to look for, and it will ALWAYS insert a record!

:eek:

[dfrancis]

Sounds like you are attempting two functions... 1) looking for the existence of the value "strPrimarySSN" and if it's not found; 2) inserting it. The way I do it is to create a record set, look through it and if it's not there, only then do I attempt an insert. Also, I give myself a msg either way, that way I know what the heck is going on.

You'll likely need to clean this... it's from the top of my relatively flat head...


strSq = "SELECT * FROM Multi_Submission WHERE PrimarySSN = '" & strPrimarySSN & "'"
set rs = conn.Execute (strSq)

if rs.BOF and rs.EOF then
strSQL = "Insert Into Multi_Submission(PrimarySSN)VALUES('" & strPrimarySSN & "')"
conn.Execute strSQL, lngRecsAffected, adCmdText Or adExecuteNoRecords
msg = ("SSn " & strPrimarySSN & " added to the database")
else
msg = ("A ssn already exists for this application.")
end if
rs.close
set rs = nothing

[petertran123]

Ray was right, i'm missing part of
conn.execute strSQL :) Thank you all for yours supports and kindness. I will give each you guy a 15lbs TURKEY. HAPPY THANKSGIVING TO YOU ALL.


Thanks,:thumbsup: