Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a SQL table, I want several users to be able to query the table based on a parameter passed via a textbox on a form in visual basic and the results displayed in a Crystal Report.

The way I have been doing it is to create another table where the query results are placed (this overwrites any existing data in that table). As you can see this method is not good if multiple users connected at the same time decide to run the same query.

I have tried using views but I have failed to pass parameters to the view from the textbox. I tried temporary tables but the challenge with them is that I cannot pass the temporary table to Crystal Report.

I am requesting any help on how to achieve this.

Any sample code to help me is highly appreciated.

What I have tried:

Private Sub SelectViewClientInvoices()
    myconnection.Open()
    mycommand = New SqlCommand("Select MacAddress,InvoiceNo,ClientNo,ClientName,TelephoneNo,EmailAddress,PhysicalAddress,LoggedInUser FROM View_ClientSelectedInvoices WHERE ClientNo = '" & TxtClientNo.Text & "' and InvoiceNo = '" & TxtInvoiceNo.Text & "' ", myconnection)
    mycommand.ExecuteNonQuery()
    myconnection.Close()
End Sub


The above code is how I am trying to achieve it using a view. My assumption is that every time that code is run, it pulls all records from the view that match the value in the two textboxes. unfortunately I do not get the desired results.
Posted
Updated 24-Aug-20 21:41pm
Comments
Sandeep Mewara 25-Aug-20 3:39am    
Explain: "unfortunately I do not get the desired results."
sazmuelz 25-Aug-20 4:35am    
The view still pulls all data from the table instead of only the data related to the value passed into the textbox
Garth J Lancaster 25-Aug-20 3:50am    
In addition to everyone else's comments "As you can see this method is not good if multiple users connected at the same time decide to run the same query." .. that's precisely what a database is supposed to do - allow multiple users to access data .. if they are running the same query, then let the database deal with the caching etc.

I'm not sure what your issue with that is, except as OriginalGriff points out below as a solution / fix to your select code.

If your problem is that all users must use the same parameters for the query, that's a completely separate issue - you might need to use a control-table or such to store the parameters for a run, but how you update it/specify a duration in which a set of parameters is valid/the set to use will get 'interesting'
sazmuelz 25-Aug-20 4:37am    
Not all users are to use the same parameters for the query. And yes the method is not good for multiple users connected at the same time. I needed help with a solution to cater for the multiple concurrent connections.

1 solution

First off, don;t do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Secondly, a SELECT command is a query - so using ExecuteNonQuery will not work at all - you need to use a DataAdapter or DataReader instead:
VB
Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("SELECT iD, description FROM myTable", con)
		Using reader As SqlDataReader = cmd.ExecuteReader()
			While reader.Read()
				Dim id__1 As Integer = CInt(reader("iD"))
				Dim desc As String = DirectCast(reader("description"), String)
				Console.WriteLine("ID: {0}" & vbLf & "    {1}", iD, desc)
			End While
		End Using
	End Using
End Using

VB
Using con As SqlConnection = New SqlConnection(strConnect)
    con.Open()

    Using da As SqlDataAdapter = New SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con)
        da.SelectCommand.Parameters.AddWithValue("@SEARCH", myTextBox.Text)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        myDataGridView.DataSource = dt
    End Using
End Using
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900