vb6 code to check if record doesnt exist,then insert new record

[ponnu]

vb6 code to check if record doesnt exist,then insert new record.server is ms access 2010.So when i used the Dlookup function

If IsNull(DLookup("[empid]", "[employee]", "[empid]= & txtemplid.text")) Then

I got error like "function or subfunction not defined .is it due to issues with referencs .how can i fix this error .?

[BitBlt]

Try this:

If IsNull(DLookup("[empid]", "[employee]", "[empid]= " & txtemplid.text)) Then

This assumes, of course, that txtemplid.text is numeric, and that [empid] is numeric.

[Ajinkya Jagtap]

I think,you should use following code if you are using Recordset:

dim rs as new adodb.recordset
rs.open "select * from employee where emp_id='"& txtemplid.text &"'",con,adopendynamic
if rs.EOF=true then
'
'
'insert code for Record insertion
'
'
end if

[debasisdas]

tyr this

rs.open "select count(*) as cnt from employee where emp_id='"& txtemplid.text &"'",con,adopendynamic

if count is zero then insert.

[ponnu]

Private Sub command1_click()

rs.Open ("select * from employee where emp_id=' & txtemplid.Text & '")

If (rs.EOF = True) Then

'If IsNull(DLookup("[empid]", "[employee]", "[empid]= " & txtemplid.Text)) Then

Set ps = cn.Execute("INSERT INTO employee(empname,empid,ssn) VALUES('" & txtEmpname.Text & "'," & txtemplid.Text & "," & txtessn.Text & ") ")

Else
MsgBox ("record already exists")
End If
End Sub

I am getting run time error 3709.

[BitBlt]

From the ADO Help file:

adErrInvalidConnection 3709
0x800A0E7D
The application requested an operation on an object with a reference to a closed or invalid Connection object.

Where is the object "cn" defined and instantiated? Was "cn" ever opened successfully? Was "cn" closed somewhere else? What is the variable "ps"?

Oh, and you may need single quotes around your value for txtessn.text.

[ponnu]

yeah now the code works fine but after entering values int textbox i have to recompile it again to enter the new values.how can this be fixed.is it because of the variables not getting cleared.Ihave closed the recordset and connection string

[debasisdas]

You need to write some code to clear the content of the input controls.

[ponnu]

ok
thanks

[ponnu]

thanks everyone.