Populating a textbox with SQL query result

Hi,

I'm creating an app, that gets a ID number from an object that a user selects. This ID is then displayed in a textbox that cannot be edited.

I now require that varible (ID passed) in the text box to be compared with a column in a SQL database table which is an identical number. Once the matching number is found I need to return the value that is shown in another column in the same table, and populate another textbox. This second text box I want the user to be able to update and change which populates the database.

So really I need a query that passes the variable to the database, (a where clause) and returns another number.

This is what I have so far:


'***********************
'This is where is created the db connection
'Change this to match your Dbase location
'***********************
Dim dbConn As New ADODB.Connection
Dim dbRst As New ADODB.Recordset

dbConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=my_database;Data Source=SQL_database"


'***********************
'This is where it 'opens' the Table in the dbase connection
'Currently opening the "test_table" table, in the sql server
'Change this to match your table name.
'***********************
dbRst.Open "test_table", dbConn, adOpenKeyset, adLockOptimistic, adCmdTable

'odValue is the variable passed. (int)


strSQL = "SELECT * FROM [test_table] WHERE [MS_LINK]=odValue;"

UserForm1.TextBox2.Value = CStr(odValue.Value)

UserForm1.Show

Here's more code to understand some more of what I'm attempting:

For Each addObj In objSSet
msg = ""
' get the selected object data table and iterator

Set odTables = amap.Projects(ThisDrawing).odTables

For i = 0 To odTables.count - 1
Set odTable = odTables.Item(i)
Set RecordIterator = odTables.GetODRecords
Next

' initilize iterator on entity
If RecordIterator.Init(addObj, False, False) = True Then

'' oops, that entity has no od attached to specified table
If RecordIterator.IsDone Then
msg = msg & "Entity has no data from Object Data Table: " & odTable.Name
End If

'' loop through all attached records from specified table
'Setup a collection to dump the data into.
Dim colDbaseValues As New Collection
Do Until RecordIterator.IsDone
'' get record data
Set odRecord = RecordIterator.Record
'' initialize field counter
count = 0
'' loop through each od field
For Each odValue In odRecord
'' get field name from defs
msg = msg & odTable.ODFieldDefs.Item(count).Name

'' and show field value
Select Case odValue.Type
Case 0, 1, 2, 3
UserForm1.TextBox1.Value = CStr(odValue.Value)
Case 4
UserForm1.TextBox1.Value = CStr(odValue.Value.X) & "," & CStr(odValue.Value.Y) & "," & CStr(odValue.Value.X)
End Select
count = count + 1
colDbaseValues.Add (odValue.Value)
Next

'' there may be more records attached
RecordIterator.Next
Loop

'' error getting iterator
Else
'' report the bad news
msg = "Error iterating Object Data Table: " & odTable.Name
End If

'' update text box
'MsgBox msg
Next

'This is where is calls the other function to populate the database
'Currenlty used in conjunction with function AddODToDbase

AddODToDbase colDbaseValues

'objSSet.Delete
Set amap = Nothing
Set acadApp = Nothing


UserForm1.Show

End Sub

Public Function AddODToDbase(colDbaseValues As Collection)

'***********************
'This is where is created the db connection
'Change this to match your Dbase location
'***********************
Dim dbConn As New ADODB.Connection
Dim dbRst As New ADODB.Recordset

dbConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=SQL"

'***********************
'This is where it 'opens' the Table in the dbase connection
'Currently opening the Municipal_Bdy table, in the gis_parcel sql server
'Change this to match your table name.
'***********************
dbRst.Open "table_test", dbConn, adOpenKeyset, adLockOptimistic, adCmdTable


'odValue is the variable passed. (int)


strSQL = "SELECT * FROM [test_table] WHERE [MS_LINK]=odValue;"

UserForm1.TextBox2.Value = CStr(odValue.Value)



dbRst.Close
dbConn.Close

Set dbRst = Nothing
Set dbConn = Nothing

End Function

Hi,

I must say the code extracts you gave are a bit confusing. As far as I can see, all you want to do is make a data-entry form that enables a user to select a certain record from the database and update a value accordingly. If that is the case, you have way to much code. In the following code excerpt there is a form with two textboxes (one for the "lookup" value txtID and one for the "editable" value txtMSLink) and two command buttons (one to look a record up cmdLookup, and one to update the record cmdUpdate). I have made a few assumptions, you know how to program in VB and know some things about databases. I did not include comments on what it all does, because it is pretty obvious.

Now, the code:

