Trying to find a way as to how can i display a message box to say stock not available when i link it to my database to show that the quantity_available of the album( typed in and is refered to by the album_id in the textbox) ?

here is my coding that i have tried bt not working everytime i type an album's id that as value in its quantity_availalbe coloumn:

    Dim con As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
    Dim sqls As String = "Select * from album where album_id='" + txtAID.Text + "' and quantity_available < 1 "
    Dim cmd As New SqlClient.SqlCommand(sqls, con)
    con.Open()
    Dim QA As Integer = cmd.ExecuteNonQuery()
    con.Close()
    If QA < 0 Then
        MessageBox.Show("Album is out of stock", "AlbumError", MessageBoxButtons.OK, MessageBoxIcon.Error)
        txtAID.Text = ""
        Return False
    End If
    Return True

i have a request form that is linked to a album form . evertime a request is made, an album gets taken out. but now what if there is no album available that is what i wana know how to do. album has it's on table and request has it's on table in sql. i however used albums sqladapter and dataset in requests form ( which as it's own sqladapter and dataset) on the form. Help would realy appretiate it thanks

Try creating DataTable and fill it with data, if there wil be no rows inside of it, return false, if there are rows, return true:

Private Function CheckingData() As Boolean
    Dim con As New System.Data.SqlClient.SqlConnection("Data      Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
    Dim sqls As String = "Select * from album where album_id='" + txtAID.Text & "' and quantity_available < 1 "
    Dim cmd As New System.Data.SqlClient.SqlCommand(sqls, con)
    Dim table As New DataTable()
    Dim ds As New System.Data.SqlClient.SqlDataAdapter()
    da.Fill(table)
    If table.Rows.Count > 0 Then
        Return True
    Else
        Return False
    End If
End Function

Not to forget: to show messageBox, but show it in the other method, not here:

Private Sub MyMethod()
    Dim bChecking As Boolean = CheckingData()
    'call method which will check for data existance
    If bChecking Then
        MessageBox.Show("Data exists")
    Else
        MessageBox.Show("Album is out of stock")
    End If
End Sub

Edited 4 Years Ago by Mitja Bonca: added a method to call form the other method

Comments
Same method that I use. Good job.

getting this error by da.Fill(table):

'The SelectCommand property has not been initialized before calling 'Fill'.'
What does it mean?

darthswift00 - I guess for select query u should write ExecuteQuery() and not ExecuteNonQuery()

This article has been dead for over six months. Start a new discussion instead.