Booking Album system

[darthswift00]

Hi i am trying to do a transaction. It's basically were a customer makes a booking of a particular ablum(dvd or cd). In my database i have a table called Transaction with the following colomun names: Trasaction ID , MemberID, AlbumID , issueDate and ReturnDate.

Here is were the problem is as i have the AlbumID linked to another table called: Albums . Here in the Album table is the following column names: AlbumID, AlbumName, Quantity_Available and AlbumType(DVD or CD). The transaction made , when a customer books a particular album e.g: DVD = e.g: JamesBond , then in the database the quantity of that DVD is say for e.g: 30. It should then be 29 after it has been booked.

How do i do this when i have a form that does transactions and link it by updating the album table (the coloum Quantity available)

Thanks.

[Begginnerdev]

You will have to create a query that gets the current value and subtracts one.

Dim sqls as String = "UPDATE table SET column= column - 1 WHERE unique=value"

[darthswift00]

Okay so how does one go about it? I am a beginner at VB.NET. If you could give me an example of what you mean please.
thanks

[darthswift00]

Oh k Thanks will i do this under the save button of the form when the transaction has been made? And would i have to open a connection to do this code?

[Begginnerdev]

If you have a connection and command already declared just do this.

command = New Command(sqls,connection) 'Substitute "Command" With your adapter type.

[darthswift00]

Dim sqls as String = "UPDATE table SET column= column - 1 WHERE unique=value"

I have tried this code but it seems to give me an error saying Can not convert string to 'system.data.dataset.'

i used it in the follwoing:

sqladapter.update(sql,"album")

Is this right?

[Begginnerdev]

Should look something like this:

    Dim sqls As String = "UPDATE table SET column= column - 1 WHERE unique=value"
    Dim con As New SqlConnection(connection string here)
    Dim cmd As New SqlCommand(sqls, con)
    cmd.ExecuteNonQuery()

You can also do this:

sqladapter.UpdateCommand = sqls
sqladapter.Update("album")

[darthswift00]

I want to know wheter i should i put this code in my transaction form or the album form?

if i put it in the transaction form, do i do this under the trasaction save button?

i gt the pictures of how my forms wolud look like.

would u want the coding for each form?

[Begginnerdev]

If I were you, I would place the code in the transaction form. That way the new total is calculated as soon as the transaction is submitted.

[darthswift00]

Sorry again but i seem to be still geting the same error

    SqlDACollAdd.UpdateCommand = sqls
    'string cant be converted'


    SqlDACollAdd.Update("album")
    'overload resolution'

I declared the sqls in the global part of the form and put the code that has the erros in the save button of the transaction like so:

    cmd.ExecuteNonQuery()
    SqlDACollAdd.UpdateCommand = sqls
    SqlDACollAdd.Update("album")

Is this right? Thanks

[darthswift00]

Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE unique=value"
Dim con As New SqlClient.SqlConnection(sqls)
Dim cmd As New SqlClient.SqlCommand(sqls, con)

This i declared global part of the transaction form.

[Begginnerdev]

You are trying to bind the conenction to the statement string.

You will have to use the connection string in the SQLClient.SQLConnection.

You can get your connection string from here

[darthswift00]

error appering by:

cmd.ExecuteNonQuery()
it says this:
Incorrect syntax near the keyword 'unique'.

i did this in the codn:

declred globally:
Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE unique=value"
Dim con As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
Dim cmd As New SqlClient.SqlCommand(sqls, con)

in the buuton save:

    con.Open()
    cmd.ExecuteNonQuery()
    SqlDACollAdd.UpdateCommand = SqlSelectCommand1
    SqlDACollAdd.Update(DataSetCollAdd1, "album")
    con.Close()

the sqladapter u see her, i draged and droped it to the transaction form from the album form. Would that cause any problem?
Basically then there are two sqladapters:
1. Ther transaction form has already.
2. The one i draged from the album form.

[Mitja Bonca]

error appering by:

.ExecuteNonQuery()
t says this:
ncorrect syntax near the keyword 'unique'.

unique is a column name in your database. maybe he only gave you an example name, you have to change it to your actaul column name!

[darthswift00]

Thanks again bt now the code is sayn after i add a transaction,

invalid coloumn name='value'

mst i put the following:

Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE quantity_available=value"

What must i put by value?

[darthswift00]

Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE quantity_available=value"

sorry must i put a number by value?

[Mitja Bonca]

"value" in your case, must be a number.

1. Or to pass number directly:

   Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE quantity_available=1"

which is in no good, if your parameter is changing (normally is does)

1. Or you create a parametrized query:

   Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 WHERE quantity_available=@parameter1"
   Dim conn As New SqlConnection("connString")
   Dim cmd As New SqlCommand(sqls, conn)
   cmd.Parameters.Add("@parameter1", SqlDbType.Int).Value = 1 'declaring parameter
   'or pass some integer variable instead, like:
   'int myValue = 2;
   'cmd.Parameters.Add(@"parameter1", SqlDbType.Int).Value = myValue;

Hope it helps,bye

[darthswift00]

Hi i got it to work using this:

    Dim holder As String
    holder=txtAID.text

    Dim sqls As String = "UPDATE album SET quantity_available= quantity_available - 1 where album_id='" + holder + "' "
    Dim con As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
    Dim cmd As New SqlClient.SqlCommand(sqls, con)

    con.Open()
    cmd.ExecuteNonQuery()
    SqlDACollAdd.UpdateCommand = SqlSelectCommand1
    SqlDACollAdd.Update(DataSetCollAdd1, "album")
    con.Close()

I found out that by placing this holder it didnt change all the coloumn quantity_availables in it as it was.
I also had to copy the sqladapter,connection and dataset for the Album form and place it in the trasastion form for it to work.

Thanks again for all the help...
God Bless