Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
| Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 16th, 2006, 05:11 AM
Authorized User
 
Join Date: Mar 2006
Location: bedlington, northumberland, United Kingdom.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Searching a database for an entry

Hope someone can help...I need to know how I can search an msDB to see if a user's chosen date is already saved in the DB. If it is, a message to inform them to make a new selection needs to be made because only one date can be in the database at one time, else save thier chosen date into the DB.
What's happening at the moment is because the DB date is the primary key, if the user chooses an already saved date, the program crashes with an exemption error.
I think this is something to do with Try/Catch but I can not understand how these work !!!
Hope I have someone out there who can help me!
xxx

 
Old March 18th, 2006, 11:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 540
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Use a SELECT statement to determine whether or not the date is already there prior to inserting a new value to the database.

If it is there, cancel the insertion and inform them to choose another date.
 
Old March 19th, 2006, 04:14 AM
Authorized User
 
Join Date: Mar 2006
Location: bedlington, northumberland, United Kingdom.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Katsarosj for your reply. I have managed to work out how to insert data, and now how to sort through the data saved in the database but unfortunatly what is happening now is that I am getting an exception error if the date is in the database already. I have tried Try/Catch but all this does is stop the user from choosing another date.
Here is my snippet if code. I have removed it from the try/catch because I have been told that for what i am doing it is not necessary. I have also been told that it is my If statement that needs work...but I can't see where.
Can you see what I have done? Or how I can fix it?
Thank you so much! xxx

        DsAllFields1.Tables(0).DefaultView.Sort = "date"
        intRow = DsAllFields1.Tables(0).DefaultView.Find("chosendat e")
        Debug.WriteLine(intRow)

        If intRow > 0 Then
            MessageBox.Show(OrgName & " We are sorry but this date is not available for booking. You
                  must now choose another.", "DATE UNAVAILABLE")

        Elseif intRow < 0 Then
            myConnection.Open()
            myRow("date") = chosendate
            myRow("name") = OrgName
            myRow("age") = orgAge
            myRow("house number or name") = orgHouse
            myRow("post code") = orgPostcode
            myRow("phone number") = orgPhone
            myRow("optionCode") = EventChoice

            'fills the dataset with the user-input data
            DsAllFields1.Bookings.Rows.Add(myRow)

            'updates the adapter with the dataset items
            Me.oleDABookinsAll.Update(DsAllFields1)

            MessageBox.Show("Your date has been accepted and this booking has now been confirmed
                   and stored. Thank you " & OrgName & "", MessageBoxButtons.OK)

            'closes the connection with the database
            myConnection.Close()
        End If


 
Old March 19th, 2006, 02:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 540
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I agree. For what you want to do, you don't necessarily need to place this in a Try/Catch. What this will do, however, is allow you to determine what the error is without crashing your program.

You could then, for example, write that message to a messagebox and display it or a more generic message for the end user. This message could then be relayed to "tech support" or something of that nature. So if you have a specific database connection/insertion error, and this was caught with the Try/Catch, you display a message like "There was an error connected to the database, please contact tech support at xxx-xxx-xxxx. Error number xxxx." The point being that you don't want to frighten the user with long, scary error messages - leave those to scare the developer. :)

The reason I don't think you necessarily need a Try/Catch in this instance is that you already know what your problem is, i.e., you know that you only have problems if the date already exists.

That being said, I would likely have to see more of your code to determine whether I could find the problem. I would also need to know what your table layout is, I assume you are using an Access database? Also, post the specific error message and what line it is pointing to.

J
 
Old March 20th, 2006, 01:23 PM
Authorized User
 
Join Date: Mar 2006
Location: bedlington, northumberland, United Kingdom.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have sent you the full module to see if you can see where I have gone wrong or, to see where I can go from here. Because I am new to programming this is fustrating me BIG STYLE!!! :(
I have an msAccess db with 1 table named Bookings and the date collumn(spelt as date) is in possition 1 if that helps any!(a collumn optionCode is in possition 0)
I have not any sql because that was done in the builder when I did the adapter and dataset(placed the adapter onto the form). The dataset has got the whole Bookings table stored when connection is open.
I hope this makes it a little clearer as to what I am doing, and to how to help me.
xxx

 Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click



        'variables to connect to the rows in a database
        Dim OrgName As String = txtOrgName.Text
        Dim orgAge As String = txtOrgAge.Text
        Dim orgHouse As String = txtOrgHouse.Text
        Dim orgPhone As String = txtOrgPhone.Text
        Dim orgPostcode As String = txtOrgPostcode.Text
        Dim AddName1 As String = txtAddName1.Text
        Dim AddAge1 As String = txtAddAge1.Text
        Dim Addname2 As String = txtAddName2.Text
        Dim AddAge2 As String = txtAddAge2.Text
        Dim Addname3 As String = txtAddName3.Text
        Dim AddAge3 As String = txtAddAge3.Text
        Dim chosendate As Date = dtpChooseDate.Value

        'declares the connection to a variable myConection
        Dim myConnection As OleDb.OleDbConnection = oleDCBookingsAll

        'variable for a Row/column/Table in a db
        Dim myRow As DataRow
        Dim myColumn As DataColumn
        Dim Bookings As DataTable

        'variable to hold the string value of the event type
        Dim EventChoice As String


        'creates a string variable holding event type to be read to the database
        If rdbFeed.Checked Then
            EventChoice = "FeedSP"
        ElseIf rdbHalf.Checked Then
            EventChoice = "WorkingHalf "
        Else : EventChoice = "WorkingFull "
        End If

        'Sets a new row to be filled in the db
        myRow = DsAllFields1.Bookings.NewBookingsRow

        'sets a search of the database for the chosen date
        Dim intRow As Integer
        myConnection.Open()
        DsAllFields1.Tables(0).DefaultView.Sort = "date"
        intRow = DsAllFields1.Tables(0).DefaultView.Find("chosendat e")
        Debug.WriteLine(intRow)

               If intRow < 0 Then

            myRow("date") = chosendate
            myRow("name") = OrgName
            myRow("age") = orgAge
            myRow("house number or name") = orgHouse
            myRow("post code") = orgPostcode
            myRow("phone number") = orgPhone
            myRow("optionCode") = EventChoice

            'assign the text variables to the rows of a specific column if they are set as visible on the form
            If grbAddPeople1.Visible And txtAddName1.Text <> "" Then
                myRow("extraNameOne") = AddName1
            Else : myRow("extraNameOne") = 0
            End If

            If grbAddPeople1.Visible And txtAddAge1.Text <> "" Then
                myRow("extraAgeOne") = AddAge1
            Else : myRow("extraAgeOne") = 0
            End If

            If grbAddPeople2.Visible And txtAddName2.Text <> "" Then
                myRow("extraNameTwo") = Addname2
            Else : myRow("extraNameTwo") = 0
            End If

            If grbAddPeople2.Visible And txtAddAge2.Text <> "" Then
                myRow("extraAgeTwo") = AddAge2
            Else : myRow("extraAgeTwo") = 0
            End If

            If grbAddPeople3.Visible And txtAddName3.Text <> "" Then
                myRow("extraNameThree") = Addname3
            Else : myRow("extraNameThree") = 0
            End If

            If grbAddPeople3.Visible And txtAddAge3.Text <> "" Then
                myRow("extraAgeThree") = AddAge3
            Else : myRow("extraAgeThree") = 0
            End If

            'fills the dataset with the user-input data
            DsAllFields1.Bookings.Rows.Add(myRow)

            'updates the adapter with the dataset items
            Me.oleDABookinsAll.Update(DsAllFields1)

            MessageBox.Show("Your date has been accepted and this booking has now been confirmed and stored. Thank you " & OrgName & "", MessageBoxButtons.OK)

            'closes the connection with the database
            myConnection.Close()

            'sets the confirm button to disabled after they have already confirmed booking
            btnConfirm.Enabled = False

            Exit Sub

Else: MessageBox.Show(OrgName & " We are sorry but this date is not available for booking. You must now choose another.", "DATE UNAVAILABLE")

        End If

    End Sub

 
Old March 20th, 2006, 01:28 PM
Authorized User
 
Join Date: Mar 2006
Location: bedlington, northumberland, United Kingdom.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

PS. Sorry I forgot to give you the error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Highlights on this line:
 Me.oleDABookinsAll.Update(DsAllFields1)
xxx

 
Old March 24th, 2006, 12:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 540
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Your error simply indicates that you are trying to update the database with a composite key that already exists.

Here is what I have found out from your solution:

In your btnConfirm_Click event you have this:

'sets a search of the database for the chosen date

Dim intRow As Integer
DsAllFields1.Tables(0).DefaultView.Sort = "date"

intRow = DsAllFields1.Tables(0).DefaultView.Find("chosendat e")

One, you are looking for values in your dataset before you have filled your dataset. So when you are trying to find the chosendate, there is nothing in there. That is why you are getting nothing returned.

Second, your line:

intRow = DsAllFields1.Tables(0).DefaultView.Find("chosendat e")

should read:

intRow = DsAllFields1.Tables(0).DefaultView.Find(chosendate )

chosendate is already a string so you don't have to wrap it in quotes or it will throw an error.

So to fill your dataset so you can search on it, in your form load event you can put something like this:

'add all of the values from the database to the dataset to be used later

Dim SQL As String = "SELECT * FROM Bookings"
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand

cmd = oleDCBookingsAll.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL

da.SelectCommand = cmd
da.Fill(DsAllFields1, "Bookings")

oleDCBookingsAll.Close()

----------------------------

This should fill your dataset so that you can search for your dates prior to adding a new entry. Hope this helps.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking for duplicate entry in Database sharon5469 ASP.NET 1.0 and 1.1 Basics 1 November 18th, 2007 06:08 PM
Reuse form for database entry kwng Pro VB Databases 0 April 30th, 2007 02:26 PM
Database Entry Problems gmoney060 Classic ASP Databases 4 November 9th, 2004 08:19 AM
Database entry from HTML forms gmoney060 Classic ASP Basics 4 March 31st, 2004 04:50 AM
Database Double Entry isg2000 Access ASP 1 June 13th, 2003 04:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.