Addnew

[Elika]

Hello ,

Just finished my first database , everything ok , however ;
when I use the addnew statement , and there is allready a record with the same data I get an error. How can I check if a new record allready exists in a database ?

Thanks , Elika

[JonnyCab]

Firstly, I assume that you are adding data to tables by taking values from a text box or similar form object.

Secondly there are different ways of doing it and some depend on if you are using ADO or DAO connections to the database.

You could use the ADO 'Find' method.

ADORecordset.Open
ADORecordset.MoveFirst
ADORecordset.Find ("ADOFieldName = '" & _ Form.TextBox.Text & "'")
If ADORecordset.EOF Then
ADORecordset.AddItem
ADORecordset.ADOField=Form.TextBox.Text
Else
Msgbox "This Item Exists"
Exit sub
End IF

You could also do something similar using the SELECT query and if an empty recordset is returned from query then you can add item.

Lets us know if you need any more help on this.

[kb244]

Taken from MSDN July 2000:

INSERT – SQL Command
Example See Also

Appends a record to the end of a table that contains the specified field values.

Syntax

INSERT INTO dbf_name [(fname1 [, fname2, ...])]
VALUES (eExpression1 [, eExpression2, ...])

-or-

INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR

Arguments

INSERT INTO dbf_name

Specifies the name of the table to which the new record is appended. dbf_name can include a path and can be a name expression.

If the table you specify isn't open, it is opened exclusively in a new work area and the new record is appended to the table. The new work area isn't selected; the current work area remains selected.

If the table you specify is open, INSERT appends the new record to the table. If the table is open in a work area other than the current work area, it isn't selected after the record is appended; the current work area remains selected.

[(fname1 [, fname2 [, ...]])]

Specifies the names of the fields in the new record into which the values are inserted.

VALUES (eExpression1 [, eExpression2 [, ...]])

Specifies the field values inserted into the new record. If you omit the field names, you must specify the field values in the order defined by the table structure. If SET NULL is ON, INSERT – SQL attempts to insert null values into any fields not specified in the VALUES clause.

FROM ARRAY ArrayName

Specifies the array whose data is inserted into the new record. The contents of the elements of the array, starting with the first element, are inserted into the corresponding fields of the record. The contents of the first array element are inserted into the first field of the new record; the contents of the second array element are inserted into the second field, and so on.

Any default values for fields are ignored when you include the FROM ARRAY clause.

FROM MEMVAR

Specifies that the contents of variables are inserted into fields with the same names as the variables. If a variable doesn't exist with the same name as the field, the field is left empty.

Remarks

The new record contains the data listed in the VALUES clause or contained in the specified array or variables. The record pointer is positioned on the new record.

you can use it as a parameter in your Recordset.Open command, and you dont have to close it since it's a command and doesnt return a recordset.

[Elika]

Thanks for your fast reply JonnyCab ,

First of all I'm using ADO and yes ,I'm also using Textbox
controls to show the contents of the DB to the user. I've tried usin SQL but I'm having problems with that. When the
new data is allready present the recordset is shown but I can't move to ather records afterwards. When the new data is not found the new data is inserted but again I can' move to other records afterwards (except when I close and reopen the aplication).

I understand what you mean in your reply but I have a second question ; The data has to be checked for 2 textbox
controls because this is a DB with custommers information and there are different custommers with the same surname but different first names ,could this be done with the FIND command ?
The 2 fieldnames are Sname and Fname.

Thank you

[JonnyCab]

I would use the following code...

Dim strSQL As String
Dim rstTemp As ADODB.Recordset

strSQL = "SELECT *" _
& " FROM Table" _
& " WHERE Table.Field = '" & Form.TextBox & "'" _
& " AND Table.Field2 = '" & Form.TextBox2 & "'"

Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

If rstTemp.Recordcount = 0 then
rstTemp.AddItem
rstTemp!Field1 = Form.TextBox
rstTemp!Field2 = Form.TextBox2
rstTemp.Update
end if

rstTemp.Close


.... My DataEnvironment.Connection1 is already open. I think something along these lines would work better than trying to use 'Find'

Let us know how you go.

Cheers

[Elika]

Ok , I see what you mean , but you have to excuse me because there are a few things that I don't understand. On my form I have one ADOdc control called ADODATA so I replaced your Dim rstTemp As ADODB.Recordset with

Dim rstTemp As AdoData.Recordset Is this correct ?

Then I changed The strSQL string because I want to check
field2 & field3 , like this ;
(Newtext 0 and 1 are the textbox controls holding the new information.)

strSQL = "SELECT *" _
& " FROM Table" _
& " WHERE Table.Field2 = '" & Newtext(0).Text & "'" _
& " AND Table.Field1 = '" & Newtext(1).Text & "'"

----------------------------------------------------------

This set command is something that I don't understand , because I don't get where the DataEnvironment1.Connection1.Execute is comming from. Is DataEnvironment1 a second control or what ? And what kind of control is it ?

Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

----------------------------------------------------------

I also changed the rest of the code , like this ;

If rstTemp.Recordcount = 0 then
rstTemp.AddItem
rstTemp!Field1 = Newtext(0).Text
rstTemp!Field2 = Newtext(1).Text
rstTemp.Update
end if

rstTemp.Close

THe rstTemp.Close command troubles me , will the user still be able to scroll trough the records using the MovePrevious
and MoveNext commands ??

So , anyway , I've tried the code but I got this error ;

Compile error
User-defined type not defined.

When I click OK the rstTemp As AdoData.Recordset is highlighted.

