Does a record exist in a table

[HalErp]

I am using MySQL with VB.net 2005 I have spent 1 hr trying to figure this problem with out success it is time to ask for help!

The problem is:

If the record exist I will need to use Update. If the record does not exist I will of course add it. My problem is how do I tell if the record exist or not so that I can create the Update or Insert.

I know there must be a simple answer but I do not know it.

Thanks guy and Gals

Hal:-(

 

[oldbear]

Hi

You could perform a select statement using the key for that record.

select @var = count(*)
from mytable
where key = @key

Then check the count to see whether that record exists in an if statement. The two branches of the statement would either update or insert.

Look at using the EXISTS statement (look at sql server books online), this may perform faster.

Hope this helps

Chris Seary

 

[DavidT_macktool]

Look into the DataView.Find method to locate existing records of the dataset.
You create a dataview and sort on the column you are searching for. If the record is found it will return an integer value of the location of the record. You are searching a key field.
Note that SearchValue could be a string to search for also.

Dim DV As DataView
Dim I As Integer = -1
Dim SearchValue As Integer = 12345
DV = New DataView(DsDataset11.Tables("TableName"))
DV.Sort = ("ColumnName1")
I = DV.Find(SearchValue)

If I > -1 Then
  Me.BindingContext(DsDataset1, "TableName").Position = I
End If

 

[TechGnome]

In our shop here, everything has an ID. When we go to save, if the ID (this is in the VB code) is 0, we call the insert. If it is NOT 0, then we call the update.

-tg

 

[HalErp]

Thank you all for you good ideas. I just bumped into another way to check for the existence of a record if you are using ADO you can


cmdString = "SELECT SchedulePlans.* " + _
"FROM SchedulePlans " + _
"WHERE SchedulePlans.ScheduleID = " + strScheduleID
odbCommandSPlan.Connection = odbConnSPlan 'This is the connection that was created in the Form Load event
odbCommandSPlan.CommandText = cmdString 'Set the command text property of the command object to the select statement described above.
odbDataReaderSPlan = odbCommandSPlan.ExecuteReader 'Executes the select statement described in the cmdString and places the results into the data reader object
If odbDataReaderSPlan.HasRows Then
Update
Else
Insert
End If

I have already implemented one of the other suggestions so I have not actually tired it