none
Write a query with two count conditions RRS feed

  • Question

  • Hi Guys!

    In my VB.Net form I have NameBox and a SurnameBox and they are writing the data to the related columns in my sql server 2012 database with a Save button.

    So how can I write something to the buttonclick event like:

    If NameBox.Text & " " & SurnameBox.Text = Contacts_Table Name_Colum & " " & Contacts_Table Surname_Colum(for the same row) Then

    Messagebox.Show("A person with the same name and surname already exist!")

    End If

    or

    Count of Contacts_Table Name_Colum & " " & Contacts_Table Surname_Colum for the same line(row) returns 1 or more results for the string NameBox.Text & " " & SurnameBox.Text, then, show the warning.

    In many places, they say it can be achieved by writing a query. How can I write this? Any example? Also I read something like VLookUp. How can I use this? Or does it have nothing to do with my question?

    I am sorry for my bad English. I hope, I was able to define what I need.

    Thanks a lot!



    • Edited by Flashbond Tuesday, August 6, 2013 11:45 PM
    Tuesday, August 6, 2013 11:41 PM

Answers

  • I did it like that; Simply I used a filter with two conditions:

    Private Sub SaveToolStripMenuItem_Click(ByVal Sender As System.Object, e As System.EventArgs) Handles SaveToolStripMenuItem.Click
            Add_New_Customer_Form.contact_informationTableAdapter.Fill(Add_New_Customer_Form.myDataSet.contact_information)
            Add_New_Customer_Form.contact_informationBindingSource.Filter = String.Format("FirstName = '" & _
    Add_New_Customer_Form.TextBoxFirstName.Text & "' AND LastName = '" &   Add_New_Customer_Form.TextBoxLastName.Text & "'")
            
            If  Add_New_Customer_Form.contact_informationBindingSource.Count> 0 Then
                Dim result As DialogResult = MessageBox.Show("There is already an existing record with " & _
    "the same name and lastname. You may still continue, however, you can cancel saving " & _
    "in order to re-arrange the information.", "Warning", MessageBoxButtons.OKCancel)
                If result = Windows.Forms.DialogResult.OK Then
                    GoTo REGISTER
                ElseIf result = Windows.Forms.DialogResult.Cancel Then
                    Exit Sub
                End If
            Else
    REGISTER:
                Add_New_Customer_Form.Validate()
                Add_New_Customer_Form.contact_informationBindingSource.EndEdit()
                Add_New_Customer_Form.contact_informationTableAdapter.Insert(Add_New_Customer_Form.TextBoxFirstName.Text, _
                Add_New_Customer_Form.TextBoxLastName.Text, _
                Add_New_Customer_Form.TextBoxAdress.Text, _
                Add_New_Customer_Form.MaskedTextBoxPhoneNumber.Text, _
                Date.Today.ToShortDateString & " " & Date.Now.ToShortTimeString, _
                Date.Today.ToShortDateString & " " & Date.Now.ToShortTimeString)
                MessageBox.Show("Register Successful!")
            End If
        End Sub



    • Marked as answer by Flashbond Wednesday, August 7, 2013 1:14 PM
    • Edited by Flashbond Thursday, August 8, 2013 1:03 AM
    Wednesday, August 7, 2013 1:10 PM

