automatic addition

[chi2king]

I have a book entry page design in asp.net 2.0, I can already add a new row to the database table by having to enter
details of the book into textboxes and then clicking a button. My problem is, i want the design to automatically
increase the number of books (NUMBER_OF_BOOKS column) whenever a new book is entered having the same title (TITLE
column) that is already stored in the database. But if the book title being entered doesnt exist yet then 1 will be
entered automatically (means you do not enter any number into a textbox for NUMBER_OF_BOOKS column since i dont have a
textbox for that) into the database column.

here is my code for inserting (adding) new book details.

Protected Sub addbutton_Click(ByVal sender As Object, ByVal e As 
System.EventArgs) Handles addbutton.Click
        Dim DBConn As New Data.SqlClient.SqlConnection("DATA SOURCE =xxxx;Initial Catalog= xxx;Integrated 
Security=True")
        Dim DBCmd As New Data.SqlClient.SqlCommand
        Dim DBAdap As New Data.SqlClient.SqlDataAdapter
        Dim DS As New Data.DataSet
        DBConn.Open()
        Try
            DBCmd = New Data.SqlClient.SqlCommand("INSERT INTO BOOK 
(TITLE, AUTHOR, PUBLISHER) VALUES (@TITLE, 
@AUTHOR, @PUBLISHER)", DBConn)
            DBCmd.Parameters.Add("@TITLE", Data.SqlDbType.VarChar).Value 
= TextBox1.Text
            DBCmd.Parameters.Add("@AUTHOR", 
Data.SqlDbType.VarChar).Value = TextBox2.Text
            DBCmd.Parameters.Add("@PUBLISHER", 
Data.SqlDbType.VarChar).Value = TextBox3.Text
 
            DBCmd.ExecuteNonQuery()
            Label11.Text = "You have added the '" & 
TextBox1.Text.ToString & "' book."
 
            DBAdap = New Data.SqlClient.SqlDataAdapter("SELECT * FROM 
BOOK", DBConn)
            DBAdap.Fill(DS)
 
        Catch ex As Exception
            Label11.Text = "You were not able to add the '" & 
TextBox1.Text.ToString & "' book."
 
        End Try
        DBCmd.Dispose()
        DBAdap.Dispose()
        DBConn.Close()
        DBConn = Nothing
    End Sub

 

[jmcilhinney]

It's not clear what you want. You could be saying that you want only a sinlge record for each name and the count value gets increased each time a book name is entered. You could be saying that want multiple records for each name where each record has the same count value. You could be saying that you want multiple records where the first has a count value of 1, the second has a count value of 2, and so on. Please clarify.

 

[chi2king]

You could be saying that you want multiple records where the first has a count value of 1, the second has a count value of 2, and so on.

I want that number_of_books column will automatically increment by one if same book title is entered. otherwise, if no book with same title exist in the database the number_of_books column data will be one (also automatically).

thanks

 

[jmcilhinney]

So it's not really the number of books in the database because the original record still has the same value it always did. It's just a sequence number. You can't say that it's the number of books if there are two books in the database and one them has 1 in that column.

Basically before you insert the value you need to perform a query to get the number of existing records with the same name:

SELECT COUNT(*) FROM Books WHERE Name = @Name

Provide the name of the book you're about to insert for the parameter value. That query will return a number. You add 1 to it and there's your sequence number for the new record.

 

[chi2king]

Can you please show me where am i gonna insert the increment code.

[LEFT]DBCmd = New Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM Books WHERE Name = @Name)", DBConn)[/LEFT]
 
DBCmd = New Data.SqlClient.SqlCommand("INSERT INTO BOOK 
(TITLE, AUTHOR, PUBLISHER) VALUES (@TITLE, @AUTHOR, @PUBLISHER)", DBConn)
            DBCmd.Parameters.Add("@TITLE", Data.SqlDbType.VarChar).Value 
= TextBox1.Text
            DBCmd.Parameters.Add("@AUTHOR", Data.SqlDbType.VarChar).Value = TextBox2.Text
            DBCmd.Parameters.Add("@PUBLISHER", 
Data.SqlDbType.VarChar).Value = TextBox3.Text

Thanks.

 

[jmcilhinney]

In the code you've posted you create the query on the first line and then you simply ignore it. You have to execute that query to get the result of the SELECT statement. Plus, "Name" was just an example I used. Your Book table has TITLE, AUTHOR and PUBLISHER columns, no Name column. It also has no column for the sequence number. I suggest that you have a look at the ADO.NET tutorial in my signature. Have another go and post back if you still have issues.