0
  Private Sub MetroButton1_Click(sender As Object, e As EventArgs) Handles MetroButton1.Click
    If MetroTextBox1.Text <> "" And MetroTextBox2.Text <> "" And MetroComboBox1.SelectedItem <> "" And MetroComboBox2.SelectedItem <> "" Then
        conn.Open()
        Label6.Text = Val(NumericUpDown1.Value)
        Dim cmd As New SqlCommand("select * from books where bname=@book and bpub=@pub and byear=@year and btype=@type", conn)
        cmd.Parameters.AddWithValue("@book", MetroTextBox1.Text)
        cmd.Parameters.AddWithValue("@pub", MetroTextBox2.Text)
        cmd.Parameters.AddWithValue("@year", Label6.Text)
        cmd.Parameters.AddWithValue("@type", MetroComboBox1.Text)
        read = cmd.ExecuteReader
        If read.HasRows Then
            MsgBox("Add New Books or anothe published copy of it!", MsgBoxStyle.Exclamation, "The Same Book Already Exist!")
        Else
            read.Close()
            comm.CommandText = "insert into [books] values('" + MetroTextBox1.Text + "','" + MetroTextBox2.Text + "','" + Label6.Text + "','" + MetroComboBox1.SelectedItem + "','" + MetroComboBox2.SelectedItem + "') "
            comm.ExecuteNonQuery()
            conn.Close()
        End If
    ElseIf MetroTextBox1.Text = "" And MetroTextBox2.Text = "" And MetroComboBox1.SelectedItem = "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider1.SetError(MetroTextBox1, "Requierd value is missing")
        ErrorProvider2.SetError(MetroTextBox2, "Requierd value is missing")
        ErrorProvider3.SetError(MetroComboBox1, "Requierd value is missing")
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text <> "" And MetroTextBox2.Text = "" And MetroComboBox1.SelectedItem = "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider2.SetError(MetroTextBox2, "Requierd value is missing")
        ErrorProvider3.SetError(MetroComboBox1, "Requierd value is missing")
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text <> "" And MetroTextBox2.Text <> "" And MetroComboBox1.SelectedItem = "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider3.SetError(MetroComboBox1, "Requierd value is missing")
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text <> "" And MetroTextBox2.Text <> "" And MetroComboBox1.SelectedItem <> "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text = "" And MetroTextBox2.Text <> "" And MetroComboBox1.SelectedItem = "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        ErrorProvider1.SetError(MetroTextBox1, "Requierd value is missing")
        ErrorProvider3.SetError(MetroComboBox1, "Requierd value is missing")
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text = "" And MetroTextBox2.Text = "" And MetroComboBox1.SelectedItem <> "" And MetroComboBox2.SelectedItem = "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider1.SetError(MetroTextBox1, "Requierd value is missing")
        ErrorProvider2.SetError(MetroTextBox2, "Requierd value is missing")
        ErrorProvider4.SetError(MetroComboBox2, "Requierd value is missing")
    ElseIf MetroTextBox1.Text = "" And MetroTextBox2.Text = "" And MetroComboBox1.SelectedItem = "" And MetroComboBox2.SelectedItem <> "" Then
        sound.Play()
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
        ErrorProvider1.SetError(MetroTextBox1, "Requierd value is missing")
        ErrorProvider2.SetError(MetroTextBox2, "Requierd value is missing")
        ErrorProvider3.SetError(MetroComboBox1, "Requierd value is missing")
    Else
        sound.Play()
        ErrorProvider1.SetError(MetroTile1, "Requierd value is missing")
        MetroTile1.Text = "Requierd value is missing"
        MetroTile1.ForeColor = Color.Red
    End If
    conn.Close()
    loadlb()
End Sub

I'm new to vb.net I always get an error in SqlConnection when I execute reader I always pass the connection to it but I keep saying connection need to be open first I can't understand how it work I usually work with mysql so if anyone of you can tell me what is going on I'll be thankful.

And my second question is the validation of user input I need fast way to check user input in each field with out writing all these line of codes

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arvin Sloane
  • 11
  • 6
  • It looks like you are using the same connection over and over. A new connection should be used each time. You didn't say *what* the error was, so not much more can be said. Oh, and you spelled "Required" wrong everywhere. – Ňɏssa Pøngjǣrdenlarp Jan 24 '15 at 15:25
  • Where do you define and initialize the conn variable that represent your connection object? This code, in case of exceptions leaves the connection open. – Steve Jan 24 '15 at 15:26
  • i define my conn var in the beginning of my class with the data source and am not using it over i just want to know if i opened my connection do i need to pass the conn var to the sqlcommand – Arvin Sloane Jan 24 '15 at 15:34
  • @Plutonix this is the error "Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed." – Arvin Sloane Jan 24 '15 at 15:37
  • If you define it once, you *are* using it over and over. You can create a helper function to create a new one each time so that the connection string is not repeated over and over, but the connection *object* should be created for each use and then closed and disposed. That long procedure could be simplified if all the data validation happened *before* you try start in on db ops. – Ňɏssa Pøngjǣrdenlarp Jan 24 '15 at 15:37
  • .... *then* look into SQL Parameters to simplify the SQL/db access and prevent SQL injection attacks: http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i – Ňɏssa Pøngjǣrdenlarp Jan 24 '15 at 15:48
  • @Plutonix thank you sir v.much this statement make my day " That long procedure could be simplified if all the data validation happened before you try start in on db ops" i couldn't think of that i'll adjust my code now thank you . and i found that the error caused by the loadlb function i closed the conn then i call it and there is db ops happening there – Arvin Sloane Jan 24 '15 at 15:51
  • This will happen again and again if you don't follow the good pattern to keep a local object for your connection. Remember that you gain nothing keeping a connection object. ADO.NET, and Sql Server in particular, support the [Connection Pooling](https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx) – Steve Jan 24 '15 at 15:57

0 Answers0