Record Existing Coding request

[Cally_Law]

Can anyone help on how to show a message box showing that existing record has been entered in database if i tried to enter the new record. However, if the input is new record (record added), the record is saved into the database. I'm using visual basic 2008 and Microsoft access for database.

[Pgmer]

You mean to say User should be givin msgbox if he tries to add existing item?
Are you showing the items on ur front end?
you can use two aproaches here

1)If you are showing the items on listbox or grid just validate the new entry with these if exisit show msgbox and come out.

2) Or whatver user enters go to database and check if item exist.. and return true or false... use that boolean flag to show msgbox.

[Netcode]

What database are you using
If a database is in use, do you have a column defined as a primary key column so each time in that column can be unique?

[ShahanDev]

When you execute your "command" use ExecuteScalar() method to accomplish the task.

You should do like this:

'Define your OleConnection, connectionstring etc..

Dim cmd as OleDBCommand(........)  'pass connectionstring,connection object etc
      
Dim AffectedRecords as Integer = cmd.ExecuteScalar()

If(AffectedRecords>0)then    ' if condition match there is some record added.
   ' Display your message to alert user.
End if

[Cally_Law]

You mean to say User should be givin msgbox if he tries to add existing item?
Are you showing the items on ur front end?
you can use two aproaches here

1)If you are showing the items on listbox or grid just validate the new entry with these if exisit show msgbox and come out.

2) Or whatver user enters go to database and check if item exist.. and return true or false... use that boolean flag to show msgbox.

No, not to add existing, is not allow to add if the record already exist.the record was alrdy in database, but when user key in if exist then i wan the msg box will show "the record was exist".can you show me example coding according your question number 2 ?

[Cally_Law]

When you execute your "command" use ExecuteScalar() method to accomplish the task.

You should do like this:

'Define your OleConnection, connectionstring etc..

Dim cmd as OleDBCommand(........)  'pass connectionstring,connection object etc
      
Dim AffectedRecords as Integer = cmd.ExecuteScalar()

If(AffectedRecords>0)then    ' if condition match there is some record added.
   ' Display your message to alert user.
End if

Can show me full example coding ? because i'm vb newbie and doing first project, don't much much connection thing. Thanks a lot.

[Cally_Law]

What database are you using
If a database is in use, do you have a column defined as a primary key column so each time in that column can be unique?

i'm using microsoft access for database and SQL coding. i defined column ID as primary key.

[ShahanDev]

Hi!

Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "YourConnectionStringHere"
  
    ' Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    ' set the SQL string
    strSQL = "INSERT INTO Employee(FirstName,LastName)" & _
                      " VALUES ( 'Beth' , 'Hart' )"

    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    ' execute the command
    Dim AffectedRecords as Integer = objCmd.ExecuteScalar()

    If(AffectedRecords>0)then    ' if condition match there is some record added.
       '  Display your message to alert user.
    End if

"YourConnectionStringHere" refers the connection string of Database.

Also, here is another sample:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executescalar%28v=vs.71%29.aspx

Hope it helps!

Regards,
Shahan

[Cally_Law]

Hi!

Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "YourConnectionStringHere"
  
    ' Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    ' set the SQL string
    strSQL = "INSERT INTO Employee(FirstName,LastName)" & _
                      " VALUES ( 'Beth' , 'Hart' )"

    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    ' execute the command
    Dim AffectedRecords as Integer = objCmd.ExecuteScalar()

    If(AffectedRecords>0)then    ' if condition match there is some record added.
       '  Display your message to alert user.
    End if

"YourConnectionStringHere" refers the connection string of Database.

Also, here is another sample:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executescalar%28v=vs.71%29.aspx

Hope it helps!

Regards,
Shahan

can explain this line code to me?
strSQL = "INSERT INTO Employee(FirstName,LastName)" & _
" VALUES ( 'Beth' , 'Hart' )"

Employee is my table name or column name? and how about last VALUES ( 'Beth' , 'Hart' )"?
my First name need set primary key or no need?

[ShahanDev]

Hi!

Employee----------->Table name

FirstName---------->ColumnName
Its value is -------------------->'Beth'

LastName----------->ColumnName
Its value is -------------------->'Hart'

my First name need set primary key or no need?

No, it can not be a primary key. First name can be similar for more than one records.

If you have 'ID'(Primary Key) assuming integer datatype, 'FirstName' assuming datatype 'text', 'LastName' assuming datatype 'text', in your table named Employee then you can re-write the query as:

