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.
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