If Record Exist

[gayano]

HI,
What i trying to do is
when a user try to add new item that already in the table
(example try to add a "name" that already in the "Name_Table")
msgbox pops up and tell the user that the name is already in the table

is there anyway to do this in Visual basic 2005

Thanks (Any help)

[edpudol]

Use a sql statement something like

Select * from table where name='juan dela crus'

if the return value is 1 or more then the name is exist

[gayano]

Thanks

Could you give me a example vb code

Thank you

[xtianixm]

SAMPLE:


SQL = Select * from table where name='juan dela crus'

set rs = conn.execute(SQL)

if not rs.eof then

msgbox "Oyee file already exist ^__^"

end if


..hope that solve your problem ^__^

[debasisdas]

before inserting the record execute this

select count(name_field) from table_name where name_field= 'name';

if it returns 1 or more ,you can return the message and no need to execute the insert statment.

[gayano]

Thank you guys
Thank you veeeeeeeeeeeeeeeeeeeery much

[gayano]

sorry guys i need more info

What to do before writing those codes that you gave me.
cz im not verry good at sql

Im using sql server 2005


do i have open a connection to my database first??
can you give me A to Z example??

[edpudol]

sorry guys i need more info

What to do before writing those codes that you gave me.
cz im not verry good at sql

Im using sql server 2005


do i have open a connection to my database first??
can you give me A to Z example??

Yes you need to open new connection first...

e.g.

Dim dbs As ADODB.Connection
Dim rec As ADODB.Recordset

Set dbs = New ADODB.Connection
Set rec = New ADODB.Recordset

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"
dbs.Open sConnString
rec.Open "SELECT * FROM TABLE WHERE name='juan dela cruz'", dbs

If rec.EOF And rec.BOF Then
MsgBox "not exist"
Else
MsgBox "exist"
End If

[Killer42]

I think it might help a lot if you tell/show us what you've got so far. We don't know how you're storing the data yet.

[gayano]

This is What i done for inserting data?? this works greate but i dont want user to add same data again and again sowhat should i do??

at the time i dont have any diclaration fo sql conn... ,,,, (Dont mind about the Japanese Charactors in the msg box's)

Private Sub InsertData()
        Dim Truckcode As Integer
        Dim TruckNo As String
        Dim TruckMake As String
        Dim TruckRegDate As Date
        Dim TruckOption As String
        Dim TruckDriver As String
        Dim TruckTons As Integer
        Dim TruckSS As Date
        Dim TruckSE As Date
 
 
        Truckcode = CInt(txtTruckCode.Text)
        TruckNo = txtTruckNo.Text
        TruckMake = txtTruckMake.Text
        TruckRegDate = CDate(txtmTruckRegDate.Text)
        TruckOption = txtTruckOption.Text
        TruckDriver = txtTruckDriver.Text
        TruckTons = CInt(txtTruckTons.Text)
        TruckSS = CDate(txtmTruckSS.Text)
        TruckSE = CDate(txtmTruckSE.Text)
 
        Try
            Me.TrucksTableAdapter.Insert(Truckcode, TruckNo, TruckMake, TruckRegDate, TruckOption, TruckDriver, TruckTons, TruckSS, TruckSE)
            Me.TrucksTableAdapter.Update(Me.UnkouTrucks.Trucks)
        Catch ex As Exception
            MsgBox("入力間違ってます 管理人にお問い合わせしてください....っていうかガヤンに聞いて" & ex.Message)
 
        End Try
        MsgBox("トラック１台追加されました", MsgBoxStyle.Information, "確認")
 
 
 
        ''**************** Update and Fill Truck Form **********************''
 
        Try
 
            Trucks.TrucksTableAdapter.Update(UnkouTrucks.Trucks)
            Trucks.TrucksTableAdapter.Fill(UnkouTrucks.Trucks)
        Catch ex As Exception
            MsgBox("入力間違ってます 管理人にお問い合わせしてください....っていうかガヤンに聞いて" & ex.Message)
        End Try
 
 
        ''**************** Clear The form for the next entry *****************''
        ClearForm()
 
End Sub

Thank you

[Killer42]

A lot of this is VB.Net-specific, so I can't help with it (I use "old faithful", VB6). But the simplest thing might be to put unique indexes on one or more of the fields. That way, the database would reject any duplicates, and you can just catch the exception.

Otherwise, you need to do a search for the new values before storing them. I don't know the syntax to do that with a "data adaptor".

[gayano]

Thank you killer42,

anyway when it catch the errors but unfortunatly it still insert the data.
any wrong in the code??

like where shoukd i catch the exception??

can someone do a example in here??!!