how can i increment a sequence if a data is already in a database.
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.
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
sequence do not increment
SQLMonger66
Hi,
You forgot to define the connection which the command should use.
Data.SqlClient.SqlCommand("UPDATE books (sequence =(MAX(sequence)=+1)) WHERE booktitle = '" & TextBox2.Text & "'", yourConnectionObject)
cheers,
Paul June A. Domag
moontube
Hi,
Where are you executing this command I suggest on calling the ExecuteNonQuery() method of the command after the Update() call...
cheers,
Paul June A. Domag
Raffaele Rialdi
when i change my code into:
If BOOKEXIST() Then
DBCmd = New Data.SqlClient.SqlCommand("UPDATE books (sequence =(MAX(sequence)=+1)) WHERE booktitle = '" & TextBox2.Text & "'")
DBCmd.Parameters.Add("sequence", Data.SqlDbType.Int).Value = (MAX(sequence) = +1)
DBCmd.ExecuteNonQuery()
Else
DBCmd = New Data.SqlClient.SqlCommand("UPDATE books (sequence= 1 WHERE booktitle = '" & TextBox2.Text & "'")
DBCmd.Parameters.Add("sequence", Data.SqlDbType.Int).Value = 1
DBCmd.ExecuteNonQuery()
End If
I get an error that states:
ExecuteNonQuery: Connection property has not been initialized.
InvalidOperationException was unhandled by user code.
By the way thanks for your responses.
el_Fede
I have rewriten my code and still the sequence doesnt increment.
If BOOKEXIST() Then
DBCmd = New Data.SqlClient.SqlCommand("UPDATE books (sequence =(MAX(sequence)=+1)) WHERE booktitle = '" & TextBox2.Text & "'")
DBCmd.Parameters.Add("sequence", Data.SqlDbType.Int).Value = (MAX(sequence) = +1)
Else
DBCmd = New Data.SqlClient.SqlCommand("UPDATE books (sequence= 1 WHERE booktitle = '" & TextBox2.Text & "'")
DBCmd.Parameters.Add("sequence", Data.SqlDbType.Int).Value = 1
End If
GlitchEnzo
From what I see in your code, you do NOT execute any SQL statements that increment your sequence number. You have created INSERT SQL statements, but I do not see any code that excutes them.
toyoung
Hi,
Yep, it seems that you are not using your queryString variable. By the way your code runs, I think this would not work if you were to place it in your adapter. So if your adapter is inserting your record when you call the Update method of the adapter it would be much more convenient if you change your query string to UPDATE instead of INSERT and execute it using a SqlCommand after the Update call to your adapter. The reason for this is when you call your Update method the record would then by apended to your table after appending you should the update it to the incremented value.
cheers,
Paul June A. Domag