error while getting report

[BartCl]

Hi,

When i try to run the following report i get the error message "Error converting data type varchar to numeric"

this is the querry i try to run.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tsysIPLocations
Where tblAssetCustom.State = 1 And tsysIPLocations.StartIP = '10.136.112.1' And
tsysIPLocations.EndIP = '10.136.113.255'

Can anybody help me with this issue?

Thank you.

[BartCl]

Klas,

thank you for your help, seemd to be working now.

Kind regards

[Klas]

remove the comma after tblAssets.Assettype - should look like:

".. Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= .."

[BartCl]

Hi Klas,

I just made the changes like the following and I get a syntax error:

Select Top 100 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.Realstart,
tsysIPLocations.Realend
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= tsysIPLocations.Realend
Where tsysIPLocations.IPLocation= 'Namen' And tblAssetCustom.State = 1


Sorry for the inconvienence but my sql isn't that good 😉

[Klas · Accepted solution]

you have to join the table "tsysIPLocations" properly. Change this part: "...= tblAssets.Assettype,
tsysIPLocations..."
to = "... =tblAssets.Assettype
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= tsysIPLocations.Realend

you don't have to; but this done, you can then change your where-clause; You probably given all your different ip-ranges a name? Let's say yours (10.136.112. -- 10.136.113.255) is called "BartsIPlocation". Then change it to "... Where tsysIPLocations.IPLocation= 'BartsIPlocation' And tblAssetCustom.State = 1 "

[BartCl]

Hi Klas,

Thank you seems to solve my problem 😉

but when I run this query I still get al the machines in the network and not only the one between the ip ranges?

Do you have any idea what causes this.

I adjusted the query into:

Select Top 100 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.Realstart,
tsysIPLocations.Realend
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tsysIPLocations
Where tsysIPLocations.Realstart = '10.136.112.1' And tsysIPLocations.Realend =
'10.136.113.255' And tblAssetCustom.State = 1

[Klas]

Hi BartCl,
Column StartIP and EndIP are both of data type "numeric". Therefore you can't have a where-clause providing a char-value. Those fields holds ip-values by A) replace blanks with zeroes B) remove any ".". I.e your startIP should look like this instead; 10136112001.
Or; there are two corresponding char-based fields in the table called "realstart" and "realend". They hold the ip-address in "normal" format.
So there are two alternatives:
1) ... where tblAssetCustom.State = 1 And tsysIPLocations.StartIP = 10136112001 And
tsysIPLocations.EndIP = 10136113255
or
2)... where tblAssetCustom.State = 1 And tsysIPLocations.realstart = '10.136.112.1' And
tsysIPLocations.realend = '10.136.113.255'