SQL Upgrade killed my Query

[Reynet01]

I have the following query that worked on access databse with numeric field when we upgraded to SQL the field are now int fields and my query does not work properly any more below is the recordset. any ideas on how to fix it?

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_RS1_STRING
Recordset1.Source = "SELECT state, JobNumber, DictatorName, DictatorID, TranID, dtComplete, dtAtTran, JobLength, lExtraStatus, iPriorityLevel, TranName, SubjectName FROM Dictations WHERE DictatorName like '" + Replace(Recordset1__vardictator, "'", "''") + "' AND state like '" + Replace(Recordset1__varradio, "'", "''") + "' and TranName like '" + Replace(Recordset1__vartranName, "'", "''") + "' and jobnumber like '" + Replace(Recordset1__jbnumber, "'", "''") + "' ORDER BY jobnumber ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

 

[onpnt]

SQL ?

Is that MySQL or SQL Server

Have you tested teh outputted SQL statement in the new DB analyzer?

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811

 

[amorous]

Try this:

Recordset1.Source = "SELECT state, JobNumber, DictatorName, DictatorID, TranID, dtComplete, dtAtTran, JobLength, lExtraStatus, iPriorityLevel, TranName, SubjectName  FROM Dictations  WHERE DictatorName like '" & Replace(Recordset1__vardictator, "'", "''") & "' AND state like '" & Replace(Recordset1__varradio, "'", "''") & "' AND TranName like '" & Replace(Recordset1__vartranName, "'", "''") & "'  AND jobnumber like '" & Replace(Recordset1__jbnumber, "'", "''") & "'  ORDER BY jobnumber ASC"

-VJ

 

[mwolf00]

state" may be a reserved word - try this.

Recordset1.Source = "SELECT [state], JobNumber, DictatorName, DictatorID, TranID, dtComplete, dtAtTran, JobLength, lExtraStatus, iPriorityLevel, TranName, SubjectName FROM Dictations WHERE DictatorName like '" + Replace(Recordset1__vardictator, "'", "''") + "' AND [state] like '" + Replace(Recordset1__varradio, "'", "''") + "' and TranName like '" + Replace(Recordset1__vartranName, "'", "''") + "' and jobnumber like '" + Replace(Recordset1__jbnumber, "'", "''") + "' ORDER BY jobnumber ASC"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[Reynet01]

it is MSDE 2000 with DBAMKR2k as the management interface. the funny thing is I get the records that were imported to the db from access but any new records don't show

 

[Reynet01]

mwolf00 state is one of the fields in the table it is a numeric value that shows what level the job is at

 

[Reynet01]

amorous
I tried your recordset changes and came up with the same results..... I LOVE UPGRADES!!!!!!