does record exist?

[Megalith]

i'm developing a simple database for an application. In essence the database is updated by adding a new record if a unique ID is not found.
the unique ID is a part number and a description of the part and its quantity in stock are the elements to be added working along the following VBish code

if ID exists then
     retrieve description and quantity
     add to quantity and update database
else 
     create new row with description and quantity
end if

is there a quick way to determine if a record exists. i was thinking maybe SQL here and doing a search for the ID returning false if the result is found.

This ADO is very new to me, if i have created a dataset in another subroutine( say its private) does it still reside in memory? do i have to connect each time i need to access it?
i'm having trouble grasping the concept of what ADO actually does.

 

[cjard]

You dont generally do this IF malarkey first because it's a wasted step. When you query the database you either get a result or you do not get a result. If you did not get a result it is because there is no result to get.

In the .NET 2 that youre using I would simply go to the DataSet designer surface and locate the TableAdapter of the DataTable that will be filled with whatever data you want to add/update. I.e. if you want to Add/update customer data, go find the CustomerTableAdapter under the CustomerDataTable
To the table adapter, add a query that will return at most one row - the row you wish to add or update.

THen, in your program code, make a CustomerDataTable (i'll call it tempDT) and fill it using the new query you wrote. After filling:

Dim tempDT as New CustomerDataTable
customerTA.FillByNameAndAge(tempDT, "fred smith", "28")
 
If tempDT.Rows.Count = 1 Then
  'we found the info, update row 0
  tempDT(0).Name = "Freddo Smith"
  tempDT(0).Age = 29
Else If tempDT.Rows.Count = 0
  tempDT.AddCustomerRow("Freddo Smith", 29)
Else
  MessageBox.Show("more than one row details matched. This scenario is intolerable")
  Return
End If
 
'send changes back
customerTA.Update(tempDT)

See what I mean, there;s no point working out oif the row exists or not.. just try and get it, youll get 0 results if it doesnt exist

 

[cjard]

This ADO is very new to me, if i have created a dataset in another subroutine( say its private) does it still reside in memory? do i have to connect each time i need to access it?
i'm having trouble grasping the concept of what ADO actually does.

For some further info about ADO and DataSets, read/see the two links in my signature about the DS Designer Surface and ADO.NET2 Oreintation

Any dataset you create in any method, only exists while that method is executing. If you have methodA and methodB, both of whom declare datasets, and they are called by methodC:

methodA()
methodB()

then when methodA finishes its dataset is destroyed. THis applies for all local variables and is referred to as the concept of SCOPE.

 

[Megalith]

thx for responding so quickly cjard,

i kinda thought datasets were destroyed, i presumed that opening a database was a time consuming exercise and opening it evrytime you need to perform a database related method that would slow down the execution.

I seem to be stuck in a last millenium attitude when it comes to databases

yes it makes sense to only call the routine once and create it if it doesnt exist otherwise update it. thanks for that routine, only a slight modification to my existing code will
allow it to work.

Thanks for the reading material too, i really need to bring my database skills upto date.

 

[Megalith]

The theory is great, the practice is needed

I implemented the routine above into my application(after modifications) but it throws an exception

I have the database operations in a class called DBOpps to create a DAL, and another Class that creates the Data called DataBuild, the method that generates this error in DBOpps is this:-

    Friend Sub AddThrows(ByVal throwNumber As String, ByVal gameText As String)
        ' Create a new Connection and SqlDataAdapter
        Dim myConnection As New SqlClient.SqlConnection(My.Settings.ThrowHistoryConnectionString)
        Dim mySqlDataAdapter As New SqlClient.SqlDataAdapter("Select * from Throw Details", myConnection)
        Dim tempDT As New ThrowHistoryDataSet

        tempDT.Throws.FindBythrowNumber(throwNumber)

        If tempDT.Throws.Rows.Count = 1 Then
            'we found the info, update row 0
            tempDT.Throws.Item(0).ThrowNumber = throwNumber
            tempDT.Throws.Item(0).ThrowDetails = gameText
        ElseIf tempDT.Throws.Rows.Count = 0 Then
            [I][B]tempDT.Throws.AddThrowsRow(throwNumber, gameText)
[/B][/I] 
        Else
            MessageBox.Show("more than one row details matched. This scenario is intolerable")
            Return
        End If

    End Sub

the exception i recieve where the bold text is above is this:-

System.ArgumentException was unhandled
Message="Cannot set column 'throwNumber'. The value violates the MaxLength limit of this column."

The database contains only one row at the moment with the values
throwNumber = "1"
gameText = "Test"

both of the fields are defined as being string variables

the data being added is as follows
throwNumber = "#1000100143:"
gameText = "Dave throws first scores 100 (20,60,20) ............. Mark wins the game, the leg and the match."

i tried initially using nChar(12) for the handNumber field but changed to string after this error

what have i done wrong in this code? also should i be declaring something at the start of this class to simplify my code?

Is there a limit to the Max length on Column(0)? should i simplify the number by removing the first and last chars and making it an integer? i would sooner leave it as it is if i can but it wouldnt be much to change in the DataBuild class.

 

[cjard]

I think you might find that the value is causing a violation because NChar is unicode and therefore requires 24 bytes of storage space to store 12 characters.

I'm not sure, but I would suspect that it is your dataset validation rather than the database in this regard, but that's what I would immediately suspect.

In the dataset designer, ensure that you have set the max length of the column it is complaining about, to a value suitably large enough to contain the data you are attempting to add.

additionally, there are differences between the database types char, nchar, varchar and nvarchar. If you do not know them for certain, be sure to look them up

 

[Megalith]

I found the problem there was a ' in the text i did a replace on the data to remove it and its fine