strSQL = "INSERT INTO Employee(ID, FirstName,LastName)" & _
" VALUES (1, 'Beth' , 'Hart' )"

Where:
1----------------------> ID
Beth-------------------> First Name
Hart-------------------> LastName

[Cally_Law]

Hi!

Employee----------->Table name

FirstName---------->ColumnName
Its value is -------------------->'Beth'

LastName----------->ColumnName
Its value is -------------------->'Hart'

No, it can not be a primary key. First name can be similar for more than one records.

If you have 'ID'(Primary Key) assuming integer datatype, 'FirstName' assuming datatype 'text', 'LastName' assuming datatype 'text', in your table named Employee then you can re-write the query as:

strSQL = "INSERT INTO Employee(ID, FirstName,LastName)" & _
" VALUES (1, 'Beth' , 'Hart' )"

Where:
1----------------------> ID
Beth-------------------> First Name
Hart-------------------> LastName

ok, then the 'Beth' & 'Hart' izit must put ? when i copy your code and paste on my program , when i press save button ,the table show Beth Hart in my database.

[Netcode]

can explain this line code to me?
strSQL = "INSERT INTO Employee(FirstName,LastName)" & _
" VALUES ( 'Beth' , 'Hart' )"

Employee is my table name or column name? and how about last VALUES ( 'Beth' , 'Hart' )"?
my First name need set primary key or no need?

This goes to show you need to do some more reading to know what you want.

[Cally_Law]

Hi!

Employee----------->Table name

FirstName---------->ColumnName
Its value is -------------------->'Beth'

LastName----------->ColumnName
Its value is -------------------->'Hart'

No, it can not be a primary key. First name can be similar for more than one records.

If you have 'ID'(Primary Key) assuming integer datatype, 'FirstName' assuming datatype 'text', 'LastName' assuming datatype 'text', in your table named Employee then you can re-write the query as:

strSQL = "INSERT INTO Employee(ID, FirstName,LastName)" & _
" VALUES (1, 'Beth' , 'Hart' )"

Where:
1----------------------> ID
Beth-------------------> First Name
Hart-------------------> LastName

Got error and cant work.

If (AffectedRecords > 0) Then    ' if condition match there is some record added.
            '  Display your message to alert user.
            Me.Validate()
            Me.EmployeeBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.ExampleDataSet)
            MsgBox("Record Added")
        Else
            
            MsgBox("name already exist")
        End If

i put like tat izit correct?

[ShahanDev]

Please remove the space between "First<Space>Name" from your query.

Also, do you want updation using "DataSet" (as i have seen in your attachment, "DataSourceWindow" contains a "dataset") or or via "connectionstring"(the code i have recommended).

There will be a bit of change in code.

[Cally_Law]

Please remove the space between "First<Space>Name" from your query.

Also, do you want updation using "DataSet" (as i have seen in your attachment, "DataSourceWindow" contains a "dataset") or or via "connectionstring"(the code i have recommended).

There will be a bit of change in code.

i try remove the space , but it will auto generate the BETH and HART , cannot show out record exist
and my coding above post izit correct if (AffectedRecords > 0)?
shall we chat in msn ?
my msn is [removed; keep it on site]

[ShahanDev]

Hi!

I don't have msn install. Also, chatting might not be useful. you need to know the working of code and how to make it useful.

Have a look at this video to see how to write queries and how to check in Access database.

http://www.youtube.com/watch?v=bGpFspsdzTI

Also, I am working on a test project that will help you. Will upload here soon.

[ShahanDev]

Hi!

Here is a code i have made to update the table through connectionstring

See the attachment.

How to use:

enter id in the textbox and press insert button.

How to check in database??

Please see the video i have recommended in the previous post.

[Cally_Law]

Hi!

Here is a code i have made to update the table through connectionstring

See the attachment.

How to use:

enter id in the textbox and press insert button.

How to check in database??

Please see the video i have recommended in the previous post.

i copy ur code and paste in my program ,but look like doesn't work , keep pop out ID already exist even the data exist or not.

here is my testing program. u can help me have a look to c what problem of my program? i only wan username existing if the username had been key in before.

[ShahanDev]

Hi!

Did you test my application alone?? I mean without integrating in your project. It should work...

[Cally_Law]

Hi!

