[2008] trouble updating database!!

[16bit]

Hello,

I have a strange problem. I have written the following code to insert details into my database the user can enter a quantity and the routine will run until
the vairable check = the vairable quantity.

now what is wierd is that it works fine but one it has entered record 2084, 2184, 2284 (anything ending in 84 basically) it stops working.
It stops returning the last value in the serial number field, it returns the previous and therefore wont insert into the database anymore as it will create a duplicate value in the primary key!

can anyone thing what is making it screw up?????

here's my code..........

Dim last As Integer

        Dim SerialNo As Integer

        Dim check As Integer = 0

        Do Until check = qty

            check = check + 1

            DT8000 = DB.GetTable("Select Last (serialNo) From Shipped")

            DR8000 = DT8000.Rows(0)

            last = DR8000("expr1000")

            SerialNo = last + 1

            DB.Insert("insert into Shipped (serialNo, Family, Model, SDate)   Values (" & serialNo & ", '" & Family & "', '" & model & "', '" & Year & "') "))

        Loop 

any help will be much appreciated, i am baffled!

cheers,

Iain

[Jenner]

Is that VB.NET code? What is DT8000's, DR8000's and DB's data type?

[wild_bill]

What database are you using? I'm not familiar with the 'last' keyword.
Use a parameterized command to prevent sql injection. Also setup your serialno as an identity column so you don't have to keep track of the last serial number. Here is an example for sql server

        Dim insertSql As String = _
        "insert into Shipped ( Family, Model, SDate) " & _
        "Values ('@family', '@model', '@year')"

        For check As Integer = 1 To qty


            Dim con As New Data.SqlClient.SqlConnection("connection string")
            Dim cmd As New Data.SqlClient.SqlCommand(insertSql)
            cmd.Parameters.AddWithValue("@family", family)
            cmd.Parameters.AddWithValue("@model", model)
            cmd.Parameters.AddWithValue("@year", Year)
            cmd.Connection = con

            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()

        Next

I would also look at creating a quantity column, so you make one insert.

[16bit]

i have this dll called sqlops that the college gave me
so i reference that in my program.
so
dim DB as new sqlops.MSAccess("C:/mydatabase.mdb")
dim dt8000 as datatable
dim dr8000 as datarow

the last you are onabout is an SQL command that will return the last record of a table, in my case i am returning the last reorded value in the SerialNo column of the Shipped table.

I have never used anything else to connect VB.net to a database as my tutor has only showed us the way with SQLOPS, i'm a bit annoyed with them for not teaching us the way everyone else does it!

[wild_bill]

Well, I guess if you're using a predifined data layer, there's not much you can do. Try changing your query to

Select MAX(serialNo) From Shipped

[16bit]

ok i'll have a go at that!
thanks everyone for your suggestions!