Perhaps I'm looking like a fool to you but this is my first
serious attempt to make a good database editor.

Thank anyway , Elika.

[JonnyCab]

Don't worry about asking questions.... I probably don't know much more than you and I started in June playing with VB6.


I don't know exactly how you'd use your ADOdata control in this situation. A DataEnvironment has a similar function to the data control. You set up a 'DataEnvironment' and a
'Connection' to a particular database then you very easily have assess to all the tables in that database. ie able to view, edit, add, and delete records and fields.

If you want to play around with this...make sure you have
MS ActiveX Data Objects 2 & MS ActiveX Data Objects Recordset 2.5 in project 'References'

Also include 'Data Environment' from menu Project/Components/Designers.

If you update database table using DataEnvironment method then you would probably have to ADOdata.update or refresh so that the changes to the table could be seen using your data control.

Sorry couldn't be oy more help.

[Elika]

OK , I'mgoing to check this out right now and I'll let you know. In your first answer you wrote that I could use ;

ADORecordset.Find ("ADOFieldName = '" & _ Form.TextBox.Text & "'")
to find a certain record , is it possible to use 2 Adofieldname's , because this worked well but I need to check two fields ?

Thanks , Elika

[Elika]

Hello , correction on my last reply.

I've been searching more information and it seems that the Find statement can only be used with one criteria. So I
returned to your second solution containing the Data Environment , I've added the environment and now I'm busy
testing and trying. I will let you know , but hey , thank you man , your help was (and I hope will be) very useful.

Thanks , Elika

[JonnyCab]

No problems...the DataEnvironment Designer addin takes a bit to learn but definately makes things easy once you have one set up and you know what your doing.

Good Luck

[Elika]

Hurrah , hurrah , and also Oops...

Hurrah because I've got the DataEnvironment working , I can
add a new record after checking if the record allready exists. I've changed your code a little.

strSQL = "SELECT *" _
& " FROM Table" _
& " WHERE Table.Field2 = '" & Newtext(0).Text & "'" _
& " AND Table.Field1 = '" & Newtext(1).Text & "'"
Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

becomes

strSQL = "SELECT * FROM ClientField " & _
"WHERE Firstname = '" & Newtext(0).Text & _
"' AND Lastname ='" & Newtext(1).Text & "'"
Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)


This works just fine , but then again Oops...
If rstTemp.Recordcount = 0 then
rstTemp.AddItem
rstTemp!Field1 = Newtext(0).Text
rstTemp!Field2 = Newtext(1).Text
rstTemp.Update
end if

rstTemp.Close

If I use this code you gave me I get an error

Compile error:
Method or datamember not found

And the .AddItem part is highlighted ?? Any idea ??
But I also found another way of adding the user
(Adodata is the ADOdc control I use)
AdoData.Recordset.AddNew
Dtext(1).Text = Newtext(0).Text
Dtext(2).Text = Newtext(1).Text

This works but if I knew why yours did not I would be happpy
and a little bit smarter. OK , I've got it working in two days thanks to you , so I owe you one.

[JonnyCab]

Excellent to hear everything is working.

I'm thinking mayby it could be that....

If rstTemp.Recordcount = 0 then
rstTemp.AddItem
rstTemp!Field1 = Newtext(0).Text
rstTemp!Field2 = Newtext(1).Text
rstTemp.Update
end if

rstTemp.Close

...should be...

If rstTemp.Recordcount = 0 then
rstTemp.AddItem
rstTemp!FirstName = Newtext(0).Text
rstTemp!LastName = Newtext(1).Text
rstTemp.Update
end if

rstTemp.Close

Thats the only thing I can think of off the top of my head.

Hey... now that you owe me one I'll be expecting you to answer some of my problems when I post them )

Cheers, Jonny

[Edited by JonnyCab on 10-05-2000 at 09:49 PM]

[Elika]

No Problemo Amigo , as they say in Mexico ,

If tested it and the final code looks like this
(Adodata is my ADOdc control)

If rstTemp.RecordCount = 0 Then
AdoData.Recordset.AddNew
AdoData.Recordset.Fields(1) = NewText(0).Text
AdoData.Recordset.Fields(2) = Newtext(1).Text
AdoData.Recordset.Fields(3) = Newtext(2).Text
End If

This works fine...

[Elika]

Hello Jonnycab ( or others) ,

I've got allmost everything working now , still two minor problems.

1)If a user puts a new record in the DB the routine you gave me checkes if this record allready exists , that works.
But if I just want to find a record in the DB I've got problems.
In my form there's a Search button , then the user can fill in a name (Clientname) , then I got a SQL to look for that name in the DB...

AdoData.RecordSource = "SELECT * FROM ClientField " & _
"WHERE Lastname = '" & ClientName & "'"

What happens , if the record is found it is shown to the user , but then the rest of the records can't be seen anymore , if it isn't found all I got is that EOF and BOF are true , but again I can't go to the other records...
When there are more than one records I can see them but not the other records.

How can I solve this problem ??

Elika.

[Elika]

The second problem...

When I am at the first record and I click on MOVEPREVIOUS
an empty record is shown , the same when I click on MOVENEXT when I'm at the last record. Isn't there a solution
to prevent this ?

Elika.

[JonnyCab]

For your second problem...

you need some code behind you you ADO data control...
something like...

If recordset.eof then
recordset.movefirst
end if

If recordset.bof then
recordset.movelast
end if

As for your first problem... pass at this stage.

[Elika]

OK , I'm going to try this , thanks in advance !!

Elika