ADO.NET in VS 2005

  • 11 years ago

    Hi,

    I've got a child table called: Booking for saving user bookings, with three parent tables namely: User, Venue, and Booking Type. All in an Access DB, now here's my scenario: I want to insert data into the Booking table depending on on the type of  booking the user selects like so:

    1. if booking type is meeting, then only userID(FK), startDate, startTime, endTime, and venue must be saved, including booking type ofcourse. 

    2. if booking type is leave, then only userID(FK), startDate and endDate must be saved, including booking type ofcourse.

    2. if booking type is other, then only userID(FK), startDate, startTime, endTime, and description must be saved, including booking type ofcourse.

    In essence, the only required fields are: userID, bookingType, startDate, and the rest are optional.

    I want to achieve this in VB, and I am a newbie.

  • 11 years ago

    I found my answer, and it had nothing to do with relationsips as I mentioned above. The problem is with the SQL syntax. For VS 2005 to be able to read date/time values, each and every date/time value must be enclosed with hash(#) characters like so:

    DBComm.CommandText =

    "INSERT INTO Booking ( userID, [password], bookingType, startDate, endDate)VALUES ('" & lblUser.Text & "', '" & txtPass.Text & "', '" & cmbType.Text & "', #" & cmbStartD.Text & "#, #" & cmbEndD.Text & "#)"

  • 11 years ago

    You shouldn't be building SQL statements with string concatenation anyway.  You should be using parameters, e.g.

    INSERT INTO Booking (userID, [password], bookingType, startDate, endDate) VALUES (@userID, @password, @bookingType, @startDate, @endDate)

    Then you add parameters to your command object and set the values with actual Date objects.  Then there's no need to quote anything so it's never an issue, plus there are other advantages.





Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.” - Brian Kernighan