Database help: I keep getting errors on my string..

[unborn]

Trying to grab the contract date out of 1 table and then use it in part of another string for a different table. I keep getting errors though on both when i run it. "data type mismatch in criteria expression" and a few others when i mess around. Can you please tell me what im doing wrong? Thanks!

'On Error GoTo flxContracts_Click_Error
Dim ContID As Long
Dim CompID As Long
Dim mCn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim nRs As ADODB.Recordset
Dim sSql As String
Dim nSql As String
Dim strNote As String

Set mCn = New ADODB.Connection
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\clients.mdb"

CompID = flxContracts.Text
Set nRs = New ADODB.Recordset
nSql = "SELECT ContractID FROM Contracts WHERE CustomerID = " & txtAcctID & " AND ComputerID = " & CompID [b]nRs.Open nSql, mCn, adOpenForwardOnly, adLockOptimistic[/b] [COLOR=green]'this errors out[/color]

While Not nRs.EOF
    ContID = GetString(nRs.Fields("ContractID"))
Wend

nRs.Close


'(flxSearch.MouseRow)
sSql = "SELECT NoteDate, Notes FROM Notes WHERE CustomerID = " & txtAcctID & " AND ContractID = " & ContID & " AND CategoryID = 002"



Set Rs = New ADODB.Recordset
[b]Rs.Open sSql, mCn, adOpenKeyset, adLockOptimistic, adCmdTable[/b] [COLOR=green]'this errors out[/color]

Rs.MoveFirst
While Not Rs.EOF
    strNote = Format(GetString(Rs.Fields("NoteDate").Value), "mm/dd/yy") & ": " & GetString(Rs.Fields("Notes").Value) & "\n" & strNote
    Rs.MoveNext
Wend

lstVisits = strNote

Rs.Close
mCn.Close


  ' On Error GoTo 0
  ' Exit Sub

'flxContracts_Click_Error:

   ' MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure flxContracts_Click of Form frmCLient"

I messed with it changing and adding removing thigns and i cant get it to work i made sure info IS in my tables nothing is missing its just pulling #'s and nothing is working. Any help would be much appreciated!! Thanks!

Running in circles is what I do best!

 

[shakespeare5677]

What are the errors that you are getting?
-Max

 

[unborn]

data type mismatch in criteria expression"

Is the current one im getting. with that code you see above. i made sure all the data matches as far as number and text and what not in my database. i did use the ' ' because i foudn that it wont work with numbers if you use them so i left them out but i have tried with them and still get error.

Running in circles is what I do best!

 

[jsteel]

try running the command with parameters.

nSql = "SELECT ContractID FROM Contracts WHERE CustomerID = ?" & txtAcctID & " AND ComputerID = ?" & CompID

Then pass the values when you run the command. I think you need to use a command objectd to use this method, you cant just open it directly from the resultset.

 

[jsteel]

sorry, meant like this

nSql = "SELECT ContractID FROM Contracts WHERE CustomerID = ? AND ComputerID = ?"

 

[bboffin]

First I presume that your code should read:

.
.
nSql = "SELECT ContractID FROM Contracts WHERE CustomerID = " & txtAcctID & " AND ComputerID = " & CompID 
nRs.Open nSql, mCn, adOpenForwardOnly, adLockOptimistic 'this errors out
.
.

The error is most likely that CustomerID or Computer ID or both are defined as text data (varchar, nvarchar, text etc) in the database in which case you need to surround the value with single quotes:

.
.
nSql = "SELECT ContractID FROM Contracts WHERE CustomerID = '" & txtAcctID & "' AND ComputerID = '" & CompID & "'"
.
.

Bob Boffin

 

[unborn]

They were as text but i went back and changed it a while ago and made it a number type. im off to work i will try doing what ya said jsteel when i get home tonight. thanks for the fast responces i hope the solution awaits when i get home!! hehe thanks all!

Running in circles is what I do best!