sequence not incrementing

chi2king

Member
Joined
Sep 28, 2006
Messages
20
Programming Experience
Beginner
Whats wrong with my code? When a new book is added it doesnt give error BUT the sequence is not incremented if a same booktitle is entered.
I have three columns(author, booktitle, sequence). Sequence should increment if book with same title is added. But with my codes it doesnt.

VB.NET:
Protected Sub save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles save.Click
        Dim DBConn As New Data.SqlClient.SqlConnection("DATA SOURCE = xxx;Initial Catalog= xxx;Integrated Security=True")
        Dim myDataAdapter As New Data.SqlClient.SqlDataAdapter
        myDataAdapter.SelectCommand = New Data.SqlClient.SqlCommand("Select * from books", DBConn)
        Dim cb As Data.SqlClient.SqlCommandBuilder = New Data.SqlClient.SqlCommandBuilder(myDataAdapter)
        DBConn.Open()
        Dim ds As Data.DataSet = New Data.DataSet
        myDataAdapter.Fill(ds, "books")
        Dim workRow As Data.DataRow = ds.Tables("books").NewRow()
        workRow("AUTHOR") = TextBox1.Text
        workRow("BOOKTITLE") = TextBox2.Text
        
        If BOOKEXIST() Then
            Dim queryString As String = ("INSERT INTO books (sequence) VALUES (MAX(sequence)=++1) WHERE BOOKTITLE = '" & TextBox2.Text & "'")
        Else
            Dim queryString As String = ("INSERT INTO books (sequence) VALUES (1)")    'if no same title
        End If
        ds.Tables("books").Rows.Add(workRow)
        myDataAdapter.Update(ds, "books")
        DBConn.Close()
        Return
    End Sub
 Function BOOKEXIST() As Boolean
        Dim connectionString As String = ("DATA SOURCE = xxxx; INITIAL CATALOG = xxx;Integrated Security=True")
        Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String = "SELECT MAX(sequence) FROM books WHERE (BOOKTITLE = '" & TextBox2.Text & "')"
        Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
        Dim Cmd As New Data.SqlClient.SqlCommand(queryString, sqlConnection)
        With Cmd.Parameters
            .Add(New Data.SqlClient.SqlParameter("@BOOKTITLE", TextBox2.Text))
        End With
        sqlConnection.Open()
        Dim blHasRows As Boolean
            Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        If objDR.Read() Then
            blHasRows = True
        Else
            blHasRows = False
        End If
        Return blHasRows
    End Function
 
Try using a trigger when you insert a record that gets the max sequence number and adds one, then inserts it into the record. This should always be done on the database side. You only hurt your program's performance this way....
 
Back
Top