This answer is corrected in my comments too.
it probably wouldn't work 'cos DHCP is on both by default. You don't want that. You could turn DHCP off the second router. You want all IPs on your network to be in the same subnet.
DHCP on both is fine there's no clash, because the DHCP from the isp gateway will not go past the linksys WRT54G to its LAN side. DHCP on the isp gateway is useful though it'd only be sending to one device, so no worries if you can't turn it off on the isp gateway, it still even has its use. And on the linksys WRT54G it's very useful for sending to each of your computers.
So no good if one device is 192.168.0.1/24 and another is 192.168.1.1/24 or 10.0.0.1
You do want that situation, devices on the LAN side of the linksys WRT54G to be on a different subnet to the one on the WAN side. That's with NAT on. And I have heard that without NAT on it loses other functionality and becomes just a switch. So it's not great either way in that with NAT on both you'll have double NAT just means port forwarding can be more work to do. With NAT off on the linksys, you may lose functionality you want and then have no need for the linksys at all. Ideally you want to turn NAT off on the isp gateway, maybe use a different router/modem to your ISPs if it can't do it. (Some ISPs try to force you to use theirs with testing the MAC address but some router/modems will apparently let you set the MAC manually or clone it from another device, and get around that.
You mentioned not having internet access. You can have internet access with double NAT it won't cause a problem there. It's just a bit more work with port forwarding if you want to run servers, and you're reliant on 2 things working and being configured right, rather than one when doing port forwarding.
typical working examples would be, you might have a router of 192.168.0.1 , a computer of 192.168.0.2 another computer of 192.168.0.3
or another working example , you might have a router of 192.168.1.1 , a computer of 192.168.1.2 another computer of 192.168.1.3
if a device connected to your router is not on a recognized subnet e.g. simple situation is the same subnet of the device.. Then I don't think that device will get online..
Have one router doing DHCP(handing out IPs) to all other devices. And don't have 2 routers doing DHCP or they'll clash while doing it.
Just so you're aware of the options, if however you did turn NAT off on your linksys WRT, then it'd be a switch and lose router functionality, and then if you still wanted to use it, you may not have a useful use for it, but if you still wanted to use it, then you'd want all devices in the same subnet, either set statically or DHCP done by just one device (presumably the isp gateway), DHCP from 2 devices would clash. And with DHCP from the isp gateway it'd reach all of them, would go through your linksys WRT54G and to your computers. If it was your linksys WRT54G doing it, it'd go to all too. That's with NAT off on your linksys.