Did you test my application alone?? I mean without integrating in your project. It should work...

yea..i had try out yours application , it can work , but use on my project with same code,dunno why cant work,can help me check what is the problem ?

[ShahanDev]

How did you checked my app ??

Can you mention steps ? because i have sent you tested code.

[ShahanDev]

Hi! friend,

Column names in Employee table of your Database is different from mine database.

You need to change this query and specify your column names not mine.

strSQL = "INSERT INTO Employee VALUES (" & Val(UsernameTextBox.Text) & ")"

like:

strSQL = "INSERT INTO Employee VALUES (ALLYOURCOlumnNamesCommaSeperated)"

[ShahanDev]

See attachment for the difference of fields.

[Cally_Law]

Hi! friend,

Column names in Employee table of your Database is different from mine database.

You need to change this query and specify your column names not mine.

strSQL = "INSERT INTO Employee VALUES (" & Val(UsernameTextBox.Text) & ")"

like:

strSQL = "INSERT INTO Employee VALUES (ALLYOURCOlumnNamesCommaSeperated)"

but only need username don't repeat , if i fill in all my column name will influence any ?
i really not understand the VALUES part.

and you said the column name , but i saw urs also put xxx.text ,not column name

i know the different of field ,the text word but i got change in my program. izit i nid change autonumber too?

or u can help me correct my code ? ==

[ShahanDev]

Hmmm.... My bad i write this one wrong:
'ALLYOURCOlumnNamesCommaSeperated' it should be 'AllValuesCommaSeparated'

>>>> but i saw urs also put xxx.text ,not column name.
xxx.text is a value of textbox. you can hard code it some values for testing it should run atleast once.

>>>but only need username don't repeat.
When you will insert the record and it will raise error if could not insert thats' why i have put it in a try-catch block and alert user it already exists if error occurs.
Your user name should be a primary key (having IsUnique equals to true in MsAccess)

[ShahanDev]

Hi!

These are equivalent:
strSQL = "INSERT INTO Employee VALUES (13,'Shahan','Ayyub')"
'OR
strSQL = "INSERT INTO Employee VALUES (" & Val(txtID1.Text) & ",'Shahan','Ayyub')"

IF:
txtID1.Text = 13

See the attachments:

[Cally_Law]

Hi!

These are equivalent:
strSQL = "INSERT INTO Employee VALUES (13,'Shahan','Ayyub')"
'OR
strSQL = "INSERT INTO Employee VALUES (" & Val(txtID1.Text) & ",'Shahan','Ayyub')"

IF:
txtID1.Text = 13

See the attachments:

i get what your mean already i did it if got values,but i don't wan set values 'shahan','Ayyub', because i wan request user to key in record in textbox then the text in textbox save in database when i click save, if i set the values, my record can save in database. Can you get what i mean ? sorry ya, i pronoun not so good.

[ShahanDev]

Okay...Kindly answer few questions: (in the same sequence)

1) So you mean if you write like this:
'shahan','Ayyub'

it is saving ?? Right ??

2) Are you looking for a code to take values from all text boxes and then save in database.

3) Are you looking for a code to check whether this particular user exists in database OR not ?

Reagrds,
Shahan

[Cally_Law]

Okay...Kindly answer few questions: (in the same sequence)

1) So you mean if you write like this:
'shahan','Ayyub'

it is saving ?? Right ??

2) Are you looking for a code to take values from all text boxes and then save in database.

3) Are you looking for a code to check whether this particular user exists in database OR not ?

Reagrds,
Shahan

reply
1) 'shahan','Ayyub' is directly write in coding,not i mean for.And i wan is on text box's text that user key in can insert into database and check whether exists or not is only my ID, izit possible to do that?

2)yea
3)yea

[ShahanDev]

2)
You should use google to understand how to make query dynamic to take values from text box. I would recommend reading articles on how to do this.
s
One thing you should know that:
When string pass to the database with a single quotes like if you have Berth in textbox when you will write query it should look like 'berth' within the query.
on the other hand when you pass a number like id etc say id=10 so it should pass as it is.(no single qoute around it.)

3)

dim str as string = "select * from Employee where username='" & txtusername.text & "';"
    ' Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
dim dt  as new datatable ()
dim da as new sqldataadapter(objcmd)
da.fill(dt)

if(dt.rows.count>0)then
  ' if this condition comes the username exists in the database.
end if

and apologize for the too late response.