Using datagrid values as query parameters
Below is a screen dump of an application I'm developing in VB6. The application is to be used by users to clear their logins from databases on our payroll system. They select one or more records from the datagrid and then click the 'Clear User' button to remove them. The data is populated in the datagrid but I have rubbed it out for security reasons.
http://img.photobucket.com/albums/v134/andyl2002/work/TS_activeusers.jpg
The datagrid control is bound to an ADODC control, which is not showing on the form. In the ADODC data source, I have set up a query showing the userid, username and database name of users who are logged into a database. In that query I filtered a field called 'userloggedin' where it equals 'Y', which returns all the users who are active.
What I'm now concerned with is the code behind the command button 'Clear User'. I need to allow a user to select one or more rows from the datagrid and then click that button so it will clear that users logins from the database(s).
Basically, the query that will used for the button will be something like the following:
UPDATE 'data source name' SET userloggedin =' '
WHERE databasename='parameter1'
AND userid = 'parameter2'
What I really need to know is how to modify this so it actually looks at the ADODC data source and uses the selected records from the datagrid to get its parameter values.
[1517 byte] By [
andy78] at [2007-11-11 9:53:41]
# 1 Re: Using datagrid values as query parameters
I can't use the existing ADODBC control (data source) on the form, so I'm setting up code for a new connection. I've been playing around with the code for the button in VB6 but am having no joy. Below is the syntax I am using again:
Private Sub Clear_login_Click()
Dim Clear_login_msg As Integer
Dim User_cleared_msg As Variant
Dim Conn1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs1 As New ADODB.Recordset
Dim SQL_Connect As String
SQL_Connect = "Provider=SQLOLEDB.1;" & _
"Password=******;" & _
"Persist Security Info=True;" & _
"User ID=*******;" & _
"Initial Catalog=********;" & _
"Data Source=**********;"
Clear_login_msg = MsgBox("Do you wish to clear the user?", vbYesNo + vbExclamation, "")
If Clear_login_msg = 6 Then
Conn1.ConnectionString = SQL_Connect
Conn1.Open
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "UPDATE TSSecurity.dbo.ts_usersec SET userloggedin = '';" & _
"Where TSSecurity.dbo.ts_user.userid = TSSecurity.dbo.ts_usersec.userid
AND TSSecurity.dbo.ts_usersec.userloggedin = 'Y'
AND (TSSecurity.dbo.ts_usersec.DatabaseName= Active_users.datagrid1.DatabaseName.row
AND TSSecurity.dbo.ts_usersec.userid = Active_users.DataGrid1.userid.Row) ;"
Set Rs1 = Cmd1.Execute
User_cleared_msg = MsgBox("The user has been cleared", vbOKOnly, "")
Rs1.Close
Conn1.Close
Conn1.ConnectionString = " "
Else
End If
When I compile it, the problem seems to be within the code which is emboldened. This is the actual query that is supposed to pick up data selected from the rows in the datagrid. First of all, it has an UPDATE statement which is changing the userloggedin value to ' ', so they are not active anymore.
In the WHERE part, it then joins the 2 tables containing the data together using the userid field. It then looks for active users by filtering where the userloggedin field equals Y.
The last thing it is attempting to do is take the values of selected records in the datagrid. For example, with AND (TSSecurity.dbo.ts_usersec.DatabaseName= Active_users.datagrid1.DatabaseName.row , it is referring to the datagrid first through the form name Active_users, the datagrid name Datagrid1 the field name Database name and the currently selected record by using the property row. I have done the same thing for the userid field as well.
I would be grateful if anybody could give me any suggestion as to where I'm going wrong.
andy78 at 2007-11-11 17:23:34 >
# 2 Re: Using datagrid values as query parameters
The problem is that ADO doesn't know anything about the controls on your form. You must get the values of the selected items from the ADODC's underlying recordset and insert the values into the SQL command. Try something like this:
Dim item As Variant
Dim DatabaseName As String
Dim UserID As String
Dim SQL As String
SQL = "UPDATE TSSecurity.dbo.ts_usersec SET userloggedin = '';" & _
"WHERE TSSecurity.dbo.ts_user.userid = TSSecurity.dbo.ts_usersec.userid " & _
"AND TSSecurity.dbo.ts_usersec.userloggedin = 'Y' "
For Each item In DataGrid1.SelBookmarks
With AdoDC1.Recordset
.Bookmark = item
DatabaseName = !DatabaseName
UserID = !UserID
cmd1.CommandText = SQL & _
"AND TSSecurity.dbo.ts_usersec.DatabaseName = '" & DatabaseName & _
"' AND TSSecurity.dbo.ts_usersec.userid = '" & UserID & "'"
cmd1.Execute
End With
Next
