recheck n enter to database

[smile4evr]

hi... this is a small doubt in VB6... i am making a request form... in which one of the fields are reasons... now i want it such if my user send in a new reason then it should get written into my database and generate it as a part of the drop-down menu the next time some1 tries to access it!...i know hw to generate it but i want to know how to check if the reason entered by the user already exists in the database or not...
please try n do help...
thankx a lot!...

[AUGXIS]

hi. just search their entry using a query and if is not found then just add it as a new entry.

[QVeen72]

Hi,

Populate the ComboBox using This SQL Query:

Select Distinct Reasons From MyTable

And allow the User to Select one of them.. If a User wants to enter new Reason, In Keypress Event , Trap the Escape Key, and allow him to enter a New Reaon, Anyway, whenever, you save that Response, it will be saved in Database, and then Re-Load the Combobox with same Query..

REgards
Veena

[smile4evr]

thnx a lot for ur responses!... plz clarigy if

Hi,

Populate the ComboBox using This SQL Query:

Select Distinct Reasons From MyTable

is Distinct a function??

[debasisdas]

if you want to restrict duplicate entry into databse table then
1.make the field a primary key
2.use COUN() to findout if the entry already existins in database before inserting into database. Allow to insert only if count returns 0

if you want to populate the control with unique values then use distinct as suggested by Qveen72

[jireh]

Well, Distinct is used if you want to not include the same records... best example is like when you have fieldname named firstname and has a 5 persons that has named like "jireh". if you query it without using disntinct it will return 5 records but ifyou'll use DISTINCT it ill return one record.

but before using the DISTINCT have read this...

http://www.databasejournal.com/features/postgresql/article.php/3437821/SELECT-DISTINCT-A-SQL-Case-Study.htm

[smile4evr]

thnkx a lot for the responses...

now i can show only distinct reasons from the table are getting shown in my front end... but i want to avoid duplication in the backend also... so i hv to give a count()... hw do i do that??
ive included one more field in my database as reason_id...

Dim rst4 As New adodb.Recordset
Dim reasons As String
reasons = "SELECT DISTINCT * FROM Request_Reasons"
CN.Execute reasons
rst4.Open reasons, CN
reason2.Clear
rst4.MoveFirst
Do While Not rst4.EOF
reason2.AddItem rst4!RORequest
rst4.MoveNext
Loop
reason2.ListIndex = 0

Dim rst24 As New Recordset
Dim sql24 As String
sql24 = "Insert into Request_Reasons(RORequest) VALUES('" & reason2.Text & "')"
CN.Execute sql24

[smile4evr]

thnkx a lot for the responses...

now i can show only distinct reasons from the table are getting shown in my front end... but i want to avoid duplication in the backend also... so i hv to give a count()... hw do i do that??
ive included one more field in my database as reason_id...

Dim rst4 As New adodb.Recordset
Dim reasons As String
reasons = "SELECT DISTINCT * FROM Request_Reasons"
CN.Execute reasons
rst4.Open reasons, CN
reason2.Clear
rst4.MoveFirst
Do While Not rst4.EOF
reason2.AddItem rst4!RORequest
rst4.MoveNext
Loop
reason2.ListIndex = 0

Dim rst24 As New Recordset
Dim sql24 As String
sql24 = "Insert into Request_Reasons(RORequest) VALUES('" & reason2.Text & "')"
CN.Execute sql24

please do help and thankx a lot!...

[QVeen72]

Hi,

Before Inserting Record in Request_Reasons, First Check if that reason is present in Database, if not Found then Insert..
Make the Reason_Id as Auto Generated Number, Whenever, you Insert a new reason, id is generated automatically..

Dim rst24 As New Recordset
Dim sql24 As String
sql24 = "Select * From Request_Reasons  Where UCase(RORequest)='"  Ucase(reason2.Text) & "'"
rst24.Open sql24,CN
If rst24.EOF Then 'Rec not found 
    sql24 = "Insert into Request_Reasons(RORequest) VALUES('" & UCase(reason2.Text) & "')"
    CN.Execute sql24
End If
rst24.Close

Regards
Veena