Dynamic SQL Search in VB 2005

[reverend]

I am looking for some direction on building a dynamic query connecting to SQL Express. Everything I am finding online seems to be for ADO.NET and ASP.NET. This is just a simple form that searches a local SQL Express database. I am basically trying to pull info from the database using a textbox, combobox and button. The textbox represents the search string 'keyword', the combobox represents what column to search, and of course the button initiates the search.

Thank you in advance for any and all advice or samples.

 

[Stonkie]

You can use an SqlCommand object to run a query on the database without using a dataset. You simply set the connection, CommandText and optionnaly the parameters, run the Execute method that fits the task (ExecuteReader, ExecuteScalar, ExecuteNonQuery, etc) and you're done!

You can concatenate a big string to create the SQL or parametrize the variable part of the query and set the parameters on the SqlCommand object. Depending on the query's complexity, you're the one who knows, but if you can parametrize the query, you can isolate it in a typed dataset and use the ADO.NET tool set to execute it.

 

[reverend]

Thank you so much, I will research the usage of the 'execute' type methods. This is my first SQL related project and I appreciate the input very much

 

[reverend]

Well so much for reading LOL. I am having some real difficulty grasping the concept. Would anyone have a bit of code that reflects this? I do have a valid connection, and my form fills with all the data in the binding navigator etc.

 

[Icecat]

Sorry i Cant posting Code well, & im weak at connection

Private ComputerName As String = System.Net.Dns.GetHostName
    Private DatabaseName As String = "Mapia" 'Change Mapia to your database name
    Private ConnectionString As String = "Data Source=" & ComputerName & "\SQLEXPRESS;Initial Catalog=" & DatabaseName & ";Integrated Security=True"
Private conn As New SqlConnection

 Public Function GetData(ByVal strSQL1 As String) As DataSet
        Dim ds As New DataSet
        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.ConnectionString = ConnectionString
            conn.Open()
            ' Create a data adapter
            Dim da As New SqlDataAdapter(strSQL1, conn)
            ' Create DataSet, fill it and view in data grid

            da.Fill(ds, "Tabel")

        Catch ex As Exception

        End Try
        Return ds
    End Function


 Private Sub Btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Search.Click
       dim ds_Search as dataset
ds_Search = GetData("select * from YourDataBase where " & YourCombobox.text & " = '" & YourTextbox.text & "'")

[COLOR="DarkOrange"]output[/COLOR]
[COLOR="DarkOrange"]if u r using combobox[/COLOR]
         If ds_Search.Tables(0).Rows.Count > 0 Then
                For i As Integer = 0 To ds_Search.Tables(0).Rows.Count - 1
                    isia = ds_Search.Tables(0).Rows(i).Item(0) 'Item number represent which column u r showing
                    Cbo_AssetNo.Items.Add(isia)
                Next
          End If

[COLOR="DarkOrange"]if u r using datagridview[/COLOR]
YourDGV.DataSource = ds_Search.Tables(0)
    End Sub

 

[reverend]

Thank you very much for the example! I will study it's usage so that I actually start to understand exactly what this is doing. As I had stated earlier, I am a complete noob to the usage of VB.net in reference to SQL, and it has kind of been a mystery to me in the usage of dynamically built queries. I cannot thank you enough!!
I had tried something similar to: ds_Search = GetData("select * from YourDataBase where " & YourCombobox.text & " = '" & YourTextbox.text & "'")
but I can see already the verbage I was using was incorrect.

Best regards,

Reverend

 

[reverend]

Ok, this is how I coded this (I know my naming conventions stink):

Private ComputerName As String = System.Net.Dns.GetHostName
    Private DatabaseName As String = "praise.dbo.praise" 'Change Mapia to your database name
    Private ConnectionString As String = "Data Source=ADMINROAM\SQLEXPRESS;Initial Catalog=praise;Integrated Security=True"
    Private conn As New SqlConnection

    Public Function GetData(ByVal strSQL1 As String) As DataSet
        Dim ds As New DataSet
        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.ConnectionString = ConnectionString
            conn.Open()
            ' Create a data adapter
            Dim da As New SqlDataAdapter(strSQL1, conn)
            ' Create DataSet, fill it and view in data grid

            da.Fill(ds, "praise.dbo.praise")

        Catch ex As Exception

        End Try
        Return ds
    End Function

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' If ComboBox1.Text = "Search By..." Then
        '   MsgBox("Please select a Search By... option.")
        ' End If
        ' If TextBox1.Text = "Search For..." Then
        '   MsgBox("Please select a Keyword to search.")
        ' ElseIf TextBox1.Text = "" Then
        '     MsgBox("Please select a Keyword to search.")
        ' End If


        Dim ds_Search As DataSet
        ds_Search = GetData("select * from praise.dbo.praise where " & ComboBox1.Text & " = '" & TextBox1.Text & "'")

        SearchForm.Show()
        SearchForm.DataGridView1.DataSource = ds_Search.Tables(0)

    End Sub

Public Class SearchForm

    Private Sub SearchForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ModelDataSet.Praise' table. You can move, or remove it, as needed.
        Me.PraiseTableAdapter.Fill(Me.ModelDataSet.Praise)


    End Sub

    Private Sub RichTextBox1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub
end class

The datagrid is on a separate form. I get the error "Cannot find table 0". What am I doing wrong?

 

[reverend]

I fianlly got it! I am posting the code as I used it in this instance for anyone in the future who runs into the same issue. Thank you to those who replied and helped

Private ComputerName As String = System.Net.Dns.GetHostName
    Private DatabaseName As String = "praise.dbo" 'Change Mapia to your database name
    Private ConnectionString As String = "Data Source=ADMINROAM\SQLEXPRESS;Initial Catalog=praise;Integrated Security=True"
    Private conn As New SqlClient.SqlConnection

    Public Function GetData(ByVal strSQL1 As String) As DataSet
        Dim ds As New DataSet
        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.ConnectionString = ConnectionString
            conn.Open()
            ' Create a data adapter
            Dim da As New SqlClient.SqlDataAdapter(strSQL1, conn)
            ' Create DataSet, fill it and view in data grid

            da.Fill(ds, "praise.dbo.praise")

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return ds
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds_Search As DataSet
        ds_Search = GetData("select * from praise.dbo.praise where " & ComboBox1.Text & " = '" & TextBox1.Text & "'")

        'output()
        'if u r using combobox
        If ds_Search.Tables(0).Rows.Count > 0 Then
            For i As Integer = 0 To ds_Search.Tables(0).Rows.Count - 1
                i = ds_Search.Tables(0).Rows(i).Item(0) 'Item number represent which column u r showing
                ComboBox1.Items.Add(i)
            Next
        End If

        'if u r using datagridview
        searchform.DataGridView1.DataSource = ds_Search.Tables(0)
    End Sub