All replies

  • I am not sure if you are asking for the t-sql query or VB.NET code.

    the t-sql query will be like this:

    select count(*) from mytable where firstname='firstname' and lastname='lastname'

    now if you want to implement this through .NET you can use values from input text boxes in this query string


    <b><a href="http://www.radacad.com/CoursePlan.aspx?course=1">SQL Server 2012 Integration Services - Tutorial Videos</a></b><hr /><a href="http://www.rad.pasfu.com">Reza Rad's Technical Blog</a>

    Tuesday, August 6, 2013 11:55 PM
  • Actually I am asking for VB.Net code but in this thread I couldn't get a satisfying reply.

    I don't know how to write sql queries in VB.Net.

    Tuesday, August 6, 2013 11:59 PM
  • this depends on how you connect to SQL Server from your VB.NET code, 

    did you used LINQ? Entity Framework? or you use simple ADO.NET SQLCommand and ExecuteReader or ExecuteScalar method?


    <b><a href="http://www.radacad.com/CoursePlan.aspx?course=1">SQL Server 2012 Integration Services - Tutorial Videos</a></b><hr /><a href="http://www.rad.pasfu.com">Reza Rad's Technical Blog</a>

    Wednesday, August 7, 2013 12:10 AM
  • I added it from data sources with a connection string via add new connection window.
    I choosed Microsoft Sql Server as data source and .Net Framework Data Provider for SQL.

    Edit: I use TableAdapter and BindingSource. I wanted to write this because in most cases they are very helpful. Also all of my columns are indexed if it helps...

    • Edited by Flashbond Wednesday, August 7, 2013 12:40 AM
    Wednesday, August 7, 2013 12:21 AM
  • link below explained how to write a query in VB.NET:

    http://msdn.microsoft.com/en-us/library/dw70f090.aspx


    <a href="http://www.radacad.com/CoursePlan.aspx?course=1">SQL Server 2012 Integration Services - Tutorial Videos</a>

    Wednesday, August 7, 2013 12:39 AM
  • Yeah, I think this link is what I need. But I am too stupid to understand all these. For example I have a TableAdapter. I don't think I haveto write a connection string.
    Wednesday, August 7, 2013 1:28 AM
  • you don't have to write connection string again,

    but in your application files, you will find an APP.config (windows application) or Web.config (web application) file

    in that file there will be a connection string, that you can use it.

    but for now, I suggest you to write connection string in the code, and run the query from code behind as the link explained, then for next step you can read that connection string form config file to be consistent.

    if the link above is confusing for you, here are few lines that run the query

    Dim ConStr As String ""
    2 Dim Query As String "SELECT COUNT(*) FROM view_main where initials='jjj';"
    3 Dim Con As New System.Data.SqlClient.SqlConnection(ConStr)
    4 Con.Open()
    5 Dim Command As New System.Data.SqlClient.SqlCommand(Query, Con)
    6 Dim Count As Integer = Command.ExecuteScalar()
    7 Con.Close()

    Source:

    http://www.dreamincode.net/forums/topic/99724-count-records-in-query/


    [url=http://www.radacad.com/CoursePlan.aspx?course=1]SQL Server 2012 Integration Services - Tutorial Videos[/url]

    Wednesday, August 7, 2013 1:34 AM
  • I did it like that; Simply I used a filter with two conditions:

    Private Sub SaveToolStripMenuItem_Click(ByVal Sender As System.Object, e As System.EventArgs) Handles SaveToolStripMenuItem.Click
            Add_New_Customer_Form.contact_informationTableAdapter.Fill(Add_New_Customer_Form.myDataSet.contact_information)
            Add_New_Customer_Form.contact_informationBindingSource.Filter = String.Format("FirstName = '" & _
    Add_New_Customer_Form.TextBoxFirstName.Text & "' AND LastName = '" &   Add_New_Customer_Form.TextBoxLastName.Text & "'")
            
            If  Add_New_Customer_Form.contact_informationBindingSource.Count> 0 Then
                Dim result As DialogResult = MessageBox.Show("There is already an existing record with " & _
    "the same name and lastname. You may still continue, however, you can cancel saving " & _
    "in order to re-arrange the information.", "Warning", MessageBoxButtons.OKCancel)
                If result = Windows.Forms.DialogResult.OK Then
                    GoTo REGISTER
                ElseIf result = Windows.Forms.DialogResult.Cancel Then
                    Exit Sub
                End If
            Else
    REGISTER:
                Add_New_Customer_Form.Validate()
                Add_New_Customer_Form.contact_informationBindingSource.EndEdit()
                Add_New_Customer_Form.contact_informationTableAdapter.Insert(Add_New_Customer_Form.TextBoxFirstName.Text, _
                Add_New_Customer_Form.TextBoxLastName.Text, _
                Add_New_Customer_Form.TextBoxAdress.Text, _
                Add_New_Customer_Form.MaskedTextBoxPhoneNumber.Text, _
                Date.Today.ToShortDateString & " " & Date.Now.ToShortTimeString, _
                Date.Today.ToShortDateString & " " & Date.Now.ToShortTimeString)
                MessageBox.Show("Register Successful!")
            End If
        End Sub



    • Marked as answer by Flashbond Wednesday, August 7, 2013 1:14 PM
    • Edited by Flashbond Thursday, August 8, 2013 1:03 AM
    Wednesday, August 7, 2013 1:10 PM