Option Explicit
Private m_cnDatabase As ADODB.Connection
Private m_rsData As ADODB.Recordset
Private Sub cmdLookup_Click()
Dim sSql As String
' ----------------------
On Error GoTo PROC_ERR
sSql = "Select * from TEST_TABLE where MS_LINK = " & txtID.Text

Set m_rsData = New ADODB.Recordset
With m_rsData
.ActiveConnection = m_cnDatabase
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Source = sSql

.Open

If (Not .BOF) And (Not .EOF) Then
txtMSLink.Text = .Fields("YOUR_FIELD").Value
End If
End With
PROC_ERR:
Select Case Err.Number
Case Is <> 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdLookup_Click of Form Form1"
Err.Clear
End Select
End Sub
Private Sub cmdUpdate_Click()
On Error GoTo PROC_ERR
If (Not m_rsData Is Nothing) Then
With m_rsData
If (.State = adStateOpen) Then
.Fields("UPDATE_FIELD").Value = txtMSLink.Text
.Update
Else
MsgBox "The recordset is not opened.", _
vbOKOnly + vbInformation, _
"Sample - Error condition"
End If
End With
Else
MsgBox "First lookup a value, before trying to update the database", _
vbOKOnly + vbInformation, _
"Sample - Error condition"
End If
PROC_ERR:
Select Case Err.Number
Case Is <> 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click of Form Form1"
Err.Clear
End Select
End Sub
Private Sub Form_Load()
On Error GoTo PROC_ERR
Set m_cnDatabase = New ADODB.Connection

With m_cnDatabase
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=my_database;Data Source=SQL_database"
.CursorLocation = adUseClient
.Open
End With
PROC_ERR:
Select Case Err.Number
Case Is <> 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of Form Form1"
Err.Clear
End Select
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo PROC_ERR
If (Not m_rsData Is Nothing) Then
With m_rsData
If (.State <> adStateClosed) Then
.Close
End If
End With
End If
If (Not m_cnDatabase Is Nothing) Then
With m_cnDatabase
If (.State <> adStateClosed) Then
.Close
End If
End With
End If
PROC_EXIT:
'
' Clean up and exit gracefully.
'
Set m_cnDatabase = Nothing
Set m_rsData = Nothing
PROC_ERR:
Select Case Err.Number
Case Is <> 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Unload of Form Form1"
Err.Clear
Resume PROC_EXIT
End Select
End Sub

If you have any questions, or I completely failed to understand your situation, feel free to ask questions.

Grtz.©

M.

First of all, thank you very much for the help. You have come very close to what I need. I will try and incorrperate the code you have supplied. The portion of the code I supplied must be used, as the user must select objects from onscreen, which is what that code accomplishes. There is one particular peice of the code that is vitally important shown below:

'' loop through all attached records from specified table
'Setup a collection to dump the data into.
Dim colDbaseValues As New Collection
Do Until RecordIterator.IsDone
'' get record data
Set odRecord = RecordIterator.Record
'' initialize field counter
count = 0
'' loop through each od field
For Each odValue In odRecord
'' get field name from defs
msg = msg & odTable.ODFieldDefs.Item(count).Name

'' and show field value
Select Case odValue.Type
Case 0, 1, 2, 3
UserForm1.TextBox1.Value = CStr(odValue.Value)
Case 4
UserForm1.TextBox1.Value = CStr(odValue.Value.X) & "," & CStr(odValue.Value.Y) & "," & CStr(odValue.Value.X)
End Select
count = count + 1
colDbaseValues.Add (odValue.Value)
Next

'' there may be more records attached
RecordIterator.Next
Loop

The value sorted in that collection (colDbaseValues) more specifically the value (odValue.Value) is was is needed to compare in the database.

I'm going to use what you have given, and see if I can get it going.

Thanks again, and if I need any further help, I'll be sure to post.

After putting it together, I think I can modify what you have supplied. Thanks again. I have ran into an error. I'm not the VBA expert that you seem to be, so I'll run this error past you. I've checked the permissions agains't the database, and the table itself. That seems to be ok


There error I got was part of your error handling code in the cmdLookup_Click() function:

Error 3709(The connection cannot be used to perform this operation. It is either closed or invalid in this context) in procedure cmdLookup_Click of Form Form1


By the way, very impressive error handling you have incorperated with this app.

Hi,

The error you get says that the database connection is not open. If you check that, it should be ok, does the piece where the connection is opened fire when you run the code?

And to access an item in the colDBasevalues you can use the .Items property with either a key or a number:



That should get you on the way.

Grtz.©

M.

Hmm, I'm not sure. It seems like it's firing, just not activating the connection.