Using dates between VB and SQL

Question

I am building a school project which is a room booking service. I have reached the part of the programming where I need to figure out how to store bookings. I know how to pull from a database using VB and I've laid it out like that when I try to pull booking information from the database.

    strBookingQuery = "SELECT * FROM bookings WHERE Date = '" & InputBookingDate & "'"
    Dim Cmd As New MySqlCommand(strBookingQuery, Newconnection)
    Newconnection.ConnectionString = strServerString
    Newconnection.Open()
    reader = Cmd.ExecuteReader()
    reader.Read()

    StartPeriod = reader.GetInt16(1)
    Length = reader.GetInt16(2)
    UserID = reader.GetInt16(3)
    RoomID = reader.GetInt16(4)
    Newconnection.Close()

But when it gets to StartPeriod = reader.GetInt16(1) it says Invalid attempt to access a field before calling Read(), is this because MySQL stores dates in a different way? Or something else? Any help would be greatly appreciated :)

since you have mentioned is this because MySQL stores dates in a different way.... assumed that StartPeriod is a date type column. Please comment if it's not, if so my answer isn't valid. — bonCodigo
– bonCodigo, Commented Jan 29, 2013 at 14:22

Community · Accepted Answer · 2017-05-23 10:31:59Z

1

How do you expect GetInt16 to read a date?

Reader.GetInt16() gets the value of the specified column as a 16-bit signed integer. No conversions are performed; therefore, the data retrieved must already be an Int16 or coercible to an Int16.

Given that OP confirmed there's no issue with startPeriod, the possible issue could be:

Invalid attempt to access field before calling read()

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 29, 2013 at 14:19

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Henry Penton Over a year ago

Ah apologies I forgot to include an earlier bit of code. Where it says InputBookingDate that's reading from a Date variable. Start Period, Length, UserID and RoomID are all just integers.

bonCodigo Over a year ago

Please add that code as well. Further, can you confirm that you are only returning Start Period, Length, UserID and RoomID and they are in order? If not please show the entire query plus the columns you are pulling out.

Henry Penton Over a year ago

InputBookingDate = DateTimePicker1.Value.Date ^ I did this to isolate it so it was just a date. GetBookingData() ^ That runs the function which is the code I previously showed. Public BookingDate() As Date Public StartPeriod As Integer Public Length As Integer Public UserID As Integer Public RoomID As Integer ^ These are the variables which they are being stored to.

Collectives™ on Stack Overflow

Using dates between VB and SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related