Error while getting data from server via sql

[baha17]

Good Morning Dear Masters,

I created a code to get data from the server. It worked very well over a year. However, somehow one of the criteria has been changed about "refused name(no name)" customers. Before we used to assign customer number for them automatically, but with the new changes the system does not assign any customer number customer numbers for those customer instead it gives them "R/N" code. Therefore now I have some problem extracting data from server. I tried to apply some of those "by-pass techniques that I found on net but they did not work for me (talking about CAST).

case WHEN isnumeric([CustId]) THEN CAST([CustId] AS int) else 0 end

I get this error:

Run-time error ' -2147217913 (80040e07)
[Microsoft] [ODBC SQL Server Driver][SQL Server] Conversation failed
when converting the nvarchar value 'R/N'to data type int.

All I need is disregard those "R/N" in customer id field. Please have a look at my vba code below.
Thank you in advance
Baha

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim cel As Range
Dim TD As Double
Dim TD2 As Double
Dim qdate As Double
Dim qdate2 As Double
Dim lastrow As Long
Dim custid 'As String+


TD = DateSerial(Year(Now()), Month(Now()), 1)
TD2 = Target.Offset(-4, -2).Value
custid = Range("custid4").Text
    
    Target.Cells(1, 1).Select
    Selection.Activate
    Set TargetRange = Range("custid4").Offset(2, 0)
    Set Conn = New ADODB.Connection
    
    Conn.Open "driver={SQL Server};" & _
   "server=XXXX01\RPTDB;database=Reporting_ODS;"
    
    Set RecSet = New Recordset
    RecSet.Open "SELECT TOP(1)Baha_PM.Lname +' '+ Baha_PM.Fname FROM Reporting_ODS.TG.Baha_PM Baha_PM " & _
    "WHERE Baha_PM.CustId='" & custid & "'", Conn, , , adCmdText
    TargetRange.CopyFromRecordset RecSet

    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
' Trip

    Set TargetRange = Range("custid4").Offset(14, 0)
    Set Conn = New ADODB.Connection
    
    Conn.Open "driver={SQL Server};" & _
   "server=XXXX01\RPTDB;database=Reporting_ODS;"
     Set RecSet = New Recordset
   
    RecSet.Open "SELECT MAX(Baha_PM.Trip_Id)  FROM Reporting_ODS.TG.Baha_PM Baha_PM " & _
    "WHERE Baha_PM.CustId='" & custid & "'", Conn, , , adCmdText
    TargetRange.CopyFromRecordset RecSet
    
    
    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
    Set Conn = Nothing

End Sub

 

[mole999]

you'll need to modify this

"WHERE Baha_PM.CustId='" & custid & "'", Conn, , , adCmdText

to incorporate

something <> "R/N"

 

[baha17]

Hi Mole
Thanks for the input.That was the first thing I tried but unfortunately didn't work.

 

[mole999]

does the code point to the table and field you expect, is R/N stored there on its own

 

[baha17]

does the code point to the table and field you expect, is R/N stored there on its own[/QUOTE
Yes Mole definitely does.This code works well before.The system automatically generates 110 for refused name customer. Now that 110 changed to "R/N" since that time I got the error. But what I don't understand is,in my code I look for

custid='"custid'"

there is no relation between "R/N" with my custid.the reason I do that is to get the name of the patron.
I tried to put

[FONT=Helvetica Neue]
[/FONT][COLOR=#00008B][FONT=Consolas]case[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] WHEN isnumeric[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]CustId[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]]+[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]'E+00'[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])=[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]1[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] THEN CAST[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([[/FONT][/COLOR][COLOR=#2B91AF][FONT=Consolas]CustId[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]][/FONT][/COLOR][COLOR=#000000][FONT=Consolas] AS [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]int[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]else[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]0[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]end[/FONT][/COLOR]

but didn't work too
thanks for the help
Baha

 

[mole999]

probably a variable issue, number to text causing a problem, if you put it back to 110, will it run again

not sure about access anymore but in SQL i would be doing

Case WHEN CustId = 110 THEN "R/N"
else "NULL"
end

 

[baha17]

thanks Mole, i will try that on monday and let you the result
cheers

 

[baha17]

Hi Mole,
I change "Range("custid4").Text" to "Range("custid4").Value" but still get the same result. In addition,

Case WHEN CustId= 110 THEN "R/N" 
else "NULL"
end

does not match my intention,(with all due my respect)
I always key in the number on CustId but the problem is when it checks Top(1) from the server, if there is any non integer data in that field, it return error. Lets put it this way;
if in CustId field in the server all data store as number(int) then my code works. But some days, if there is non-number data in the field which is usually "R/N" my code generates error. Actually it does not make sense to me because as in my sql statement, it should only get TOP(1) record from the server when it is equal to "custid"
Am I missing something out?
Thanks for the help
Baha

 

[baha17]

Hi Mole
after a while we realized that there was a defect in the database that was why it did not work. Thank you for the help...